Back to results
US 8,412,700 B2Granted
Database query optimization using index carryover to subset an index
AssigneeInternational Business Machines Corporation
InventorsPaul Reuben Day, Randy L. Egan, Roger Alan Mittelstadt
Life of this patent
Estimating the term…
Abstract
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.
Claims19
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;
- creating a predicate from the identified range of records;
- 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 predicate, wherein the first and second indexes are built over different fields of the same database table, wherein each of the first and second indexes covers all of the records of the database table, wherein the field and the other field are identified in the database query, and wherein subsetting the second index further comprises using the second index associated with the other field in the database table to identify a second range of records in the database table that includes instances of the second key value in the other field;
- and
- generating a composite range of records based upon the first and second ranges of records, wherein generating the composite range of records comprises combining the first range of records from the first index and the second range of records from the second index based upon at least one operation in the database query.
2 more elements
An apparatus comprising:
- a processor,
- a memory;
- and program code resident in the memory and configured to be executed by the processor to process 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, by 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 and using the identified range of records to subset a second index associated with the other field in the database table, wherein the program code is further configured to create a predicate from the identified range of records and subset the second index with the predicate, wherein the first and second indexes are built over different fields of the same database table, wherein each of the first and second indexes covers all of the records of the database table, wherein the field and the other field are identified in the database query, wherein the program code is further configured to subset the second index by using the second index associated with the other field in the database table to identify a second range of records in the database table that includes instances of the second key value in the other field, wherein the program code is further configured to generate a composite range of records based upon the first and second ranges of records, wherein the program code is further configured to generate the composite range of records by combining the first range of records from the first index and the second range of records from the second index based upon at least one operation in the database query.
A program product, comprising:
- program code configured to process 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, by 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 and using the identified range of records to subset a second index associated with the other field in the database table, wherein the program code is further configured to create a predicate from the identified range of records and subset the second index with the predicate, wherein the first and second indexes are built over different fields of the same database table, wherein each of the first and second indexes covers all of the records of the database table, wherein the field and the other field are identified in the database query;
- wherein the program code is further configured to subset the second index by using the second index associated with the other field in the database table to identify a second range of records in the database table that includes instances of the second key value in the other field, wherein the program code is further configured to generate a composite range of records based upon the first and second ranges of records, wherein the program code is further configured to generate the composite range of records by combining the first range of records from the first index and the second range of records from the second index based upon at least one operation in the database query, and a non-transitory computer readable medium storing the program code.
Dependencies are inferred from the claim text.