Multiple result sets generated from single pass through a dataspace

• US 9,411,861 B2
• Filed: 12/21/2007
• Issued: 08/09/2016
• Est. Priority Date: 12/21/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bestgen, Robert Joseph, Carlson, David Glenn, Downer, Robert Victor, Kethireddy, Shantan
• Primary Examiner(s): Waldron, Scott A
• Assistant Examiner(s): Davanlou, Soheila
• Application Number:
  US11/962,980
• Publication Number:
  US 20090164412A1
• Time in Patent Office: 3,154 Days
• Field of Search: 707/714, 707/999.004, 707/736, 707/747, 707/770
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

A method, apparatus and program product are provided for performing a query of a database. A database query is received, which includes first and second operations respectively configured to generate first and second disjoint results sets from a dataspace. The database query is analyzed to identify a set of attributes from the dataspace that are used by at least one of the first and second operations in the database query. During execution of the database query, a plurality of records from the dataspace is iteratively processed in a single pass, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets.

Claims

1. A method of performing a query of a database, the method comprising:

receiving a database query, the database query including first and second operations respectively configured to generate first and second disjoint results sets from a dataspace;

analyzing the database query to identify a set of attributes from the dataspace that are used by at least one of the first and second operations in the database query; and

during execution of the database query, iteratively processing a plurality of records from the dataspace in a single pass, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets.

2. The method of claim 1 wherein analyzing the database query comprises:

with a query optimizer, recognizing in the database query the creation of first and second disjoint results sets from the dataspace;

identifying the set of attributes from the dataspace that are used by at least one of the first and second operations in the database query;

defining the first and second operations to include in the set of attributes the attributes relevant to the corresponding first and second operations; and

rewriting the database query with the query optimizer to retrieve all of the attributes in the set of attributes in a single pass through the common dataspace.

3. The method of claim 1 wherein the first and second operations each include a production of a hash table, sorted list, index, relative record number list, relative record number bitmap, or unordered list.

4. The method of claim 1 wherein performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets is performed serially.

5. The method of claim 1 wherein performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets is performed in parallel.

6. The method of claim 1 wherein the database query includes at least one of:

user defined functions, user defined table functions, and references to many tables.

7. The method of claim 1 wherein each attribute in the set of attributes defines a column in a database table in the dataspace.

8. The method of claim 1 wherein the database query includes a third operation configured to generate a third disjoint results set from the dataspace, the method further comprising:

analyzing the database query to identify the set of attributes from the dataspace that are used by at least one of the first, second, and third operations in the database query; and

during execution of the database query, iteratively processing a plurality of records from the dataspace in a single pass, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first, second, and third operations on the record using the retrieved attributes for such record to build the first, second, and third disjoint results sets.

9. The method of claim 1 wherein the dataspace comprises a plurality of database tables including attributes referenced by the database query.

10. An apparatus comprising:

a processor; and

program code configured to be executed by the processor for performing a query of a database, the program code configured to receive a database query, the database query including first and second operations respectively configured to generate first and second disjoint results sets from a dataspace, to analyze the database query to identify a set of attributes from the dataspace that are used by at least one of the first and second operations in the database query, and to iteratively process a plurality of records from the dataspace in a single pass during execution of the database query, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets.

11. The apparatus of claim 10 wherein the program code is configured to analyze the database query by:

with a query optimizer, recognizing in the database query the creation of first and second disjoint results sets from the dataspace;

identifying the set of attributes from the dataspace that are used by at least one of the first and second operations in the database query;

defining the first and second operations to include in the set of attributes the attributes relevant to the corresponding first and second operations; and

rewriting the database query with the query optimizer to retrieve all of the attributes in the set of attributes in a single pass through the common dataspace.

12. The apparatus of claim 10 wherein the first and second operations each include a production of a hash table, sorted list, index, relative record number list, relative record number bitmap, or unordered list.

13. The apparatus of claim 10 wherein the program code is configured to perform each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets serially.

14. The apparatus of claim 10 wherein the program code is configured to perform each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets in parallel.

15. The apparatus of claim 10 wherein the database query includes at least one of:

user defined functions, user defined table functions, and references to many tables.

16. The apparatus of claim 10 wherein each attribute in the set of attributes defines a column in a database table in the dataspace.

17. The apparatus of claim 10 wherein the database query includes a third operation configured to generate a third disjoint results set from the dataspace, the program code further configured to analyze the database query to identify the set of attributes from the dataspace that are used by at least one of the first, second, and third operations in the database query, and to iteratively process a plurality of records from the dataspace in a single pass during execution of the database query, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first, second, and third operations on the record using the retrieved attributes for such record to build the first, second, and third disjoint results sets.

18. A program product, comprising:

a non-transitory recordable computer readable medium; and

a program code configured for performing a query of a database, the program code resident on the computer readable medium and configured to receive a database query, the database query including first and second operations respectively configured to generate first and second disjoint results sets from a dataspace, to analyze the database query to identify a set of attributes from the dataspace that are used by at least one of the first and second operations in the database query, and to iteratively process a plurality of records from the dataspace in a single pass during execution of the database query, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets.

19. The program product of claim 18 wherein the program code is configured to analyze the database query by:

with a query optimizer, recognizing in the database query the creation of first and second disjoint results sets from the dataspace;

identifying the set of attributes from the dataspace that are used by at least one of the first and second operations in the database query;

defining the first and second operations to include the attributes in the set of attributes relevant to the corresponding first and second operations; and

rewriting the database query with the query optimizer to retrieve all of the attributes in the set of attributes in a single pass through the common dataspace.

20. The program product of claim 18 wherein the first and second operations each include a production of a hash table, sorted list, index, relative record number list, relative record number bitmap, or unordered list.

21. The program product of claim 18 wherein the program code is configured to perform each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets serially.

22. The program product of claim 18 wherein the program code is configured to perform each of the first and second operations on the record using the retrieved attributes for such record to build the first and second disjoint results sets in parallel.

23. The program product of claim 18 wherein the database query includes at least one of:

user defined functions, user defined table functions, and references to many tables.

24. The program product of claim 18 wherein each attribute in the set of attributes defines a column in a database table in the dataspace.

25. The program product of claim 18 wherein the database query includes a third operation configured to generate a third disjoint results set from the dataspace, the program code further configured to analyze the database query to identify the set of attributes from the dataspace that are used by at least one of the first, second, and third operations in the database query, and to iteratively process a plurality of records from the dataspace in a single pass during execution of the database query, including, for each of the plurality of records, processing such record by retrieving the plurality of attributes for such record from the dataspace and performing each of the first, second, and third operations on the record using the retrieved attributes for such record to build the first, second, and third disjoint results sets.