Method and apparatus for optimizing queries having group-by operators
AssigneeHewlett-Packard Company
InventorSurajit Chaudhuri
Life of this patent
Estimating the term…
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.
Claims7
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.
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.
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.
Dependencies are inferred from the claim text.