International Business Machines Corporation

Donald Alan Kossmann

Michael James Carey

Laura Myers Haas

Ioana Mihaela Ursu

A system and method for avoiding accessing a remote database twice, once during query execution and again to retrieve objects identified by the query, when an application requires objects on which to operate. During query plan generation in response to a request for database objects from an application, a query optimizer inserts cache operators into the candidate plans, and then a cost-benefit analysis is undertaken to identify the best plan. The best plan is then used to execute the query, with the cache operators causing objects identified during query execution to be cached locally to the requesting application as the query is being executed, thereby avoiding requiring the application to access the database after query execution.

System and method for loading a cache with query results

A STOP AFTER clause for a relational database management system (RDBMS) structured query language (SQL) causes the RDBMS, in response to a query, to return a query result having at most a predetermined cardinality specified in the query. A query with a STOP AFTER clause is processed by the RDBMS by provision of one or more STOP operators that are inserted into a query execution plan generated by the RDBMS to execute the query. In a conservative policy, STOP operators are inserted in the query execution plan such that no tuples that might be required in the query result are discarded. In contrast, an aggressive policy inserts a STOP operator in the query execution plan wherever it is able to provide a beneficial cardinality reduction. A RESTART operator is inserted into aggressive policy query execution plans to ensure that at least the predetermined number of tuples are returned in the query result, and a final STOP operator is added at or near the root of the plan to ensure that at most the specified number of tuples are produced.

Growing community of inventors

Passau, Germany

Donald Alan Kossmann