Optimizing a database query that returns a predetermined number of rows using a generated optimized access plan
View Patent ↗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.
1. 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 if the query can be optimized by determining the query contains an Order By clause, that an index exists for each predicate in a Where clause, and the field of the Order By clause exists in each index;
generating for the query an optimized access plan that eliminates records defined by the Where clause prior to a sort by fetching only n rows from each index and then returning a first n rows, where n is an integer variable; and
retrieving data from a computer database using the optimized access plan that comprises returning n rows after sorting a set of records that includes the n rows from each index.
2. The method of claim 1 wherein the Fetch First n Rows Only clause is implied and the query is an interactive query.
3. The method of claim 1 further comprising the steps of:
determining the query contains an Order By clause, and that there is a determinable value of a field of the Order by Clause; and
eliminating records prior to a sort by creating an access plan that fetches fewer rows by adding the field with the determinable value to each predicate of the Where clause.
4. The method of claim 1 further comprising the steps of:
determining the query contains an Order By clause and that an index exists for the leftmost column but not all the columns; and
generating an access plan that eliminates records prior to a sort by fetching n rows from the index over the leftmost column and additional rows until a unique value of the index is encountered.