System for aggregation subquery join elimination

• US 7,966,314 B2
• Filed: 08/18/2008
• Issued: 06/21/2011
• Est. Priority Date: 04/30/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Ma, Wenbin, Zuzarte, Calisto, Cheng, Qi
• Primary Examiner(s): Rones; Charles
• Assistant Examiner(s): Adams; Charles D
• Application Number:
  US12/193,585
• Publication Number:
  US 20080306906A1
• Time in Patent Office: 1,037 Days
• Field of Search: None
• US Class Current: 707/713
• CPC Class Codes: G06F 16/90335 Query processing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A system for use with an information retrieval system, in which system is used for transforming a database statement containing a first subquery and a second subquery joined together, the first subquery including a group by operation over a child subquery, the information retrieval system comprising means for removing the first subquery from the database statement thereby eliminating the join between the first subquery and the second subquery.

Claims

1. An information retrieval system for transforming a database statement containing a first subquery and a second subquery joined to the first subquery, the first subquery including a group by operation over a child subquery, the information retrieval system comprising means for removing the first subquery from the database statement thereby eliminating the join between the first subquery and the second subquery,

wherein the means for removing of the first subquery and elimination of the join between the first subquery and the second subquery creates a transformed database statement that includes the second subquery and excludes the first subquery, and

wherein the child subquery subsumes the second subquery, the child subquery is equivalent to the second subquery when a group by compensation operation is applied to the child subquery,

a plurality of columns in the group by compensation operation is a super set of one or more columns in the group by operation,

the join between the first subquery and the second subquery involves an equality predicate between at least one column in the group by operation and at least one column in the group by compensation operation,

one or more aggregation functions in the first subquery are not referenced elsewhere in the database statement, and

the first subquery does not include a local predicate on the group by operation.

2. The system of claim 1, further comprising means for adding a window aggregation function in the second subquery corresponding to an aggregation function in the first subquery when the database statement references the aggregation function in the first subquery.

3. The system of claim 1, further comprising means for inserting a window aggregation function in the second subquery corresponding to an aggregation function in the first subquery;

and transferring a local predicate on the aggregation function in the first subquery to the inserted corresponding window aggregation function in the second subquery.

4. The system of claim 3, wherein the transferred local predicate is pulled up.

5. The system of claim 1, further comprising means for pulling up a having clause on the group by operation in the first subquery;

and means for including a window aggregation function in second subquery corresponding to an aggregation function in the first subquery.

6. A computer program product having a computer readable medium tangibly embodying computer executable code for directing an information retrieval system to transform a database statement containing a first subquery and a second subquery joined to the first subquery, the first subquery including a group by operation over a child subquery, the computer program product comprising code for removing the first subquery from the database statement thereby eliminating the join between the first subquery and the second subquery,

wherein the code for removing of the first subquery and elimination of the join between the first subquery and the second subquery creates a transformed database statement that includes the second subquery and excludes the first subquery, and

wherein the child subquery subsumes the second subquery, the child subquery is equivalent to the second subquery when a group by compensation operation is applied to the child subquery,

a plurality of columns in the group by compensation operation is a super set of one or more columns in the group by operation,

the join between the first subquery and the second subquery involves an equality predicate between at least one column in the group by operation and at least one column in the group by compensation operation,

one or more aggregation functions in the first subquery are not referenced elsewhere in the database statement, and

the first subquery does not include a local predicate on the group by operation.

7. The computer program product of claim 6, further comprising code for adding a window aggregation function in the second subquery corresponding to an aggregation function in the first subquery when the database statement references the aggregation function in the first subquery.

8. The computer program product of claim 6, further comprising code for inserting a window aggregation function in the second subquery corresponding to an aggregation function in the first subquery;

and transferring a local predicate on the aggregation function in the first subquery to the inserted corresponding window aggregation function in the second subquery.

9. The computer program product of claim 8, wherein the transferred local predicate is pulled up.

10. The computer program product of claim 6, further comprising code for pulling up a having clause on the group by operation in the first subquery;

and means for including a window aggregation function in second subquery corresponding to an aggregation function in the first subquery.