System and method for optimizing database queries

• US 5,822,747 A
• Filed: 08/23/1996
• Issued: 10/13/1998
• Est. Priority Date: 08/23/1996
• Status: Expired due to Term

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Tandem Computers Incorporated (HP Inc.)
• Inventors: Zeller, Hansjorg, Graefe, Goetz, Celis, Pedro, Vaishnav, Jay
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): CHANNAVAJJALA, SRIRAMA T
• Application Number:
  US08/702,106
• Time in Patent Office: 781 Days
• Field of Search: 707/2, 707/3, 707/4, 707/203, 707/6
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24542 Plan optimisation
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

A system and method for optimizing a database query is herein disclosed. The system consists of a search engine and a database implementor that determines an optimal plan for executing a SQL query. The SQL query is represented as a query tree consisting of a number of nested expressions. The search engine generates a number of plans from which an optimal plan is selected. Plans are generated through the application of a set of rules consisting of implementation and transformation rules. Implementation rules are used to obtain plans. Transformation rules are used to determine equivalent expressions. A plan for the query tree entails finding plans for each expression within the tree where each plan is generated in accordance with a prescribed set of rules. The database implementor selects the set of rules such that more promising plans are generated rather than generating all possible plans. In a preferred embodiment of the invention, multiple passes are made by the search engine in order to determine the optimal plan. In a first pass, implementation rules are used in order to generate a first plan having a cost that is used as a threshold when generating for additional plans. In each subsequent pass, a set of implementation and transformation rules is used to generate one or more plans whose cost does not exceed the threshold. An optimal plan is selected from the generated plans as the one having the lowest cost.

Claims

1. A method for generating a plan for executing a database query, comprising:

(a) representing said database query as a query tree including one or more levels of expressions, each expression including zero or more expressions as inputs, each level of expression other than a top level expression having one or more expressions that are input to a higher level expression at a preceding level, the top level expression representing a root expression;

(b) storing in a memory a plurality of implementation and transformation rules, an implementation rule transforming a logical expression into a physical expression, a transformation rule transforming a logical expression into an equivalent logical expression;

(c) partitioning said query tree into one or more levels of subproblems, each subproblem including at least one logical expressions, a top subproblem level having one of said subproblems representing said root expression, each subproblem at each subsequent subproblem level representing one of said inputs to a corresponding higher level expression at a preceding level;

(d) determining an order for analyzing each of said subproblems, said order analyzing said subproblems from a lowest subproblem level to a top subproblem level such that each input is analyzed prior to its corresponding higher level expression;

(e) determining a first plan and a base cost for each subproblem, wherein the first plan comprises only physical expressions; and

(f) using said order, starting at said lowest subproblem level,

(1) generating equivalent physical expressions for least one logical expression in each subproblem by applying at least one transformation rule and at least one implementation rule,

(i) for each equivalent physical expression determine an associated equivalent-expression cost,

(ii) if the equivalent-expression cost is less than the base cost, then denoting the associated equivalent physical expression as a plan and the equivalent-expression cost as the plan cost,

(iii) selecting the plan with the lowest plan cost for use in a next higher subproblem level, and

(2) repeatedly performing said generating step for higher subproblem levels until a plan is selected for said top subproblem level.

2. The method of claim 1 wherein said step (b) of storing stores a promise value for at least a subset of the transformation rules, and

said step (f)(1) of selecting transformation rules to apply, selects and applies transformation rules having a promise value more than a predetermined promise threshold.

3. The method of claim 1 wherein said step (f)(1) of generating equivalent expressions stops after a predetermined number of equivalent expressions are generated.

4. The method of claim 1 wherein said step (f)(1) of generating equivalent expressions further includes the step of selecting transformation rules to apply based on predetermined search heuristics.

5. The method of claim 4 wherein the predetermined search heuristics include promise functions, cutoff functions, guidance functions and match methods.

6. The method of claim 4 said step (f)(1) of generating equivalent expressions generates equivalent expressions based on a guidance function that considers a prior application of said transformation rules.

7. The method of claim 4 wherein said step (e) of determining the base cost for each subproblem and the root expression generates the first plan for each subproblem and the root expression by the following steps:

using said order, starting at said lowest subproblem level,

generating an equivalent base physical expression for each subproblem having a logical expression by applying only implementation rules, each implementation rule having an implementation cost, the implementation rule being designated as the first plan, and the implementation cost is the base plan cost;

repeating said step of generating an equivalent base physical expression for the next higher subproblem level until the first plan is generated for said root level.

8. The method of claim 4 wherein said step of generating an equivalent expression for each subproblem, generates an equivalent expression for each subproblem, such that each subproblem is assigned to a separate task in a task data structure, and a task scheduler manages the execution of the tasks from the task data structure.

9. A method for generating at least one plan for executing a database query, said method comprising the steps of:

(a) providing a task scheduler that manages execution of tasks placed in a task data structure, said task scheduler selecting one or more tasks for execution from said task data structure;

(b) storing in a memory a query tree representing said database query, said query tree containing a plurality of logical expressions, each logical expression including an operator having zero or more inputs, each input being a logical expression, a subset of said expressions representing subtrees having one or more inputs, one such logical expression being a root expression;

(c) storing in a memory a set of rules, a first subset of said rules including zero or more transformation rules, a second subset of said rules including zero or more implementation rules, each of said transformation rules transforming a logical expression into an equivalent expression, each of said implementation rules transforming a logical expression into a physical expression;

(d) storing in a memory a search data structure for storing a plurality of groups, each group including at least one logical expression from said query tree, zero or more equivalent expressions associated therewith and at least one plan, said equivalent expressions including zero or more equivalent logical expressions and zero or more equivalent physical expressions;

(e) placing onto said task data structure an OPTIMIZE_-- GROUP task for the group associated with said root expression;

(f) when executing an OPTIMIZE_-- GROUP task for a specific group;

determining if a suitable plan for said specific group exists in said search data structure, and

if no suitable plan exists for said specific group, for each logical expression in said specific group, placing onto said task data structure an OPTIMIZE_-- EXPRESSION task for each of said logical expressions;

(g) when executing an OPTIMIZE_-- EXPRESSION task for a specific logical expression;

for at least one rule in the first and second subsets of rules, placing on said task data structure an APPLY_-- RULE task for said specific logical expression;

(h) when executing an APPLY_-- RULE task for a p articular expression and a specific rule;

(i) applying said specific rule zero or more times producing zero or more new equivalent expressions,

(ii) storing each of said new equivalent expressions in said search data structure,

(iii) for each new equivalent physical expression, placing onto said task data structure an OPTIMIZE_-- INPUTS task for said new equivalent physical expression, and

(iv) for each new equivalent logical expression, placing onto said task data structure an OPTIMIZE_-- EXPRESSION task for s aid new equivalent logical expression; and

(i) when executing an OPTIMIZE_-- INPUTS task for a designated expression, providing a plan for said designated expression once plans for each input to said designated expression are obtained.

10. The method of claim 9, further comprising the step of:

upon completion of said tasks, determining a best plan for said database query from said plans included in said group associated with said root expression.

11. The method of claim 10,

wherein said step (i) obtains said plans for each input to said designated expression by placing onto said task data structure an OPTIMIZE_-- GROUP task for each of said inputs.

12. The method of claim 9,

wherein said database query has one or more physical properties associated therewith;

and

wherein said suitable plan matches at least each physical property associated with said database query.

13. The method of claim 9,

wherein said step (g) selects said rules in accordance with a prescribed criteria.

14. The method of claim 9,

wherein said step (h) further comprises the steps of:

before said step (h)(i), determining all possible bindings, for said rule; and

repeating steps (i)-(iv) for each binding.

15. The method of claim 9,

wherein each of said new equivalent expressions is stored in said search data structure if said new equivalent expression does not already reside in said search data structure.

16. The method of claim 9,

said step (a) including placing on said task data structure an EXPLORE_-- GROUP task for each input of the rule for which the APPLY_-- RULE task was placed on said task data structure, excluding inputs that are cut or tree operators;

the method further comprising the steps of;

when executing an EXPLORE_-- GROUP task for a particular group;

if said group has not been explored previously, for each logical expression associated with said particular group, placing onto said task data structure an EXPLORE_-- EXPRESSION task for each logical expression associated with said particular group; and

when executing an EXPLORE_-- EXPRESSION task for a certain logical expression;

determining a second set of rules for application to said certain logical expression,

for each rule of said second set;

placing onto said task data structure an APPLY_-- RULE task for said certain logical expression with an indicator specifying said EXPLORE_-- EXPRESSION task, and

if said rule has an input that is a subtree, placing onto said task data structure an EXPLORE_-- GROUP task for said certain logical expression'"'"'s group.

17. The method of claim 16,

wherein said step (g)(ii) further comprises the step of placing onto the task data structure an EXPLORE_-- GROUP task with said specific logical expression'"'"'s group for each input to said specific rule that is a subtree;

and

wherein said step (h) further comprises the step of placing onto the task data structure an EXPLORE_-- EXPRESSION task with said new equivalent expression when said indicator specifies said EXPLORE_-- EXPRESSION task.

18. The method of claim 17,

wherein said OPTIMIZE_-- EXPRESSION task provides said EXPLORE_-- GROUP task a first guidance structure for use by said EXPLORE_-- EXPRESSION task in generating said second set of rules;

wherein said APPLY_-- RULE task provides said EXPLORE_-- EXPRESSION task a second guidance structure for use by said EXPLORE_-- EXPRESSION task in generating said second set of rules;

wherein said APPLY_-- RULE task provides said OPTIMIZE_-- EXPRESSION task a third guidance structure for use by said OPTIMIZE_-- EXPRESSION task in generating said first set of rules;

wherein said OPTIMIZE_-- INPUTS task provides said OPTIMIZE_-- GROUP task a fourth guidance structure for use by said OPTIMIZE_-- EXPRESSION task in generating said first set of rules.

19. A database query optimization system, comprising:

a query tree representing a database query, said query tree including one or more levels of expressions, each expression including zero or more expressions as inputs, each level of expression other than a top level expression having one or more expressions that are input to a higher level expression at a preceding level, the top level expression representing a root expression;

a memory for storing a plurality of implementation and transformation rules, each transformation rule transforming a logical expression into an equivalent logical expression, each implementation rule transforming a logical expression into a physical expression;

a search engine for generating one or more plans that execute said database query, said search engine including instructions that

partition said database query into one or more subproblems, each subproblem including at least one logical expression, a top subproblem level having one of said subproblems representing said root expression, each subproblem at each subsequent subproblem level representing one of said inputs to a corresponding higher level expression at a preceding level,

determine an order for analyzing each of said subproblems from a lowest subproblem level to the top subproblem level such that each input is analyzed prior to its corresponding higher level expression,

determine a first plan and a base cost for each subproblem, wherein the first plan comprises only physical expressions;

using said order, starting at said lowest subproblem level,

(1) generate equivalent physical expressions for at least one logical expression in each subproblem by applying at least one transformation rule and at least one implementation rule,

(i) for each equivalent physical expression determine an associated equivalent-expression cost.

(ii) if the equivalent-expression cost is less than the base cost, then denote the associated equivalent physical expression as a plan and the equivalent-expression cost as the plan cost,

(iii) select the plan with the lowest plan cost for use in a next higher subproblem level, and

(2) repeatedly perform said generating step for higher subproblem levels until a plan is selected for said top subproblem level; and

a database implementor for selecting one or more of said transformation rules for application to said logical expressions in each of said subproblems.

20. The system of claim 19 further comprising:

a task data structure for storing a task associated with each subproblem for generating each of said equivalent physical expressions; and

a task scheduler that schedules said tasks stored in said task data structure for execution.

21. The system of claim 19, wherein said database implementor further comprises search heuristics for use in selecting those rules that will generate viable plans.

22. The system of claim 19, further comprising

promise values associated with each transformation rule, wherein said database implementor selects a transformation rule to apply if the associated promise value is more than a predetermined threshold.

23. The system of claim 19 further comprising,

a search data structure for storing expressions generated by said search engine'"'"'s application of said rules.

24. A computer program product for use in conjunction with a computer system, the computer program product comprising a computer readable storage medium and a computer program mechanism embedded therein, the computer program mechanism comprising:

a query tree representing a database query, said query tree including one or more levels of expressions, each expression including zero or more expressions as inputs, each level of expression other than a top level having one or more expressions that are input to a higher level expression at a preceding level, the top level expression representing a root expression;

a plurality of implementation and transformation rules, each transformation rule transforming a logical expression into an equivalent logical expression, each implementation rule transforming a logical expression into a physical expression;

a search engine for generating one or more plans that execute said database query, said search engine including instructions that

partition said database query into one or more subproblems, each subproblem including at least one logical expressions, a top subproblem level having one of said subproblems representing said root expression, each subproblem at each subsequent subproblem level representing one of said inputs to a corresponding higher level expression at a preceding level,

determine an order for analyzing each of said subproblems from a lowest subproblem level to top subproblem level such that each input is analyzed prior to its corresponding higher level expression,

determine a first plan and a base cost for each subproblem, wherein the first plan comprises only physical expressions;

using said order, starting at said lowest subproblem level,

(1) generate equivalent physical expressions for at least one logical expression in each subproblem by applying at least one transformation rule and at least one implementation rule,

(i) for each equivalent physical expression determine an associated equivalent-expression cost,

(ii) if the equivalent-expression cost is less than the base cost, then denote the associated equivalent physical expression as a plan and the equivalent-expression cost as the plan cost,

(iii) select the plan with the lowest plan cost for use in a next higher subproblem level, and

(2) repeatedly perform said generating step for higher subproblem levels until a plan is selected for said top subproblem level; and

a database implementor for selecting one or more of said transformation rules for application to said logical expressions in each of said subproblems.

25. The computer program product of claim 24 further comprising:

a task data structure for storing a task associated with each subproblem for generating each of said equivalent physical expressions; and

a task scheduler that schedules said tasks stored in said task data structure for execution.

26. The computer program product of claim 24, wherein

said database implementor further comprises search heuristics for use in selecting those rules that will generate viable plans.

27. The computer program product of claim 24, further comprising promise values associated with each transformation rule,

wherein said database implementor selects a transformation rule to apply if the associated promise value exceeds a predetermined threshold.

28. The medium of claim 24 further comprising,

a search data structure for storing expressions generated by said search engine'"'"'s application of said rules.