Optimal ordering of selections and joins in acyclic queries with expensive predicates.pdfVIP

Optimal Ordering of Selections and Joins inAcyclic Queries with Expensive PredicatesWolfgang Scheufele Guido MoerkotteRWTH AachenLehrstuhl f}ur Informatik III52056 Aachen, Germanyfws jmoerg@rmatik.rwth-aachen.deAbstractThe generally accepted optimization heuristics of pushing selections down does notyield optimal plans in the presence of expensive predicates. Therefore, several re-searchers have proposed algorithms for the optimal ordering of expensive joins andselections in a query evaluation plan. All of these algorithms have an exponentialrun time. For a special case, we propose a polynomial algorithm which { in oneintegrated step { computes the optimal join order and places expensive predicatesoptimally within the join tree. The special case is characterized by the followingstatements:1. only left-deep trees are considered,2. no cross-products are considered,3. the cost function has to exhibit the ASI property, and4. cheap selections are pushed before-hand.1 IntroductionTraditional work on algebraic query optimization has mainly focused on the problem of or-dering joins in a query. Restrictions like selections and projections are generally treated bypush-down rules. According to these, selections and projections should be pushed downthe query plan as far as possible. These heuristic rules worked quite well for traditionalrelational database systems where the evaluation of selection predicates is of neglectablecost and every selection reduces the cost of subsequent joins. As pointed out by Kemper,Moerkotte and Steinbrunn [8] and Hellerstein and Stonebraker [4], this is no longer true formodern database systems like extensible DBMSs or object-oriented DBMSs where usersare allowed to implement arbitrary complex functions in a general-purpose programminglanguage. Even in traditional SQL database systems expensive predicates occur due tosubqueries which can not be unnested.Several researchers have proposed algorithms for the optimal ordering of expensive joinsand sel