CMSC 424 Database design Lecture 18 Query optimization Mihai Pop Admin More midterm solutions Projects do not be late Introduction Alternative ways of evaluating a given query Equivalent expressions Different algorithms for each operation Introduction Cont An evaluation plan defines exactly what algorithm is used for each operation and how the execution of the operations is coordinated Introduction Cont Cost difference between evaluation plans for a query can be enormous E g seconds vs days in some cases Steps in cost based query optimization Generate logically equivalent expressions using equivalence rules Annotate resultant expressions to get alternative query plans Choose the cheapest plan based on estimated cost Estimation of plan cost based on Statistical information about relations Examples Statistics estimation for intermediate results number of tuples number of distinct values for an attribute to compute cost of complex expressions Cost formulae for algorithms computed using statistics Generating Equivalent Expressions Transformation of Relational Expressions Two relational algebra expressions are said to be equivalent if the two expressions generate the same set of tuples on every legal database instance Note order of tuples is irrelevant In SQL inputs and outputs are multisets of tuples Two expressions in the multiset version of the relational algebra are said to be equivalent if the two expressions generate the same multiset of tuples on every legal database instance An equivalence rule says that expressions of two forms are equivalent Can replace expression of first form by second or vice versa Equivalence Rules 1 Conjunctive selection operations can be deconstructed into a sequence of individual selections 1 2 E 1 2 Selection operations are commutative 1 2 E 2 2 E 1 E 3 Only the last in a sequence of projection operations is needed the others can be omitted 4 L1 L2 Ln E L1 E Selections can be combined with Cartesian products and theta joins E1 X E2 E1 1 E1 2 E2 E2 E1 1 2 E2 Equivalence Rules Cont 5 Theta join operations and natural joins are commutative E1 E2 E2 E1 6 a Natural join operations are associative E1 E2 E3 E1 E2 E3 b Theta joins are associative in the following manner E1 1 E2 2 3 E3 E1 1 3 E2 2 E3 where 2 involves attributes from only E2 and E3 Pictorial Depiction of Equivalence Rules Transformation rules Many more Read chapter 14 Transformation Example Pushing Selections Query Find the names of all customers who have an account at some branch located in Brooklyn customer name branch city Brooklyn branch account depositor Transformation using rule 7a customer name branch city Brooklyn branch account depositor Performing the selection as early as possible reduces the size of the relation to be joined Example with Multiple Transformations Query Find the names of all customers with an account at a Brooklyn branch whose account balance is over 1000 customer name branch city Brooklyn balance 1000 branch account depositor Transformation using join associatively Rule 6a customer name branch city Brooklyn balance 1000 branch account depositor Second form provides an opportunity to apply the perform selections early rule resulting in the subexpression branch city Brooklyn branch balance 1000 account Thus a sequence of transformations can be useful Multiple Transformations Cont Transformation Example Pushing Projections customer name branch city Brooklyn branch account depositor When we compute branch city Brooklyn branch account we obtain a relation whose schema is branch name branch city assets account number balance Push projections using equivalence rules 8a and 8b eliminate unneeded attributes from intermediate results to get customer name account number branch city Brooklyn branch account depositor Performing the projection as early as possible reduces the size of the relation to be joined Join Ordering Example For all relations r1 r2 and r3 r1 r2 r3 r1 r2 r3 Join Associativity If r2 r3 is quite large and r1 r2 is small we choose r1 r2 r3 so that we compute and store a smaller temporary relation Join Ordering Example Cont Consider the expression customer name branch city Brooklyn branch account depositor Could compute account depositor first and join result with branch city Brooklyn branch but account depositor is likely to be a large relation Only a small fraction of the bank s customers are likely to have accounts in branches located in Brooklyn it is better to compute branch city Brooklyn branch account first Enumeration of Equivalent Expressions Query optimizers use equivalence rules to systematically generate expressions equivalent to the given expression Can generate all equivalent expressions as follows Repeat apply all applicable equivalence rules on every equivalent expression found so far add newly generated expressions to the set of equivalent expressions Until no new equivalent expressions are generated above The above approach is very expensive in space and time Two approaches Optimized plan generation based on transformation rules Special case approach for queries with only selections projections and joins Implementing Transformation Based Optimization Space requirements reduced by sharing common sub expressions when E1 is generated from E2 by an equivalence rule usually only the top level of the two are different subtrees below are the same and can be shared using pointers E g when applying join commutativity E1 E2 Same sub expression may get generated multiple times Detect duplicate sub expressions and share one copy Time requirements are reduced by not generating all expressions Dynamic programming We will study only the special case of dynamic programming for join order optimization
View Full Document
Unlocking...