1 / 21

CPS216: Advanced Database Systems Notes 07:Query Execution

CPS216: Advanced Database Systems Notes 07:Query Execution. Shivnath Babu. SQL query. parse. parse tree. Query rewriting. statistics. logical query plan. Physical plan generation. physical query plan. execute. result. Query Processing - In class order. 2; 16.1. 3; 16.2,16.3.

jimbo
Download Presentation

CPS216: Advanced Database Systems Notes 07:Query Execution

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: Advanced Database SystemsNotes 07:Query Execution Shivnath Babu

  2. SQL query parse parse tree Query rewriting statistics logical query plan Physical plan generation physical query plan execute result Query Processing - In class order 2; 16.1 3; 16.2,16.3 4; 16.4—16.7 1; 13, 15

  3. Roadmap • Path of a SQL query • Plans • Operator trees • Physical Vs Logical plans • Plumbing: Materialization Vs pipelining

  4. Parser Logical query plan Query Optimizer Physical query plan Query Executor Access method API calls Storage Manager Disk(s) Storage system API calls Modern DBMS Architecture Applications SQL DBMS File system API calls OS

  5. Locial 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

  6. 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

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

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

  9. 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 */ }

  10. 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(); }

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

  12. 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(); }

  13. 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?

  14. Example 1 (contd.) • Assume Statistics: • B(R1) = 1000 blocks, T(R1) = 10,000 tuples • B(R2) = 500 blocks, T(R2) = 5000 tuples • B(R3) = 1000 blocks, T(R3) = 10,000 tuples • Let X = R1 Join (R1.B = R2.B) R2 • T(X) = 1,000,000 tuples, B(X) = 200,000 blocks • Let Output = 1000 tuples • Questions: • Number of getNext() calls? • Number of disk I/Os? • Assume we have 1000 blocks of memory, how can we improve the plan?

  15. 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?

  16. Example 2 (contd.) • Assume Statistics: • B(R1) = 1000 blocks, T(R1) = 10,000 tuples • B(R2) = 500 blocks, T(R2) = 5000 tuples • B(R3) = 1000 blocks, T(R3) = 10,000 tuples • Let X = R1 Join (R1.B = R2.B) R2 • T(X) = 1,000,000 tuples, B(X) = 200,000 blocks • Let Output = 1000 tuples • Questions: • Number of getNext() calls? • Number of disk I/Os? • Assume we have 1000 blocks of memory, how can we improve the plan?

  17. Questions to think about • What "shape" of plan works best for nested loop joins: 'Left deep' (Example 1) or 'right deep' (Example 2)? • Will sorting help for nested loop join? (Hint: think about clustered vs. unclustered indexes) • Can materialization help for nested loop join? • Generalize Example 1 (and 2) to 'n' relations: What is the optimal use of M blocks of memory? (I don't know the answer :-) )

  18. Example 3: Hash-Join Plan HJ TableScan HJ R3(C,D) TableScan TableScan R1(A,B) R2(B,C)

  19. Example 3 (contd.) • Naive materialization: • Compute hash join of R1, R2 (called X) • Write output X to disk • The (outer) hash join reads X (table scan) and reads R3 and performs hash join • What is the cost of naive materialization? • Suggest an improved processing strategy that shaves 2 B(X) from the above cost

  20. Example 3 (contd.) • Can this be completely pipelined if you have limited memory? • How much memory do you need to be able to pipeline this plan?

  21. Questions to think about • If you are designing/building the basic set of physical operators for your database system, would you implement the hash-join as a single operator or as two operators -- one that partitions and the second that joins? • If you are designing physical operators for your database system, would you implement sort-merge join as a single operator or as two operators -- one that sorts and one that merges? • Think about pipelining vs. materialization issues in plans involving sort-merge joins

More Related