Optimizing a database query that fetches N rows
AssigneeInternational Business Machines Corporation
InventorsBrian Robert Muras, Robert Russell Nelson, John Matthew Santosuosso
Life of this patent
Estimating the term…
Abstract
An apparatus and method to optimize a query access plan in a computer database system. In preferred embodiments, the query optimizer determines the query can be optimized and generates for the query an optimized access plan that eliminates records defined by a Where clause and one or more ordering criteria prior to ordering the records (sorting or grouping) and then returning a first n rows.
Claims17
An apparatus comprising:
- at least one processor;
- a memory coupled to the at least one processor containing a database of records;
- a query residing in the memory that includes a Select statement with a Fetch First n Rows Only clause, where n is an integer variable;
- a query optimizer residing in the memory and executed by the at least one processor, wherein the query optimizer:
- determines that the query can be optimized and generates for the query an optimized access plan that eliminates records defined by a Where clause prior to ordering the records and then returns a first n rows;
- determines that the query contains a Group By clause and that an index exists for a leftmost column but not all the columns of the Group By clause;
- and
- generates an access plan that eliminates records prior to grouping by fetching n rows from the index over the leftmost column and remaining rows until a unique value of the index is encountered.
A method for optimizing a database query that includes a Select statement with a Fetch First n Rows Only clause, the method comprising the steps of:
- analyzing the query to determine the query is optimizable by determining that the query contains a Group By clause and that an index exists for a leftmost column but not all the columns of the Group By clause;
- generating for the query an optimized access plan that eliminates records defined by a Where clause prior to ordering records by creating an access plan that eliminates records prior to grouping by fetching n rows from the index over the leftmost column and remaining rows until a unique value of the index is encountered;
- and
- using the optimized access plan to retrieve data from the database table.
An article of manufacture comprising:
- (A) a query optimizer that analyzes and optimizes a query by performing the following steps:
- analyzing the query to determine the query is optimizable by determining the query includes a Select statement with a Fetch First n Rows Only Clause;
- wherein the query optimizer further determines that the query contains a Group By clause and that an index exists for a leftmost column but not all the columns of the Group By clause;
- generating for the query an optimized access plan that eliminates records defined by a Where clause prior to ordering records by creating an access plan that eliminates records prior to grouping by fetching n rows from the index over the leftmost column and remaining rows until a unique value of the index is encountered and then returns a first n rows, where n is an integer variable;
- and
- (B) computer-recordable storage media bearing the query optimizer.
Dependencies are inferred from the claim text.