Database query optimization using index carryover to subset an index

摘要

A method, apparatus and program product use a first index associated with a field in a database table to identify a range of records in the database table that includes instances of a first key value in the field and use the identified range of records to subset a second index associated with another field in a database table. The database query identifies the first key value for the field in the database table and the second key value for the other field in the database table. By doing so, information from an index may be carried over and applied to another index to subset the other index, often reducing the quantity of entries that are searched in the other index and improving performance.

主权项

1. A method for processing a database query, wherein the database query identifies a first key value for a field in a database table and a second key value for another field in the database table, the method comprising:
using a first index associated with the field in the database table to identify a range of records in the database table that includes instances of the first key value in the field, wherein the first index associated with the field in the database table identifies a first record in the database table including an instance of the first key value in the field and a last record in the database table including an instance of the first key value in the field, and wherein using the first index to identify the range of records uses the first and last records identified by the first index; creating a range predicate from the identified range of records; prior to executing the database query, optimizing the database query by adding the range predicate to the database query; and using the identified range of records to subset a second index associated with the other field in the database table by subsetting the second index with the range predicate, wherein the first and second indexes are built over different fields of the same database table, and wherein the field and the other field are identified in the database query.