Risk-premium-based database-query optimization
AssigneeHewlett-Packard Development Company, L.P.
InventorsAwny K. Al-Omari, Hansjorg Zeller, Tom C. Reyes
Life of this patent
Estimating the term…
Abstract
Embodiments of the present invention are directed to query-optimization methods and systems that employ risk premiums assigned to database operations in order to increase the robustness and reliability of the evaluation and selection of query-execution plans for executing high-level database queries. Using risk-premium estimates, selection of query-execution plans that include potentially inefficient database operations and inefficient database-operation-execution sequences, often selected by currently available query-optimization methods and systems, are avoided.
Claims17
A database management system comprising:
- one or more computer systems;
- a database-management system encoded as instructions in one or more computer readable media within the one or more computer systems and executed by the one or more computer systems, the database-management system including a query-execution-plan-compilation component that:
- selects an operation, based on a cost estimate for using the operation from among two or more equivalent operations for use in a query-execution plan by applying a risk premium to the cost estimate, for using the operation;
- selects one of two or more alternative query-execution plans for execution of a query based on a cost estimate for executing each alternative query-execution plan that includes one or more risk premiums associated with one or more operations included in the query-execution plan;
- determines a cost estimate for executing a query-execution plan, the cost comprising one or more of:
- an elapsed time for execution of the query-execution plan by the database management system;
- a time for which one or more components of the database management system is used to execute the query-execution plan;
- and
- an amount of one or more computational resources used to execute the query-execution plan, including memory, communications bandwidth, processor bandwidth;
- and
- wherein the risk premium is computed to reflect a potential underestimation of the cost for executing the database operation when underlying estimates of characteristics of tables to which the database operation is applied are incorrect.
A method for evaluating an execution cost of a particular database operation within a query-execution plan encoded as computer instructions in a computer-readable medium, the method comprising:
- estimating the costs of executing all database operations according to the query-execution-plan the cost comprising one or more of:
- an elapsed time for execution of the query-execution plan by the database management system;
- a time for which one or more components of the database management system is used to execute the query-execution plan;
- and
- an amount of one or more computational resources used to execute the query-execution plan, including memory, communications bandwidth, processor bandwidth;
- estimating the cost of executing the particular database operation;
- combining the estimated costs of executing all database operations that are executed prior to the particular database operation according to the query-execution-plan and the estimated cost of executing the particular database operation to produce an intermediate cost estimate;
- and
- applying a risk-premium multiplier to the intermediate cost estimate, wherein the risk premium is computed to reflect a potential underestimation of the cost for executing the database operation when underlying estimates of characteristics of tables to which the database operation is applied are incorrect.
A tangible, non-transitory, machine-readable medium that stores machine-readable instructions executable by a processor for evaluating an execution cost of a particular database operation within a query-execution plan, the tangible, non-transitory, machine-readable medium comprising:
- machine-readable instructions for a query-execution-plan-compilation component that, when executed by the processor, selects an operation, based on a cost estimate for using the operation, from among two or more operations for use in a query-execution plan by applying a risk premium to the cost estimate for using the operation, wherein a risk premium for a database operation is a cost-estimate multiplier that reflects a risk associated with selecting the database operation for incorporation into a query-execution plan, machine-readable instructions for a query-execution-plan-compilation component that, when executed by the processor, selects one of two or more alternative query-execution plans for execution of a query based on a cost estimate for executing each alternative query-execution plan that includes one or more risk premiums associated with one or more operations included in the query-execution plan
- machine-readable instructions for a query-execution-plan-compilation component that, when executed by the processor, determines a cost estimate for executing a query-execution plan, the cost comprising one or more of:
- an elapsed time for execution of the query-execution plan by the database management system;
- a time for which one or more components of the database management system is used to execute the query-execution plan;
- and
- an amount of one or more computational resources used to execute the query-execution plan, including memory, communications bandwidth, processor bandwidth;
- and
- wherein the risk premium is computed to reflect a potential underestimation of the cost for executing the database operation when underlying estimates of characteristics of tables to which the database operation is applied are incorrect.
Dependencies are inferred from the claim text.