A call for order in search space generation process of query optimization

Many search space generation algorithms used by query optimizers focus on efficiently and exhaustively enumerating the set of alternative plans. However, newly emerged systems, such as Object-Relational Mapping (ORM) tools, introduce new challenges to the query optimizer. This is because dynamically generated queries are very complex, relatively inexpensive to execute, and they must be optimized at runtime. For such queries the optimization time could be prohibitively expensive relative to their execution time, if traditional join enumeration algorithms are used. Recently, new heuristics for the query optimization have been proposed which provide feasible alternatives to the classical enumeration algorithms by using greedy techniques to efficiently build an execution plan. In this paper we propose a middle ground approach by introducing new techniques for dynamically ordering the search space of the candidate joins generated by conventional enumeration algorithms. Ordering the enumerated joins based on their properties allows effective pruning of the search space, so that the space size becomes adapted to the current characteristics of the optimization process. Any conventional join enumeration algorithms based on enumerating partitions and using memorization for saving access plans can be extended with our proposed ordered-Par technique. We discuss the enumeration algorithms supported in the SQL Anywhere Optimizer. We present experimental results comparing these algorithms with the new ordered-DPhyp algorithm which applies the ordered-Par technique to the DPhyp algorithm previously proposed in the research literature.