Automatically and adaptively determining execution plans for queries with parameter markers

Abstract

A method for automatically and adaptively determining query execution plans for parametric queries. A first classifier trained by an initial set of training points is generated using a set of random decision trees (RDTs). A query workload and / or database statistics are dynamically updated. A new set of training points collected off-line is used to modify the first classifier into a second classifier. A database query is received at a runtime subsequent to the off line phase. The query includes predicates having parameter markers bound to actual values. The predicates are associated with selectivities. The query execution plan is determined by identifying an optimal average of posterior probabilities obtained across a set of RDTs and mapping the selectivities to a plan. The determined query execution plan is included in an augmented set of training points that includes the initial set and the new set.

Description

FIELD OF THE INVENTION[0001]The present invention relates to a method and system for automatically and adaptively determining execution plans for queries with parameter markers.BACKGROUND OF THE INVENTION[0002]Query optimization is central to the efficient operation of a modern relational database system. The query optimizer is typically invoked every time a new query enters the system. The optimizer identifies an efficient execution plan for the query, based on available database statistics and cost functions for the database operators. In commercial systems, great care has been taken to reduce the overhead of query optimization. However, the task of the optimizer is complex, and the join ordering problem alone has complexity that is exponential in the number of tables [13] (see Appendix A for a list of cited references). As a result, the cost of optimization itself may represent a significant fraction of the elapsed time between query submission and answer generation.[0003]If iden...

AI Technical Summary

Benefits of technology

[0016]Advantageously, the present invention provides machine learning-based algorithms that automatically and adaptively determine query execution plans for queries having parameter markers. Further, these machine learning-based algorithms accurately model the output of a query optimizer, scale gracefully with the number of query parameters, handle non-linear boundaries in plan space, and achieve high prediction accuracy even when a limited amount of data is available for training.

Problems solved by technology

However, the task of the optimizer is complex, and the join ordering problem alone has complexity that is exponential in the number of tables [13] (see Appendix A for a list of cited references).

As a result, the cost of optimization itself may represent a significant fraction of the elapsed time between query submission and answer generation.

There is a potential problem with this approach.

In fact, often the difference in cost between the optimizer's plan and the cached plan exceeds the optimization time.

However, this locality is lost when many users interact with the system at any given time.

In this setting, query optimization performed for every query instance adds significant overhead in terms of the overall execution time and CPU utilization.

The solution proposed by loannidis [13] fails to scale in the number of parameters, and does not directly handle continuous attributes.

Geometric solutions proposed by Hulgeri and Sudarshan [12] are impractical because of the exponential explosion in the number of parameters and because they do not perform well with a typical real-life workload having multiple categorical attributes or where the underlying data is highly skewed.

Images