Notes07 - Query Optimization CS 245: Database System...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
CS 245 Notes 7 1 CS 245: Database System Principles Notes 7: Query Optimization Steven Whang CS 245 Notes 7 2 --> Generating and comparing plans Query Generate Plans Pruning x x Estimate Cost Cost Select Query Optimization Pick Min CS 245 Notes 7 3 To generate plans consider: • Transforming relational algebra expression (e.g. order of joins) • Use of existing indexes • Building indexes or sorting on the fly CS 245 Notes 7 4 • Implementation details: e.g. - Join algorithm - Memory management - Parallel processing CS 245 Notes 7 5 Estimating IOs: • Count # of disk blocks that must be read (or written) to execute query plan CS 245 Notes 7 6 To estimate costs, we may have additional parameters: B(R) = # of blocks containing R tuples f(R) = max # of tuples of R per block M = # memory blocks available HT(i) = # levels in index i LB(i) = # of leaf blocks in index i
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
CS 245 Notes 7 7 Clustering index Index that allows tuples to be read in an order that corresponds to physical order A A index 10 15 17 19 35 37 CS 245 Notes 7 8 Notions of clustering • Clustered file organization ….. • Clustered relation ….. • Clustering index R1 R2 S1 S2 R3 R4 S3 S4 R1 R2 R3 R4 R5 R5 R7 R8 CS 245 Notes 7 9 Example R1 R2 over common attribute C T(R1) = 10,000 T(R2) = 5,000 S(R1) = S(R2) = 1/10 block Memory available = 101 blocks Metric: # of IOs (ignoring writing of result) CS 245 Notes 7 10 Caution! This may not be the best way to compare • ignoring CPU costs • ignoring timing • ignoring double buffering requirements CS 245 Notes 7 11 Options • Transformations: R1 R2, R2 R1 • Join algorithms: – Iteration (nested loops) – Merge join – Join with index – Hash join CS 245 Notes 7 12 • Iteration join (conceptually) for each r R1 do for each s R2 do if r.C = s.C then output r,s pair
Background image of page 2
CS 245 Notes 7 13 • Merge join (conceptually) (1) if R1 and R2 not sorted, sort them (2) i 1; j 1; While (i T(R1)) (j T(R2)) do if R1{ i }.C = R2{ j }.C then outputTuples else if R1{ i }.C > R2{ j }.C then j j+1 else if R1{ i }.C < R2{ j }.C then i i+1 CS 245 Notes 7 14 Procedure Output-Tuples While (R1{ i }.C = R2{ j }.C) (i T(R1)) do [jj j; while (R1{ i }.C = R2{ jj }.C) (jj T(R2)) do [output pair R1{ i }, R2{ jj }; jj jj+1 ] i i+1 ] CS 245 Notes 7 15 Example i R1{i}.C R2{j}.C j 1 10 5 1 2 20 20 2 3 20 20 3 4 30 30 4 5 40 30 5 50 6 52 7 CS 245 Notes 7 16 • Join with index (Conceptually) For each r R1 do [ X index (R2, C, r.C) for each s X do output r,s pair] Assume R2.C index
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 10

Notes07 - Query Optimization CS 245: Database System...

This preview shows document pages 1 - 4. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online