Notes07 - CS245:DatabaseSystem Principles...

Info iconThis preview shows pages 1–16. 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
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 2 --> Generating and comparing plans Query Generate Plans Pruning x        x Estimate Cost Cost Select Query Optimization Pick Min
Background image of page 2
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
Background image of page 3

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

View Full DocumentRight Arrow Icon
CS 245 Notes 7 4 Implementation details: e.g. - Join algorithm   - Memory management   - Parallel processing
Background image of page 4
CS 245 Notes 7 5 Estimating IOs: Count # of disk blocks that must be  read (or written) to execute query plan
Background image of page 5

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

View Full DocumentRight Arrow Icon
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 6
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
Background image of page 7

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

View Full DocumentRight Arrow Icon
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
Background image of page 8
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)
Background image of page 9

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

View Full DocumentRight Arrow Icon
CS 245 Notes 7 10 Caution!      This may not be the best way to compare ignoring CPU costs ignoring timing ignoring double buffering requirements
Background image of page 10
CS 245 Notes 7 11 Options Transformations: R1      R2,  R2       R1 Join algorithms: Iteration (nested loops) Merge join Join with index Hash join
Background image of page 11

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

View Full DocumentRight Arrow Icon
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 12
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
Background image of page 13

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

View Full DocumentRight Arrow Icon
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  ]
Background image of page 14
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
Background image of page 15

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

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

Page1 / 60

Notes07 - CS245:DatabaseSystem Principles...

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

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