Execution of database queries including filtering

• US 8,825,616 B2
• Filed: 07/20/2007
• Issued: 09/02/2014
• Est. Priority Date: 04/28/2000
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Huffman, Paul C., Sharpe, David C., Zilio, Daniel C., McKnight, Kathy A.
• Primary Examiner(s): GOFMAN, ALEX N
• Application Number:
  US11/780,777
• Publication Number:
  US 20080256053A1
• Time in Patent Office: 2,601 Days
• Field of Search: 707/704
• US Class Current: 707/704
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• 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
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99945 Object-oriented database structure processing

Abstract

A query processing system has a query processor and a data manager. The query processor calls the data manager to carry out data access for a query including a filtering operation. The data manager accesses the data in a set of data and before returning the data, initiates a callback to the query processor to determine if the located data meets the filtering criteria. Where the data does not satisfy the filtering criteria, the data manager seeks additional data in the set of data, without having to return the first located data to the query processor.

Claims

1. A method comprising:

stabilizing a page of data that includes a record to be accessed in accordance with an access plan generated for executing a query, the query comprising a non-predicate filtering operation;

positioning a cursor on the record to be accessed;

applying the non-predicate filtering operation of the query to the record to determine whether the record is to be filtered under the non-predicate filtering operation while the page of data remains stabilized; and

in response to a determination that the record is to be filtered under the non-predicate filtering operation,

repositioning the cursor on a next record included in the page of data that is to be accessed in accordance with the access plan generated for executing the query without releasing stabilization of the page of data, and

applying the non-predicate filtering operation of the query to the next record to determine whether the next record is to be filtered under the non-predicate filtering operation while the page of data remains stabilized.

2. The method of claim 1, further comprising:

in response to a determination that the record is not to be filtered under the non-predicate filtering operation,

copying the record to a storage location for further processing in accordance with the access plan, and

releasing stabilization of the page of data.

3. The method of claim 2, further comprising:

further processing the copy of the record in the storage location in accordance with the access plan; and

returning a result for the query.

4. The method of claim 2, wherein the storage location is a processor buffer.

5. The method of claim 2, wherein the storage location is a location in memory.

6. The method of claim 1, wherein the non-predicate filtering operation is a DISTINCT operation.

7. A non-transitory computer readable storage medium encoded with a computer program, the computer program comprising computer-executable instructions for:

stabilizing a page of data that includes a record to be accessed in accordance with an access plan generated for executing a query, the query comprising a non-predicate filtering operation;

positioning a cursor on the record to be accessed;

applying the non-predicate filtering operation of the query to the record to determine whether the record is to be filtered under the non-predicate filtering operation while the page of data remains stabilized; and

in response to a determination that the record is to be filtered under the non-predicate filtering operation,

repositioning the cursor on a next record included in the page of data that is to be accessed in accordance with the access plan generated for executing the query without releasing stabilization of the page of data, and

applying the non-predicate filtering operation of the query to the next record to determine whether the next record is to be filtered under the non-predicate filtering operation while the page of data remains stabilized.

8. The non-transitory computer readable storage medium of claim 7, wherein the computer program further comprises computer-executable instructions for:

in response to a determination that the record is not to be filtered under the non-predicate filtering operation,

copying the record to a storage location for further processing in accordance with the access plan, and

releasing stabilization of the page of data.

9. The non-transitory computer readable storage medium of claim 8, wherein the computer program further comprises computer-executable instructions for:

further processing the copy of the record in the storage location in accordance with the access plan; and

returning a result for the query.

10. The non-transitory computer readable storage medium of claim 8, wherein the storage location is a processor buffer.

11. The non-transitory computer readable storage medium of claim 8, wherein the storage location is a location in memory.

12. The non-transitory computer readable storage medium of claim 7, wherein the non-predicate filtering operation is a DISTINCT operation.

13. A system comprising:

a data manager, the data manager

stabilizing a page of data that includes a record to be accessed in accordance with an access plan generated for executing a query, the query comprising a non-predicate filtering operation, and

positioning a cursor on the record to be accessed; and

a query manager in communication with the data manager, the query manager applying the non-predicate filtering operation of the query to the record to determine whether the record is qualifies under the non-predicate filtering operation while the page of data remains stabilized, and

indicating to the data manager that the record is to be filtered responsive to a determination that the record does not qualify under the non-predicate filtering operation,

in response to receiving the indication from the query manager that the record is to be filtered, the data manager

repositions the cursor on a next record included in the page of data that is to be accessed in accordance with the access plan generated for executing the query without releasing stabilization of the page of data, and

calls the query manager to apply the non-predicate filtering operation of the query to the next record without releasing stabilization of the page of data.

14. The system of claim 13, wherein the query manager indicates to the data manager that the record is not to be filtered responsive to a determination that the record qualifies under the non-predicate filtering operation,

in response to receiving the indication from the query manager that the record is not to be filtered, the data manager

copies the record to a storage location for further processing in accordance with the access plan, and

releases stabilization of the page of data.

15. The system of claim 14, wherein the query manager further

processes the copy of the record in the storage location in accordance with the access plan, and

returns a result for the query.

16. The system of claim 14, wherein the storage location is a processor buffer.

17. The system of claim 14, wherein the storage location is a location in memory.

18. The system of claim 13, wherein the non-predicate filtering operation is a DISTINCT operation.

19. A query processing system comprising:

a compiler generating an access plan for executing a query, the query comprising a designated filtering operation, the designated filtering operation being a non-predicate filtering operation;

a data manager managing access to records located in pages of stored data; and

a query processor in communication with the compiler and the data manager, the query processor

receiving the access plan for executing the query from the compiler, and

indicating to the data manager that the query comprises the designated filtering operation,

in response to receiving the indication from the query processor that the query comprises the designated filtering operation, the data manager

stabilizes a page of data containing a record to be accessed in accordance with the access plan generated for executing the query,

positions a cursor on the record to be accessed contained in the page of data, and

calls the query processor to apply the designated filtering operation of the query to the record without releasing stabilization of the page of data,

in response to receiving the call from the data manager to apply the designated filtering operation, the query processor

applies the designated filtering operation of the query to the record contained in the page of data to determine whether the record qualifies under the designated filtering operation while the page of data remains stabilized,

indicates to the data manager that the record is not to be filtered in response to a determination that the record qualifies under the designated filtering operation, and

indicates to the data manager that the record is to be filtered in response to a determination that the record does not qualify under the designated filtering operation,

in response to receiving the indication from the query processor that the record is not to be filtered, the data manager

copies the record to a storage location for further processing in accordance with the access plan, and

releases stabilization of the page of data,

in response to receiving the indication from the query processor that the record is to be filtered, the data manager

repositions the cursor on a next record to be accessed contained in the page of data without releasing stabilization of the page of data, and

calls the query processor to apply the designated filtering operation of the query to the next record without releasing stabilization of the page of data.

20. The query processing system of claim 19, wherein the query processor further processes the copy of the record in the storage location in accordance with the access plan, and

returns a result for the query.

21. The query processing system of claim 19, wherein the designated filtering operation is a DISTINCT operation.

22. The query processing system of claim 19, wherein the storage location is a buffer of the query processor.

23. The query processing system of claim 19, wherein the storage location IS a location in memory.

24. A method comprising:

generating an access plan for executing a query, the query comprising a designated filtering operation, the designated filtering operation being a non-predicate filtering operation;

stabilizing a page of data containing a record to be accessed in accordance with the access plan generated for executing the query;

positioning a cursor on the record to be accessed contained in the page of data;

applying the designated filtering operation of the query to the record contained in the page of data to determine whether the record qualifies under the designated filtering operation while the page of data remains stabilized;

in response to a determination that the record qualifies under the designated filtering operation,

copying the record to a storage location for further processing in accordance with the access plan, and

releasing stabilization of the page of data; and

in response to a determination that the record does not qualify under the designated filtering operation,

repositioning the cursor on a next record to be accessed contained in the page of data without releasing stabilization of the page of data, and

applying the designated filtering operation of the query to the next record contained in the page of data while the page of data remains stabilized.

25. The method of claim 24, further comprising:

further processing the copy of the record in the storage location in accordance with the access plan; and

returning a result for the query.

26. The method of claim 24, wherein the designated filtering operation IS a DISTINCT operation.

27. The method of claim 24, wherein the storage location is a processor buffer.

28. The method of claim 24, wherein the storage location is a location in memory.

29. A non-transitory computer readable storage medium encoded with a computer program, the computer program comprising computer-executable instructions for:

generating an access plan for executing a query, the query comprising a designated filtering operation, the designated filtering operation being a non-predicate filtering operation;

stabilizing a page of data containing a record to be accessed in accordance with the access plan generated for executing the query;

positioning a cursor on the record to be accessed contained in the page of data;

applying the designated filtering operation of the query to the record contained in the page of data to determine whether the record qualifies under the designated filtering operation while the page of data remains stabilized;

in response to a determination that the record qualifies under the designated filtering operation,

copying the record to a storage location for further processing in accordance with the access plan, and

releasing stabilization of the page of data; and

in response to a determination that the record does not qualify under the designated filtering operation,

repositioning the cursor on a next record to be accessed contained in the page of data without releasing stabilization of the page of data, and

applying the designated filtering operation of the query to the next record contained in the page of data while the page of data remains stabilized.

30. The non-transitory computer readable storage medium of claim 29, wherein the computer program further comprises computer-executable instructions for:

further processing the copy of the record in the storage location in accordance with the access plan; and

returning a result for the query.

31. The non-transitory computer readable storage medium of claim 29, wherein the designated filtering operation is a DISTINCT operation.

32. The non-transitory computer readable storage medium of claim 29, wherein the storage location is a processor buffer.

33. The non-transitory computer readable storage medium of claim 29, wherein the storage location is a location in memory.