Method and apparatus for optimizing queries having group-by operators

• US 5,598,559 A
• Filed: 05/28/1996
• Issued: 01/28/1997
• Est. Priority Date: 07/01/1994
• Status: Expired due to Term

• Current Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Original Assignee: Hewlett-Packard Company (HP Inc.)
• Inventors: Chaudhuri, Surajit
• Primary Examiner(s): Amsbury, Wayne
• Application Number:
  US08/653,804
• Time in Patent Office: 245 Days
• Field of Search: 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24537 of operators
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A method and apparatus for optimizing a query for a relational database are disclosed. The techniques employed enable a group-by operator to be an internal node. In particular, the optimization techniques include the steps of receiving a query having group-by to be optimized, generating for the query execution plans where placing group-by preceding every internal join node are considered, estimating cost for the execution plans, and choosing the execution plan having the lowest estimated cost.

Claims

1. A method for optimizing a query for a relational database, said method comprising:

(a) receiving a query to be optimized for processing, the query having a group-by operator; and

(b) generating query execution plans having a root node, relations which are joined at nodes and nodes representing group-by operations placed preceding every internal join node for which placement of a group-by operation generates an execution plan equivalent to an execution plan having the group-by operation at the root node, wherein each said group-by operation performs a group-by operation on one of said relations, by;

(b1) identifying grouping columns and aggregate columns for the query; and

(b2) determining, based on the grouping columns and the aggregate columns for the query, whether a node representing a group-by operation can be placed preceding the join nodes of the execution plan;

(c) estimating cost for each execution plans; and

(d) choosing the execution plan having the lowest estimated cost.

2. A method as recited in claim 1, wherein said determining step (b2) comprises:

determining whether the remaining join operators are on foreign keys; and

determining whether aggregate functions of the query satisfy a union property.

3. A method as recited in claim 1, wherein said determining step (b2) further comprises:

identifying candidate aggregating columns for the join node;

determining whether the candidate aggregating columns are a subset of the aggregating columns of the query, and if the candidate aggregating columns are not a subset of the aggregating columns of the query (72);

indicating that no transformation of placing a group by operation prior to the join node is available (74);

otherwise,

determining whether remaining join operations are with foreign keys, and if the remaining join operations are with foreign keys (76);

determining whether the required columns are a subset of the grouping columns of the query, and if the required columns are a subset of the grouping columns of the query (77);

indicating that the invariant property exists and that a group by operation transformation is available (78);

otherwise,

indicating that no group by transformation is available;

and if the remaining join operations are not with foreign keys;

determining whether the aggregate function satisfies the union property, and if the aggregate function does satisfy the union property (80);

indicating that a simple calescing grouping property exists and that a group by operation transformation is available (82);

otherwise,

indicating that no group by operation transformation is available.

4. A method as recited in claim 1, wherein said generating step (b) further comprises:

(b3) generating, for every join node being a join () of a first relation (R) and a second relation (S), the execution plans for Group-By (R[j]) S[j] and Group-By (S[j]) R[j].

5. A query optimizer for a database management system, said system comprising:

a relational database;

means for receiving a query to be optimized, the query including a group-by operator associated with at least one of the nodes of the query;

means for considering query execution plans for the query having a root and nodes representing group-by operations placed preceding internal join nodes wherein such execution plans are equivalent to an execution plan having the group-by operation at the root;

means for estimating cost for the execution plans; and

means for choosing the execution plan based on the estimated cost.

6. A system as recited in claim 5, wherein said considering means further comprise:

determination means having;

means for identifying grouping columns and aggregate columns for the query; and

means for determining, based on the grouping columns and the aggregate columns for the query, whether the join nodes of the execution plan can be preceded by nodes representing group-by operations.

7. In a relational database management system having a query optimizer which optimizes execution of a query, wherein the relational database management system operates to control a computer, wherein the improvement comprises optimizing a query which includes a group-by operator by selecting an execution plan for the query from a set of execution plans including execution plans having group-by operations at internal nodes, wherein each such execution plan has a root node, relations which are joined at nodes and nodes representing group-by operations placed preceding event internal join node for which placement of a group-by operation generates an execution plan equivalent to an execution plan having the group-by operation at the root node, wherein each said group-by operation performs a group-by operation on one of said relations, by:

identifying grouping columns and aggregate columns for the query; and

determining, based on the grouping columns and the aggregate columns for the query, whether a group-by operation can be placed preceding the join nodes of the execution plan.