Method and system for index sampled tablescan

• US 7,680,821 B2
• Filed: 08/07/2002
• Issued: 03/16/2010
• Est. Priority Date: 11/19/2001
• Status: Expired due to Fees

• Current Assignee: Chopard International SA, International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Finlay, Ian R., Zuzarte, Calisto P.
• Primary Examiner(s): Woo; Isaac M
• Application Number:
  US10/215,073
• Publication Number:
  US 20030097354A1
• Time in Patent Office: 2,778 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-206
• US Class Current: 707/803
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

An optimized query processing system and method for a database system. The database system includes a table of data and an index correlated to both the query and to the table. The optimized query processing system includes an index accessing module adapted to access the index to determine if the table contains an entry satisfying a query predicate; and a tablescan module for scanning substantially the entire table and retrieving data satisfying the query. A method is also disclosed for generating database diagnostic data.

Claims

1. An optimized query processing system in a database system in a computer system, the optimized query processing system for processing a query to the database system, wherein the database system comprises a table of data and an index correlated to both the query and to the table, the optimized query processing system comprising:

an optimizer module to receive a query, the optimizer module to determine whether data responsive to the query has low distinct cardinality;

an index accessing module, coupled in communication with the optimizer module, responsive to the optimizer module determining that the data responsive to the query has low distinct cardinality, the index accessing module to access the index to determine if the table contains a first data entry satisfying the query; and

a tablescan module, coupled in communication with the index accessing module, responsive to the index accessing module determining that the first entry satisfies the query and prior to determining that a second data entry satisfies the query, the tablescan module to scan substantially the entire table to retrieve substantially all data values satisfying the query.

2. The optimized query processing system as claimed in claim 1, further comprising a query execution plan generation module adapted to generate a query execution plan to cause the index accessing module to access the index to determine if the table contains an entry satisfying the query.

3. The optimized query processing system as claimed in claim 2, wherein the query execution plan generation module is further adapted such that the query execution plan causes the tablescan module to scan substantially the entire table and retrieve data satisfying the query if the index accessing module determines that the table contains an entry satisfying the query.

4. The optimized query processing system as claimed in claim 1, further comprising an optimizer module for detecting predetermined characteristics for a query received by the database system.

5. The system of claim 1, wherein

the optimizer module, responsive to determining that the data responsive to the query does not have low distinct cardianality, to determine whether the data responsive to the query has at least one value with high frequency, and

the index accessing module, responsive to the optimizer module determining that the data responsive to the query has at least one value with high frequency, to access the index to determine if the table contains a first entry satisfying the query.

6. The system of claim 1, wherein

the optimizer module, to determine whether the query includes a wild card, and

the index accessing module, responsive to the optimizer module determining that the query includes a wild card, to access the index to determine if the table contains a first entry satisfying the query.

7. The system of claim 1, wherein

the index accessing module, responsive to the optimizer module determining that the data query does not have low distinct cardinality, the index accessing module to access the index to determine row identification for the table corresponding to each entry satisfying the query, and

the tablescan module to retrieve values corresponding to the row identifications.

8. A computer implemented method of processing a query to a database in a computer system, wherein the database comprises a table of data and an index correlated to both the query and to the table, the method comprising the following steps:

receiving the database query;

determining whether data responsive to the query has low distinct cardinality;

responsive to determining that the data responsive to the query has low distinct cardinality, accessing the index to determine if the table contains a first data entry satisfying a query predicate; and

responsive to determining that the fist data entry satisfies the query and prior to determining that a second data entry satisfies the query, scanning substantially the entire table to retrieve substantially all data values satisfying the query.

9. The method as claimed in claim 8, further comprising the step of generating a query execution plan.

10. The method as claimed in claim 8, further comprising the step of returning a negative response if the table contains no instances of data satisfying the query predicate.

11. A program product stored on a computer readable medium having program logic recorded thereon for processing a query to a database system in a computer system, wherein the database system comprises a table of data and an index correlated to both the query and to the table, the program product comprising:

code for receiving a query and determine whether data responsive to the query has low distinct cardinality;

code for, responsive to determining that the data responsive to the query has low distinct cardinality, accessing the index to determine if the table contains a first data entry satisfying the query; and

code for, responsive to determining that the first entry satisfies the query and prior to determining that a second data entry satisfies the query, scanning substantially the entire table to retrieve substantially all data values satisfying the query.

12. The program product as claimed in claim 11, further comprising code for generating a query execution plan that invokes the code for accessing the index to determine if the table contains an entry satisfying the query.

13. The program product as claimed in claim 11, wherein the query execution plan further invokes the code for scanning substantially the entire table and code for retrieving data satisfying the query if the table contains an entry satisfying the query.

14. A computer readable medium containing program instructions, which when executed by a processor in a computer system causes the processor to perform a method for processing a query to a database in the computer system, wherein the database comprises a table of data and an index correlated to both the query and to the table, the method comprising:

receiving the database query;

determining whether data responsive to the query has low distinct cardinality;

responsive to determining that the data responsive to the query has low distinct cardinality, accessing the index to determine if the table contains a first data entry satisfying a query predicate; and

responsive to determining that the fist data entry satisfies the query and prior to determining that a second data entry satisfies the query, scanning substantially the entire table to retrieve substantially all data values satisfying the query.

15. The computer readable medium as claimed in claim 14, the method further comprising generating a query execution plan.

16. The computer readable medium as claimed in claim 14, the method further comprising returning a negative response if the table contains no instances of data satisfying the query predicate.