Aggregate join index for relational databases

• US 6,505,189 B1
• Filed: 06/15/2000
• Issued: 01/07/2003
• Est. Priority Date: 06/15/2000
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: NCR Corporation
• Inventors: Hoang, Chi Kim, On Au, Grace Kwan
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/594,964
• Time in Patent Office: 936 Days
• Field of Search: 707/2, 707/3, 707/4, 707/203
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24556 Aggregation; Duplicate elimination
• CPC Class Codes: G06F 16/2456 Join operations
• CPC Class Codes: G06F 16/24561 Intermediate data storage techniques for performance improvement
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

The retrieval of data from a database in a computerized database management system is optimized through the use of an aggregate join index. The aggregate join index combines one or more commonly-used columns of the base tables with the results from one or more aggregation expressions pre-computed from one or more columns of the tables.

Claims

1. A method of optimizing retrieval of data from a database in a computerized database management system, comprising:

(a) computing one or more aggregation expressions from one or more columns of one or more tables in the database; and

(b) creating an aggregate join index for the tables by combining one or more commonly-used columns of the tables with results from the aggregation expressions.

2. The method of claim 1 above, wherein a definition of the aggregate join index specifies the aggregate expressions.

3. The method of claim 1 above, wherein the creating step further comprises creating the aggregate join index by performing a join operation on the tables.

4. The method of claim 1 above, further comprising incrementally updating the aggregate join index when the tables are updated.

5. A method of optimizing retrieval of data from a database in a computerized database management system, comprising:

(a) receiving a query into a computer; and

(b) processing the query using an aggregate join index for one or more tables referenced in the query, wherein the aggregate join index combines one or more commonly-used columns of the tables with results from one or more aggregation expressions computed from one or more columns of the tables.

6. The method of claim 5 above, wherein the processing step further comprises satisfying the query with the results from the aggregate expressions stored in the aggregate join index rather than the tables referenced in the query.

7. The method of claim 6, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set of join operations as specified in the query, the aggregate expressions are defined by a same set or a superset of GROUP BY fields as specified in the query, and the aggregate join index contains all fields selected by the query.

8. The method of claim 6, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a subset of join operations as specified in the query, the aggregate join index is created by a same set or a superset of GROUP BY fields and SELECT fields as specified in the query with respect to a set of tables defined by a subset of join conditions, and the aggregate join index contains fields needed by any remaining join conditions specified in the query.

9. The method of claim 6, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set or subset of join operations as specified in the query, and the query does not specify a GROUP BY clause.

10. A computerized database management system for optimizing retrieval of data from a database, comprising:

(a) logic, performed by the database management system, for;

(1) computing one or more aggregation expressions from one or more columns of one or more tables in the database; and

(2) creating an aggregate join index for the tables by combining one or more commonly-used columns of the tables with results from the aggregation expressions.

11. The system of claim 10 above, wherein a definition of the aggregate join index specifies the aggregate expressions.

12. The system of claim 10 above, wherein the logic for creating further comprises logic for creating the aggregate join index by performing a join operation on the tables.

13. The system of claim 10 above, further comprising logic for incrementally updating the aggregate join index when the tables are updated.

14. A computerized database management system for optimizing retrieval of data from a database, comprising:

(a) logic, performed by the database management system, for;

(1) receiving a query into a computer; and

(2) processing the query using an aggregate join index for one or more tables referenced in the query, wherein the aggregate join index combines one or more commonly-used columns of the tables with results from one or more aggregation expressions computed from one or more columns of the tables.

15. The system of claim 14 above, wherein the logic for processing further comprises logic for satisfying the query with the results from the aggregate expressions stored in the aggregate join index rather than the tables referenced in the query.

16. The system of claim 15 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set of join operations as specified in the query, the aggregate expressions are defined by a same set or a superset of GROUP BY fields as specified in the query, and the aggregate join index contains all fields selected by the query.

17. The system of claim 15 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a subset of join operations as specified in the query, the aggregate join index is created by a same set or a superset of GROUP BY fields and SELECT fields as specified in the query with respect to a set of tables defined by a subset of join conditions, and the aggregate join index contains fields needed by any remaining join conditions specified in the query.

18. The system of claim 15 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set or subset of join operations as specified in the query, and the query does not specify a GROUP BY clause.

19. An article of manufacture embodying logic for performing a method of optimizing retrieval of data from a database in a computerized database management system, the method comprising:

(a) computing one or more aggregation expressions from one or more columns of one or more tables in the database; and

(b) creating an aggregate join index for the tables by combining one or more commonly-used columns of the tables with results from the aggregation expressions.

20. The article of manufacture of claim 19 above, wherein a definition of the aggregate join index specifies the aggregate expressions.

21. The article of manufacture of claim 19 above, wherein the creating step further comprises creating the aggregate join index by performing a join operation on the tables.

22. The article of manufacture of claim 19 above, further comprising incrementally updating the aggregate join index when the tables are updated.

23. An article of manufacture embodying logic for performing a method of optimizing retrieval of data from a database in a computerized database management system, the method comprising:

(a) receiving a query into a computer; and

(b) processing the query using an aggregate join index for one or more tables referenced in the query, wherein the aggregate join index combines one or more commonly-used columns of the tables with results from one or more aggregation expressions computed from one or more columns of the tables.

24. The article of manufacture of claim 23 above, wherein the processing step further comprises satisfying the query with the results from the aggregate expressions stored in the aggregate join index rather than the tables referenced in the query.

25. The article of manufacture of claim 24 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set of join operations as specified in the query, the aggregate expressions are defined by a same set or a superset of GROUP BY fields as specified in the query, and the aggregate join index contains all fields selected by the query.

26. The article of manufacture of claim 24 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a subset of join operations as specified in the query, the aggregate join index is created by a same set or a superset of GROUP BY fields and SELECT fields as specified in the query with respect to a set of tables defined by a subset of join conditions, and the aggregate join index contains fields needed by any remaining join conditions specified in the query.

27. The article of manufacture of claim 24 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set or subset of join operations as specified in the query, and the query does not specify a GROUP BY clause.

28. A data structure stored in a memory for use by a database management system executed by a computer, the data structure comprising an aggregate join index for one or more tables referenced in the query, wherein the aggregate join index combines one or more commonly-used columns of the tables with results from one or more aggregation expressions computed from one or more columns of the tables.

29. The data structure of claim 28 above, wherein a definition of the aggregate join index specifies the aggregate expressions.

30. The data structure of claim 28 above, wherein the aggregate join index is created by performing a join operation on the tables.

31. The data structure of claim 28 above, wherein the aggregate join index is incrementally updated when the tables are updated.

32. The data structure of claim 28 above, wherein a query is processed using the aggregate join index.

33. The data structure of claim 32 above, wherein the query is satisfied with the results from the aggregate expressions stored in the aggregate join index rather than the tables referenced in the query.

34. The data structure of claim 33 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set of join operations as specified in the query, the aggregate expressions are defined by a same set or a superset of GROUP BY fields as specified in the query, and the aggregate join index contains all fields selected by the query.

35. The data structure of claim 33, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a subset of join operations as specified in the query, the aggregate join index is created by a same set or a superset of GROUP BY fields and SELECT fields as specified in the query with respect to a set of tables defined by a subset of join conditions, and the aggregate join index contains fields needed by any remaining join conditions specified in the query.

36. The data structure of claim 33 above, wherein the results from the aggregate expressions stored in the aggregate join index are used to satisfy the query, when the query includes the aggregate expressions, the aggregate join index is created by a same set or subset of join operations as specified in the query, and the query does not specify a GROUP BY clause.