Method and system for index sampled tablescan

• US 20030097354A1
• Filed: 08/07/2002
• Published: 05/22/2003
• Est. Priority Date: 11/19/2001
• Status: Active Grant

• Current Assignee: Chopard International SA, International Business Machines Corporation
• Original Assignee: Chopard International SA
• Inventors: Finlay, Ian R., Zuzarte, Calisto P.
• Granted Patent: US 7,680,821 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• 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 for processing a query to a 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 index accessing module adapted to access the index a specified number of times to determine if the table contains an entry satisfying the query; and

a tablescan module for scanning substantially the entire table and retrieving data satisfying the query.

2. The optimized query processing system as claimed in claim 1, wherein the index accessing module is adapted to access the index a single time to determine if the table contains an entry satisfying the query.

3. 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.

4. The optimized query processing system as claimed in claim 3, 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.

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

6. A method of processing a database query, wherein the database comprises a table of data and wherein the database also comprises an index correlated to both the query and to the table, the method comprising the following steps:

(a) receiving the database query;

(b) accessing the index to determine if the table contains an instance of data satisfying a query predicate; and

(c) if step (b) determines that the table contains an instance of data satisfying the query predicate, scanning the table to retrieve all instances of data satisfying the query.

7. The method as claimed in claim 6, further comprising the step of generating a query execution plan to implement steps (b) and (c).

8. The method as claimed in claim 6, further comprising the step of returning a negative response if step (b) determines that the table contains no instances of data satisfying the query predicate.

9. A program product stored on a computer readable medium, the program product comprising an optimized query processing system for processing a query to a database system, wherein the database system comprises a table of data and an index correlated to both the query and to the table, and wherein the optimized query processing system comprises:

an index accessing module adapted to access the index a specified number of times to determine if the table contains an entry satisfying the query; and

a tablescan module for scanning substantially the entire table and retrieving data satisfying the query.

10. The program product as claimed in claim 9, wherein the index accessing module is adapted to access the index a single time to determine if the table contains an entry satisfying the query.

11. The program product as claimed in claim 9, 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.

12. The program product as claimed in claim 9, 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.

13. A computer readable medium containing program instructions for processing database query, wherein the database comprises a table of data and wherein the database also comprises an index correlated to both the query and to the table, the program instructions for:

(a) receiving the database query;

(b) accessing the index to determine if the table contains an instance of data satisfying a query predicate; and

(c) if step (b) determines that the table contains an instance of data satisfying the query predicate, scanning the table to retrieve all instances of data satisfying the query.

14. The computer readable medium as claimed in claim 13, further comprising instructions for generating a query execution plan to implement steps (b) and (c).

15. The computer readable medium as claimed in claim 13, further comprising instructions for returning a negative response if step (b) determines that the table contains no instances of data satisfying the query predicate.