1 / 12

Chapter 16: The Query Compiler [ (16.2) Algebraic laws for improving Query Plans]

Chapter 16: The Query Compiler [ (16.2) Algebraic laws for improving Query Plans]. Neel Parikh (section 2) Roll no : 210 4/16/2008. Algebraic laws (Agenda). Commutative and Associative laws Laws involving Selection Laws involving Projection Laws about join and products

zaina
Download Presentation

Chapter 16: The Query Compiler [ (16.2) Algebraic laws for improving Query Plans]

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Chapter 16: The Query Compiler[ (16.2) Algebraic laws for improving Query Plans] Neel Parikh (section 2) Roll no : 210 4/16/2008

  2. Algebraic laws (Agenda) • Commutative and Associative laws • Laws involving Selection • Laws involving Projection • Laws about join and products • Laws involving duplicate elimination • Laws involving grouping and aggregation

  3. Commutative and Associative laws • Commutative Law:Irrespective of the order of arguments of the operator; the result will be same. • x + y = y + x and x × y = y × x. • - is not commutative. Eg: x – y ≠ y – x • Associative Law:We may group two uses of operator wither from left or right • (x + y) + z = x + (y + z) and (x × y) × z = x × (y × z). • - is not associative. Eg: (x - y) - z ≠ x - (y - z)

  4. Associative and Commutative operators • Operators which are both commutative and associative are: • Cartesian Product, Join, Union, Intersection • Example of Cartesian product and union is given below R × S = S × R; (R × S) × T = R × (S × T). R U S = S U R; (R U S) U T = R U (S U T). General method of verifying: check that every tuple produced by the expression on the left is also produced by the expression on the right and vice – versa.

  5. Laws involving Selection • Splitting Laws • AND C2(R) = ( (R)) • C1 OR C2 (R) = ( (R)) Us ( (R)) • 2nd law OR works only if relation is a set • Order of C1 and C2 is flexible. In general we can swap the order of any sequence of operators.

  6. Laws involving Selection (cont….) Laws of selection with binary operators like product, union, intersection, difference, join. (3 laws) • For a union, the selection must be pushed to both arguments. • C (R U S) = c (R) U c (S) • For a difference, the selection must be pushed to first argument and optionally to second. • C (R - S) = c (R) - S • For others, it is only required that the selection must be pushed to one argument. • C (R x S) = c (R) x S

  7. Laws involving Projection • Consider term E  x • E : attribute, or expression involving attributes and constants. • All attributes in E are input attributes of projection and x is output attribute • Simple projection: if a projection consists of only attributes. • Example: π a,b,c (R) is simple. a,b,c are input and output attributes. • Projection can be introduced anywhere in expression tree as long as it only eliminates attributes that are never used.

  8. Laws involving Projection (cont….) [examples] • πL(R S) = πL(πM(R) πN(S)) ; M and N are all attributes of R and S that are either join (in schema of both R and S) or input attributes of L • πL(R c S) = πL(πM(R) cπN(S)) ; M and N are all attributes of R and S that are either join(mentioned in condition of C ) or input attributes of L • πL(R x S) = πL(πM(R) x πN(S)) ; M and N are all attributes of R and S that are input attributes of L Projections cannot be pushed below set unions or either of set or bag versions of intersection or difference at all.

  9. Laws about join and products R c S = C (R U S) R S = πL( c (R x S) Here; c is the condition that equates each pair of attributes from R and S, and L is list that includes one attribute from each equated pair and all others of R and S.

  10. Laws involving duplicate elimination Laws that “push” δ (delta) through other operator • δ(R x S) = δ(R) x δ(S) • δ(R S) = δ(R) δ(S) • δ(R c S) = δ(R) c δ(S) • δ( c(R)) = c(δ(R)) δ eliminates duplicates from a bag, but cannot be pushed through all the operators

  11. Laws involving grouping and aggregation Operator: γ(gamma) • γ absorbs a δ • δ (γL(R) ) = γL(R) • We may project useless attributes from the argument prior to applying γ operator. • γL(R) = γL(πM(R)) if M is list of all attributes of R that are in L

  12. Laws involving grouping and aggregation (cont…) • Some aggregations like MIN and MAX are not affected by presence or absence of duplicates • Others like SUM,COUNT,AVG produce different values if duplicates are eliminated prior to aggregation.

More Related