Notes07 (1)

# Notes07 (1) - CS 245 Notes 7 1 CS 245: Database System...

This preview shows pages 1–17. Sign up to view the full content.

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

View Full Document

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

View Full Document

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

View Full Document

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

View Full Document

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

View Full Document

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

View Full Document

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

View Full Document

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

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: CS 245 Notes 7 1 CS 245: Database System Principles Notes 7: Query Optimization Hector Garcia-Molina 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 CS 245 Notes 7 7 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 CS 245 Notes 7 8 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 9 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 10 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 CS 245 Notes 7 11 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 12 Caution! This may not be the best way to compare • ignoring CPU costs • ignoring timing • ignoring double buffering requirements CS 245 Notes 7 13 Options • Transformations: R1 R2, R2 R1 • Joint algorithms: – Iteration (nested loops) – Merge join – Join with index – Hash join CS 245 Notes 7 14 • Iteration join (conceptually) for each r ∈ R1 do for each s ∈ R2 do if r.C = s.C then output r,s pair CS 245 Notes 7 15 • 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 16 Procedure Output-Tuples...
View Full Document

## This note was uploaded on 02/21/2011 for the course CS 245 taught by Professor Dr.hector during the Winter '02 term at SUNY Buffalo.

### Page1 / 77

Notes07 (1) - CS 245 Notes 7 1 CS 245: Database System...

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

View Full Document
Ask a homework question - tutors are online