21-qp-notes

# 21-qp-notes - Query Processing CPS 116 Introduction to...

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

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 assigned today ² Due in two weeks, but please start early! 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

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

View Full Document
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: ± Memory requirement: 3 (+1 for double buffering) ± Improvement: ² ² I/O’s: ² Memory requirement: same as before
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 ² # of level- i runs = d # of level-( i –1) runs / (

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.

{[ snackBarMessage ]}

### Page1 / 11

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

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

View Full Document
Ask a homework question - tutors are online