Executing complex SQL queries using index screening for conjunct or disjunct index operations

• US 6,081,799 A
• Filed: 05/05/1999
• Issued: 06/27/2000
• Est. Priority Date: 05/05/1999
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lin, Fen-Ling, Beavin, Thomas Abel, Tie, Hong Sang, Hoa, Peter
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US09/305,552
• Time in Patent Office: 419 Days
• Field of Search: 707/2, 707/3, 707/4
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching

Abstract

A method, apparatus, and article of manufacture for an index screening system. A query is executed to access data stored on a data storage device connected to a computer. In particular, while accessing one or more indexes to retrieve row identifiers, index matching predicates in the query are applied to select row identifiers and index screening predicates in the query are applied to eliminate one or more selected row identifiers.

Claims

1. A method of executing a query to access data stored on a data storage device connected to a computer, comprising the step of:

while accessing one or more indexes to retrieve row identifiers,

applying index matching predicates in the query to select row identifiers; and

applying index screening predicates to eliminate one or more selected row identifiers.

2. The method of claim 1, further comprising the step of identifying index matching predicates in the query.

3. The method of claim 1, further comprising the step of identifying index screening predicates in the query.

4. The method of claim 1, further comprising the step of accessing the index with a list prefetch access technique.

5. The method of claim 1, further comprising the step of accessing each index with a multiple index technique.

6. The method of claim 5, wherein the multiple index technique comprises the step of performing an AND operation on the indexes.

7. The method of claim 5, wherein the multiple index technique comprises the step of performing an OR operation on the indexes.

8. The method of claim 7, wherein the step of performing the OR operation comprises the step of applying BOOLEAN logic to eliminate row identifiers in each index.

9. An apparatus for executing a query to retrieve data, comprising:

a computer having a memory and a data storage device coupled thereto that stores the data;

one or more computer programs, performed by the computer, for while accessing one or more indexes to retrieve row identifiers, applying index matching predicates in the query to select row identifiers and applying index screening predicates to eliminate one or more selected row identifiers.

10. The apparatus of claim 9, further comprising the means for identifying index matching predicates in the query.

11. The apparatus of claim 9, further comprising the means for identifying index screening predicates in the query.

12. The apparatus of claim 9, further comprising the means for accessing the index with a list prefetch access technique.

13. The apparatus of claim 9, further comprising the means for accessing each index with a multiple index technique.

14. The apparatus of claim 13, wherein the multiple index technique comprises the means for performing an AND operation on the indexes.

15. The apparatus of claim 13, wherein the multiple index technique comprises the means for performing an OR operation on the indexes.

16. The apparatus of claim 15, wherein the means for performing the OR operation comprises means for applying BOOLEAN logic to eliminate row identifiers in each index.

17. An article of manufacture comprising a program storage medium readable by a computer and embodying one or more instructions executable by the computer to perform method steps for executing a query to retrieve data stored on a data storage device, the method comprising the step of:

while accessing one or more indexes to retrieve row identifiers,

applying index matching predicates in the query to select row identifiers; and

applying index screening predicates to eliminate one or more selected row identifiers.

18. The article of manufacture of claim 17, further comprising the step of identifying index matching predicates in the query.

19. The article of manufacture of claim 17, further comprising the step of identifying index screening predicates in the query.

20. The article of manufacture of claim 17, further comprising the step of accessing the index with a list prefetch access technique.

21. The article of manufacture of claim 17, further comprising the step of accessing each index with a multiple index technique.

22. The article of manufacture of claim 21, wherein the multiple index technique comprises the step of performing an AND operation on the indexes.

23. The article of manufacture of claim 21, wherein the multiple index technique comprises the step of performing an OR operation on the indexes.

24. The article of manufacture of claim 23, wherein the step of performing the OR operation comprises the step of applying BOOLEAN logic to eliminate row identifiers in each index.