1 / 28

CS4432: Database Systems II

CS4432: Database Systems II. Lecture #13 Query Processing. Professor Elke A. Rundensteiner. Query in SQL  Query Plan in Algebra (logical)  Other Query Plan in Algebra (logical). Query plan 1 (in relational algebra).  B,D

ralph
Download Presentation

CS4432: Database Systems II

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. CS4432: Database Systems II Lecture #13 Query Processing Professor Elke A. Rundensteiner query processing - lecture 13

  2. Query in SQL  Query Plan in Algebra (logical)  Other Query Plan in Algebra (logical) query processing - lecture 13

  3. Query plan 1 (in relational algebra) B,D sR.A=“c” S.E=2  R.C=S.C X R S query processing - lecture 13

  4. Query plan 2 (in relational algebra) B,D sR.A = “c”sS.E = 2 R S natural join query processing - lecture 13

  5. Relational algebra optimization • What are transformation rules ? • preserve equivalence • What are good transformations? • reduce query execution costs query processing - lecture 13

  6. Rules:Natural joins & cross products & union R S = S R (R S) T = R (S T) query processing - lecture 13

  7. Note: • Carry attribute names in results, so order is not important • Can also write as trees, e.g.: T R R S S T query processing - lecture 13

  8. Rules:Natural joins & cross products & union R S = S R (R S) T = R (S T) R x S = S x R (R x S) x T = R x (S x T) R U S = S U R R U (S U T) = (R U S) U T query processing - lecture 13

  9. Rules: Selects sp1p2(R) = sp1vp2(R) = sp1 [ sp2 (R)] [ sp1 (R)] U [ sp2 (R)] query processing - lecture 13

  10. Bags vs. Sets R = {a,a,b,b,b,c} S = {b,b,c,c,d} RUS = ? • Option 1 SUM RUS = {a,a,b,b,b,b,b,c,c,c,d} • Option 2 MAX RUS = {a,a,b,b,b,c,c,d} query processing - lecture 13

  11. Option 2 (MAX) 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} query processing - lecture 13

  12. “Sum” option makes more sense: Senators (……) Rep (……) 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? query processing - lecture 13

  13. Executive Decision -> Use “SUM” option for bag unions -> Some rules cannot be used for bags query processing - lecture 13

  14. Rules: Project Let: X = set of attributes Y = set of attributes XY = X U Y pxy (R) = px [py (R)] query processing - lecture 13

  15. [sp (R)] S R [sq (S)] Rules:s + combined Let p = predicate with only R attribs q = predicate with only S attribs m = predicate with only R,S attribs sp (R S) = sq (R S) = query processing - lecture 13

  16. Rules:s + combined (continued) Some Rules can be Derived: spq (R S) = spqm (R S) = spvq (R S) = query processing - lecture 13

  17. Do one, others for homework: 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)] query processing - lecture 13

  18. --> Derivation for first one: spq (R S) = sp [sq (R S) ] = sp[ R sq (S) ] = [sp (R)] [sq (S)] query processing - lecture 13

  19. 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) ]} query processing - lecture 13

  20. 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)= query processing - lecture 13

  21. pxy {sp[pxz’ (R) pyz’ (S)]} z’ = z U {attributes used in P } pxy {sp(R S)} = query processing - lecture 13

  22. Rules for s,p combined with X similar... e.g., sp (R X S) = ? query processing - lecture 13

  23. Rules s,U combined: sp(R U S)= sp(R) U sp(S) sp(R - S)= sp(R) - S = sp(R) - sp(S) query processing - lecture 13

  24. 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)]} query processing - lecture 13

  25. Conventional wisdom: do projects early Example: R(A,B,C,D,E) x={E} P: (A=3)  (B=“cat”) px {sp(R)} vs. pE {sp{pABE(R)}} query processing - lecture 13

  26. What if we have A, B indexes? But B = “cat” A=3 Intersect pointers to get pointers to matching tuples query processing - lecture 13

  27. Bottom line: • No transformation is always good • Usually good: early selections query processing - lecture 13

  28. In textbook: more transformations • Eliminate common sub-expressions • Other operations, such as, duplicate elimination and others. query processing - lecture 13

More Related