21-qp - Query Processing CPS 116 Introduction to Database...

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

View Full Document Right Arrow Icon
1 Query Processing CPS 116 Introduction to Database Systems 2 Announcements (Tue. Nov. 22) ± Project milestone #2 feedback emailed ± Extra credit (20 points, assigned by email Nov. 7) due in two weeks ± Homework #4 due next Thursday ² Please start now 3 Overview ± Many different ways of processing the same query ² Scan? Sort? Hash? Use an index? ² All have different performance characteristics and/or make different assumptions about data ± Best choice depends on the situation ² Implement all alternatives ² Let the query optimizer choose at run-time
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 4 Notation ± Relations: R , S ± Tuples: r , s ± Number of tuples: | R |, | S | ± Number of disk blocks: B ( R ), B ( S ) ± Number of memory blocks available: M ± Cost metric ² Number of I/O’s ² Memory requirement 5 Table scan ± Scan table R and process the query ² Selection over R ² Projection of R without duplicate elimination ± I/O’s: B ( R ) ² Trick for selection: stop early if it is a lookup by key ± Memory requirement: 2 (+1 for double buffering) ± Not counting the cost of writing the result out ² Same for any algorithm! ² Maybe not needed—results may be pipelined into another operator 6 Nested-loop join ± R ! p S ± For each block of R , and for each r in the block: For each block of S , and for each s in the block: Output rs if p evaluates to true over r and s ² R is called the outer table; S is called the inner table ± I/O’s: B ( R ) + | R | B ( S ) ± Memory requirement: 3 (+1 for double buffering) ± Improvement: block-based nested-loop join ² For each block of R , and for each block of S : For each r in the R block, and for each s in the S block: … ² I/O’s: B ( R ) + B ( R ) B ( S ) ² Memory requirement: same as before
Background image of page 2
3 7 More improvements of nested-loop join ± Stop early if the key of the inner table is being matched ± Make use of available memory ² Stuff memory with as much of R as possible, stream S by, and join every S tuple with all R tuples in memory ² I/O’s: B ( R ) + d B ( R ) / ( M –2±)± e B ( S ) • Or, roughly: B ( R ) B ( S ) /M ² Memory requirement: M (as much as possible) ± Which table would you pick as the outer? 8 External merge sort Remember (internal-memory) merge sort? Problem: sort R , but R does not fit in memory ± Pass 0: read M blocks of R at a time, sort them, and write out a level-0 run ² There are d B ( R ) e level-0 sorted runs ± Pass i : merge ( M – 1) level-( i -1) runs at a time, and write out a level- i run ² ( M – 1) memory blocks for input, 1 to buffer output
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 / 11

21-qp - Query Processing CPS 116 Introduction to Database...

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