An Efficient Framework for Order Optimization

• Authors: Thomas Neumann;Guido Moerkotte
• Venue: ICDE '04 Proceedings of the 20th International Conference on Data Engineering
• Year: 2004

Since the introduction of cost-based query optimization,the performance-critical role of interesting orders has beenrecognized. Some algebraic operators change interestingorders (e.g. sort and select), while others exploit interesting orders (e.g. merge join). The two operations performed by any query optimizer during plan generation are 1) computing the resulting order given an input order and an algebraic operator and 2) determining the compatibility between a given input order and the required order a given algebraic operator can beneficially exploit. Since these twooperations are called millions of times during plan generation, they are highly performance-critical. The third crucial parameter is the space requirement for annotating every plan node with its output order.Lately, a powerful framework for reasoning about ordershas been developed, which is based on functional dependencies. Within this framework, the current state-of-the-art algorithms for implementing the above operations both havea lower bound time requirement of 驴(n), where n is thenumber of functional dependencies involved. Further, thelower bound for the space requirement for every plan nodeis 驴(n).We improve these bounds by new algorithms with uppertime bounds O(1). That is, our algorithms for both operations work in constant time during plan generation, after a one-time preparation step. Further, the upper bound for thespace requirement for plan nodes is O(1) for our approach.Besides, our algorithm reduces the search space by detecting and ignoring irrelevant orderings. Experimental results with a full fledged query optimizer show that our approachsignificantly reduces the total time needed for plan generation. As a corollary of our experiments, it follows that thetime spent for order processing is a non-negligible part ofplan generation.