1 / 35

CPS216: Data-Intensive Computing Systems Query Processing (contd.)

CPS216: Data-Intensive Computing Systems Query Processing (contd.). Shivnath Babu. Overview of Query Processing. SQL query. parse. parse tree. Query rewriting. Query Optimization. statistics. logical query plan. Physical plan generation. physical query plan. execute. Query

maren
Download Presentation

CPS216: Data-Intensive Computing Systems Query Processing (contd.)

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. CPS216: Data-Intensive Computing SystemsQuery Processing (contd.) Shivnath Babu

  2. Overview of Query Processing SQL query parse parse tree Query rewriting Query Optimization statistics logical query plan Physical plan generation physical query plan execute Query Execution result

  3. SQL query Initial logical plan parse Rewrite rules parse tree Query rewriting Logical plan statistics logical query plan Physical plan generation “Best” logical plan physical query plan execute result

  4. Query Rewriting B,D B,D R.C = S.C R.A = “c” Λ R.C = S.C R.A = “c” X X R S R S We will revisit it towards the end of this lecture

  5. SQL query parse parse tree Query rewriting statistics Best logical query plan Physical plan generation Best physical query plan execute result

  6. Physical Plan Generation B,D Project Natural join Hash join R.A = “c” S Index scan Table scan R R S Best logical plan

  7. SQL query parse parse tree Enumerate possible physical plans Query rewriting statistics Best logical query plan Find the cost of each plan Physical plan generation Best physical query plan Pick plan with minimum cost execute result

  8. Physical Plan Generation Logical Query Plan P1 P2 …. Pn C1 C2 …. Cn Pick minimum cost one Physical plans Costs

  9. Plans for Query Execution • Roadmap • Path of a SQL query • Operator trees • Physical Vs Logical plans • Plumbing: Materialization Vs pipelining

  10. Logical Plans Vs. Physical Plans B,D Project Natural join Hash join R.A = “c” S Index scan Table scan R R S Best logical plan

  11. B,D R.A = “c” S R Operator Plumbing • Materialization: output of one operator written to disk, next operator reads from the disk • Pipelining: output of one operator directly fed to next operator

  12. Materialization B,D Materialized here R.A = “c” S R

  13. Iterators: Pipelining B,D •  Each operator supports: • Open() • GetNext() • Close() R.A = “c” S R

  14. Iterator for Table Scan (R) Open() { /** initialize variables */ b = first block of R; t = first tuple in block b; } GetNext() { IF (t is past last tuple in block b) { set b to next block; IF (there is no next block) /** no more tuples */ RETURN EOT; ELSE t = first tuple in b; } /** return current tuple */ oldt = t; set t to next tuple in block b; RETURN oldt; } Close() { /** nothing to be done */ }

  15. Iterator for Select R.A = “c” GetNext() { LOOP: t = Child.GetNext(); IF (t == EOT) { /** no more tuples */ RETURN EOT; } ELSE IF (t.A == “c”) RETURN t; ENDLOOP: } Open() { /** initialize child */ Child.Open(); } Close() { /** inform child */ Child.Close(); }

  16. Iterator for Sort R.A GetNext() { IF (more tuples) RETURN next tuple in order; ELSE RETURN EOT; } Open() { /** Bulk of the work is here */ Child.Open(); Read all tuples from Child and sort them } Close() { /** inform child */ Child.Close(); }

  17. Iterator for Tuple Nested Loop Join • TNLJ (conceptually) for each r  Lexp do for each s  Rexp do if Lexp.C = Rexp.C, output r,s Lexp Rexp

  18. Example 1: Left-Deep Plan TNLJ TableScan TNLJ R3(C,D) TableScan TableScan R1(A,B) R2(B,C) Question: What is the sequence of getNext() calls?

  19. TableScan TNLJ R3(C,D) TableScan TableScan R1(A,B) R2(B,C) Example 2: Right-Deep Plan TNLJ Question: What is the sequence of getNext() calls?

  20. Cost Measure for a Physical Plan • There are many cost measures • Time to completion • Number of I/Os (we will see a lot of this) • Number of getNext() calls • Tradeoff: Simplicity of estimation Vs. Accurate estimation of performance as seen by user

  21. Why do we need Query Rewriting? • Pruning the HUGE space of physical plans • Eliminating redundant conditions/operators • Rules that will improve performance with very high probability • Preprocessing • Getting queries into a form that we know how to handle best • Reduces optimization time drastically without noticeably affecting quality

  22. Some Query Rewrite Rules • Transform one logical plan into another • Do not use statistics • Equivalences in relational algebra • Push-down predicates • Do projects early • Avoid cross-products if possible

  23. Equivalences in Relational Algebra R S = S R Commutativity (R S) T = R (S T) Associativity Also holds for: Cross Products, Union, Intersection 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

  24. Apply Rewrite Rule (1) B,D B,D R.C = S.C R.A = “c” Λ R.C = S.C R.A = “c” X X R S R S B,D [sR.C=S.C [R.A=“c”(R X S)]]

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

  26. [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) =

  27. Apply Rewrite Rule (2) B,D B,D R.C = S.C R.C = S.C R.A = “c” X R.A = “c” S X R S R B,D [sR.C=S.C [R.A=“c”(R)] X S]

  28. Apply Rewrite Rule (3) B,D B,D R.C = S.C Natural join R.A = “c” X S R.A = “c” S R R B,D [[R.A=“c”(R)] S]

  29. Rules:s + combined (continued) 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)]

  30. 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)]}

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

  32. But: What if we have A, B indexes? B = “cat” A=3 Intersect pointers to get pointers to matching tuples

  33. Bottom line: • No transformation is always good • Some are usually good: • Push selections down • Avoid cross-products if possible • Subqueries  Joins

  34. Avoid Cross Products (if possible) • Which join trees avoid cross-products? • If you can't avoid cross products, perform them as late as possible Select B,D From R,S,T,U Where R.A = S.B  R.C=T.C  R.D = U.D

  35. More Query Rewrite Rules • Transform one logical plan into another • Do not use statistics • Equivalences in relational algebra • Push-down predicates • Do projects early • Avoid cross-products if possible • Use left-deep trees • Subqueries  Joins • Use of constraints, e.g., uniqueness

More Related