A new heuristic for optimizing large queries

Abstract

There is a number of OODB optimization techniques proposed recently, such as the translation of path expressions into joins and query unnesting, that may generate a large number of implicit joins even for simple queries. Unfortunately, most current commercial query optimizers are still based on the dynamic programming approach of System R, and cannot handle queries of more than ten tables. There is a number of recent proposals that advocate the use of combinatorial optimization techniques, such as iterative improvement and simulated annealing, to deal with the complexity of this problem. These techniques, though, fail to take advantage of the rich semantic information inherent in the query specification, such as the information available in query graphs, which gives a good handle to choose which relations to join each time. This paper presents a polynomial-time algorithm that generates a good quality order of relational joins. It is a bottom-up greedy algorithm that always performs the most profitable joins first. Our heuristic is superior to related approaches in that it generates bushy trees, it takes into account both the sizes of intermediate results and the predicate selectivities, and it always updates the query graph to reflect the new sizes and the new selectivities.

This work is supported in part by the National Science Foundation under grant IRI-9509955.