AGGREGATE JOIN INDEX UTILIZATION IN QUERY PROCESSING

• US 20100036800A1
• Filed: 08/05/2008
• Published: 02/11/2010
• Est. Priority Date: 08/05/2008
• Status: Active Grant

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Bouloy, Carlos, Au, Grace, Gui, Hong
• Granted Patent: US 7,912,833 B2
• Time in Patent Office: Days
• US Class Current: N/A
• CPC Class Codes: G06F 16/24544 Join order optimisation
• CPC Class Codes: Y10S 707/957 Multidimensional

Abstract

A system and method include obtaining a query and identifying an aggregate join index (AJI) at a high level of aggregation. The dimension table may be rolled-up with the grouping key being the union of the grouping key in the AJI and the grouping key of the query. The identified AJI is joined with the rolled-up dimension table to obtain columns in the query that are not in the identified AJI. The joined AJI and rolled-up dimension table are then rolled up to answer the query.

Claims

1. A method comprising:

obtaining a query;

identifying an AJI at a certain level of aggregation;

rolling up the dimension table with the grouping key being the union of the grouping key in the AJI and the grouping key of the query;

joining the identified AJI with the rolled-up dimension table to obtain columns in the query that are not in the identified AJI;

rolling up the joined AJI and rolled-up dimension table to answer the query.

2. The method of claim 1 wherein if a grouping key of the query is a subset of a grouping key of the AJI, directly rolling up the AJI to answer the query.

3. The method of claim 1 wherein the join between the AJI and the rolled-up dimension table ensures that no duplicates are introduced by the join.

4. The method of claim 1 wherein the rollup of the joined AJI and rolled-up dimension table uses a union of a grouping key in the AJI and a grouping key of the query as its grouping key.

5. The method of claim 4 wherein the grouping key of the query has a function dependency on the grouping key of the AJI.

6. A method comprising:

obtaining a query that specifies an aggregate at a first level and has a grouping key;

identifying an aggregate join index (AJI) at a higher level of aggregation, wherein the AJI does not contain the grouping key of the query;

rolling up the dimension table with the grouping key being the union of the grouping key in the AJI and the grouping key of the query;

joining the identified AJI with the rolled up dimension table to obtain columns in the query that are not in the identified AJI;

rolling up the joined AJI and dimension table to answer the query.

7. The method of claim 6 wherein an AJI at higher levels of aggregation is smaller than an AJI at a lower level of aggregation.

8. The method of claim 6 wherein the join between the AJI and the rolled-up dimension table ensures that each group in the AJI has its corresponding higher level grouping key correctly and that no duplicates are introduced by the join.

9. The method of claim 6 wherein the rollup uses a union of a grouping key in the AJI and the grouping key of the query as its grouping key.

10. The method of claim 9 wherein the grouping key of the query has a function dependency on the grouping key of the AJI.

11. A computer readable medium having instructions stored thereon to cause a computer to execute a method comprising:

obtaining a query that specifies an aggregate with a grouping key;

identifying an aggregate join index (AJI) that does not contain the grouping key of the query;

rolling up the dimension table with the grouping key being the union of the grouping key in the AJI and the grouping key of the query;

joining the identified AJI with the rolled up dimension table to obtain columns in the query that are not in the identified AJI;

rolling up the joined AJI and dimension table to answer the query.

12. The computer readable medium of claim 11 wherein the join between the AJI and the rolled-up dimension table ensures that each group in the AJI has its corresponding higher level grouping key correctly and that no duplicates are introduced by the join.

13. The computer readable medium of claim 11 wherein the rollup uses a union of a grouping key in the AJI and the grouping key of the query as its grouping key.

14. The computer readable medium of claim 13 wherein the grouping key of the query has a function dependency on the grouping key of the AJI.

15. A system comprising:

one or more processing units;

one or more data storage units coupled to the one or more processors;

one or more optimizers executing on the one or more processing units that are configured to;

obtain a query that specifies an aggregate with a grouping key;

identify an aggregate join index (AJI) that does not contain the grouping key of the query;

roll up the dimension table with the grouping key being the union of the grouping key in the AJI and the grouping key of the query;

join the identified AJI with the rolled up dimension table to obtain columns in the query that are not in the identified AJI;

roll up the joined AJI and dimension table to answer the query.

16. The system of claim 15 wherein the join between the AJI and the rolled-up dimension table ensures that each group in the AJI has its corresponding higher level grouping key correctly and that no duplicates are introduced by the join.

17. The system of claim 15 wherein the rollup uses a union of a grouping key in the AJI and the grouping key of the query as its grouping key.

18. The system of claim 17 wherein the grouping key of the query has a function dependency on the grouping key of the AJI.