CS4432: Database Systems II

1. CS4432: Database Systems II Logical Plan Rewriting

2. SQL query parse parse tree convert answer logical query plan execute apply laws statistics Pi “improved” l.q.p pick best estimate result sizes {(P1,C1),(P2,C2)...} l.q.p. +sizes estimate costs consider physical plans {P1,P2,…..} query processing

3. Query in SQL  Query Plan in Algebra (logical)  Other Query Plan in Algebra (logical)

4. Query plan 1 (in relational algebra) B,D sR.A=“c” S.E=2  R.C=S.C X R S

5. Query plan 2 (in relational algebra) B,D sR.A = “c”sS.E = 2 R S natural join on R.C=S.C

6. Relational algebra optimization • What are transformation rules ? • preserve equivalence • What are good transformations? • reduce query execution costs

7. Can also write as trees, e.g.: T R R S S T Rules:Natural join rewriting. R S = S R (R S) T = R (S T)

8. Rules: Other binary operators ? R S = S R (R S) T = R (S T) • What about : • Cross product? • Condition join? • Union? • Intersection ? • Difference ?

9. Rules: Selects sp1p2(R)= sp1 [ sp2 (R)] [ sp1 (R)] U [ sp2 (R)] sp1vp2(R) =

10. Bags vs. Sets R = {a,a,b,b,b,c} S = {b,b,c,c,d} What about union R U S = ? • Option 1 SUM • R U S = {a,a,b,b,b,b,b,c,c,c,d} • Option 2 MAX • R U S = {a,a,b,b,b,c,c,d}

11. Which option makes this rule work ?sp1vp2 (R) = sp1(R) U sp2(R) Example: R={a,a,b,b,b,c} P1 satisfied by a,b; P2 satisfied by b,c • sp1vp2 (R) = {a,a,b,b,b,c}sp1(R) = {a,a,b,b,b}sp2(R) = {b,b,b,c}sp1(R) U sp2 (R) = {a,a,b,b,b,c} Let us try MAX():

12. Which option makes this rule work ?sp1vp2 (R) = sp1(R) U sp2(R) Example: R={a,a,b,b,b,c} P1 satisfied by a,b; P2 satisfied by b,c • sp1vp2 (R) = {a,a,b,b,b,c}sp1(R) = {a,a,b,b,b}sp2(R) = {b,b,b,c}sp1(R) U sp2 (R) = {a,a,b,b,b,b,b,b,c} What about Sum()?

13. Which option makes this rule work ?sp1p2(R)= sp1 [ sp2 (R)] Example: R={a,a,b,b,b,c} P1 satisfied by a,b; P2 satisfied by b,c What about MAX versus SUM ?

14. Yet another example ! Senators (……) Reps (……) T1 = pyr,state Senators; T2 = pyr,state Reps T1 Yr State T2 Yr State 97 CA 99 CA 99 CA 99 CA 98 AZ 98 CA Union? “Sum” option makes more sense!

15. Executive Decision -> Use “SUM” option for bag unions -> CAREFUL ! Some rules cannot be used for bags

16. Rules: Project Let: X = set of attributes Y = set of attributes XY = X U Y pxy (R) = px [py (R)]

17. [sp (R)] S R [sq (S)] Rules:s + combined Let p = predicate with only R attributes q = predicate with only S attributes m = predicate with both R and S attribs sp (R S) = sq (R S) =

18. Rules:s + combined spq (R S) = ? Rule can be derived !

19. Derivation for rule : spq (R S) = sp [sq (R S) ] = sp[ R sq (S) ] = [sp (R)] [sq (S)]

20. Rules:s + combined (continued) More Rules can be Derived: spq (R S) = spqm (R S) = spvq (R S) =

21. We did one, do others on your own : spq (R S) = [sp (R)] [sq (S)] spqm (R S) = sm[(sp R) (sq S)] spvq (R S) = [(sp R) S] U [R(sq S)]

22. pxz px Rules:p,s combined Let x = subset of R attributes z = attributes in predicate P (subset of R attributes) px[sp (R) ] = {sp [ px (R) ]}

23. pxy{[pxz (R) ][pyz (S) ]} Rules:p, combined Let x = subset of R attributes y = subset of S attributes z = intersection of R,S attributes pxy (R S)=

24. Which are “good” transformations?

25. Conventional wisdom: do projects early Example: relation R(A,B,C,D,E) predicate P: (A=3)  (B=“cat”) pE {sp(R)} vs. pE {sp{pABE(R)}}

26. What if we have A, B indexes? But B = “cat” A=3 Intersect pointers to get pointers to matching tuples! Then better to do projection later !

27. Which are “good” transformations? sp1p2 (R) sp1 [sp2 (R)] sp (R S)  [sp (R)] S R S  S R px [sp(R)] px {sp [pxz(R)]}

28. Bottom line: • Some heuristics : • Early selection is usually good • No transformation is always good • Rule application defines a search space • Need cost criteria to make decision

29. In textbook: more transformations • Chapter 16.2, 16.3.3 • More rewrite rules • Other operations, such as, duplicate elimination, etc.