Method for aggregation subquery join elimination

• US 7,454,416 B2
• Filed: 04/30/2003
• Issued: 11/18/2008
• Est. Priority Date: 04/30/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Cheng, Qi, Ma, Wenbin, Zuzarte, Calisto P
• Primary Examiner(s): Rones; Charles
• Assistant Examiner(s): Adams; Charles D
• Application Number:
  US10/428,263
• Publication Number:
  US 20040220911A1
• Time in Patent Office: 2,029 Days
• Field of Search: 707 1- 4, 707/102
• US Class Current: 1/1
• 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 method for use with an information retrieval system, in which the method 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 method comprising removing the first subquery from the database statement thereby removing the join between the first subquery and the second subquery.

Claims

1. A computer implemented method 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 method comprising:

removing the first subquery from the database statement thereby eliminating the JOIN between the first subquery and the second subquery,

wherein removal 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 method of claim 1, further comprising:

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 method of claim 1, further comprising:

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 method of claim 3, wherein the transferred local predicate is pulled up.

5. The method of claim 1, further comprising:

pulling up a HAVING clause on the GROUP BY operation in the first subquery; and

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