Optimizing parameterized queries in a relational database management system
First Claim
1. A system for generating a dynamic plan comprising:
- a processor;
and memory in communication with the processor, the memory comprising;
a parameter distribution component that receives a parameterized query and selects a representative sample of parameter values based on parameter values employed by users to execute queries in view of an entire parameter space;
a costing analysis component that calculates costs of plan options corresponding to the selected representative sample of parameter values employed by users to execute queries, the plan options set forth execution of queries; and
a plan generation component that;
(i) generates the dynamic plan, the dynamic plan generated by incorporating at least two plan options as a function of the calculated costs of plan options corresponding to the selected representative sample of parameter values employed by users to execute queries, the at least two plan options comprising a first plan invoked for relatively higher parameter values and a second plan invoked for relatively lower parameter values, (ii) adjusts the dynamic plan based on a balance of optimality and simplicity, and (iii) maximizes simplicity of the dynamic plan while staying within a predetermined bound of suboptimality.
2 Assignments
0 Petitions
Accused Products
Abstract
Parameterized queries are optimized by a transformational optimizer. The optimizer produces a dynamic plan that embeds multiple plan options that may be selected to execute a particular query. Parameter distribution improves query execution efficiency and performance by exploring a sample parameter space representative of the parameter values actually used. The dynamic plans can be simplified while maintaining an acceptable level of optimality by reducing the number of plan options. The reduction is achieved by eliminating switch unions to alternatives that are close in cost. Both approaches of parameter space exploration and dynamic plan generation are deeply integrated into the query optimizer.
-
Citations
10 Claims
-
1. A system for generating a dynamic plan comprising:
a processor; and memory in communication with the processor, the memory comprising; a parameter distribution component that receives a parameterized query and selects a representative sample of parameter values based on parameter values employed by users to execute queries in view of an entire parameter space; a costing analysis component that calculates costs of plan options corresponding to the selected representative sample of parameter values employed by users to execute queries, the plan options set forth execution of queries; and a plan generation component that;
(i) generates the dynamic plan, the dynamic plan generated by incorporating at least two plan options as a function of the calculated costs of plan options corresponding to the selected representative sample of parameter values employed by users to execute queries, the at least two plan options comprising a first plan invoked for relatively higher parameter values and a second plan invoked for relatively lower parameter values, (ii) adjusts the dynamic plan based on a balance of optimality and simplicity, and (iii) maximizes simplicity of the dynamic plan while staying within a predetermined bound of suboptimality.- View Dependent Claims (2, 3, 4)
-
5. A method that facilitates dynamic plan generation, comprising the following acts:
-
extracting and optimizing for a sample of parameter values from a parameter space based on commonly used parameter values employed by users to execute queries; calculating costs associated with a plurality of query execution plan options that provide support for query execution; generating a dynamic query execution plan that integrates at least two query execution plan options based on the calculated costs of plan options corresponding to the selected representative sample of parameter values employed by users to execute queries; tuning the dynamic plan as a function of query execution optimization and simplicity of plan structure, wherein the cost of the tuned dynamic plan is bound by;
X<
=Y+(A−
B)where X represents the cost of the tuned dynamic plan, Y represents the cost of the dynamic plan prior to tuning, A represents the cost of a child subplan within the dynamic plan prior to tuning, and B represents the cost of a switch union subplan within the dynamic plan prior to tuning; and
executing a parameterized query with the dynamic query execution plan.- View Dependent Claims (6, 7, 8, 9, 10)
-
Specification