System and method for executing a multi-table query
OwnerTerence Purcellinventor
Life of this patent
Estimating the term…
Abstract
A multi-table query system utilizes indexes to provide filtering and then obtains the desired data. The multi-table query system reduces excessive data retrieval by minimizing access by multi-table joins to data pages until absolutely necessary in a process of executing the query. The multi-table query system improves runtime performance and minimizes a risk of poor performance if the optimizer of the DBMS incorrectly estimates the filtering and chooses a less than optimal table join sequence. The multi-table query system does not require the implementation of any additional indexing technology for the DBMS. Existing indexing technologies, such as the standard single table B-tree index design can exploit the multi-table query system.
Claims20
A method of executing a multi-table query, comprising:
- executing the multi-table query beginning with an index of a first table, to apply a first filter, which is associated with the first table, to the query, and to retrieve index data from the first table;
- applying the index data retrieved from the index of the first table to an index of a second table to access index data from the second table, resulting in join index data;
- accessing any one of the first table or the second table with the join index data to generate an intermediate result set;
- and
- accessing any one of the first table or the second table that has not been previously accessed with the intermediate result set, to generate a qualified result set.
A computer program product having a plurality of executable codes stored on a medium, for executing a multi-table query, comprising:
- a first set of instruction codes for executing the multi-table query beginning with an index of a first table, to apply a first filter, which is associated with the first table, to the query, and to retrieve index data from the first table;
- a second set of instruction codes for applying the index data retrieved from the index of the first table to an index of a second table to access index data from the second table, resulting in join index data;
- a third set of instruction codes for accessing any one of the first table or the second table with the join index data to generate an intermediate result set;
- and
- a fourth set of instruction codes for accessing any one of the first table or the second table that has not been previously accessed with the intermediate result set, to generate a qualified result set.
A system for executing a multi-table query, comprising:
- a processor for executing the multi-table query beginning with an index of a first table, to apply a first filter, which is associated with the first table, to the query, and to retrieve index data from the first table;
- for the processor further applies the index data retrieved from the index of the first table to an index of a second table to access index data from the second table, resulting in join index data;
- a query module accesses any one of the first table or the second table with the join index data to generate an intermediate result set;
- and
- the query module further accesses any one of the first table or the second table that has not been previously accessed with the intermediate result set, to generate a qualified result set.
Dependencies are inferred from the claim text.