This preview shows page 1. Sign up to view the full content.
Unformatted text preview: ECS 165B: Database System Implementa6on Lecture 12 UC Davis April 23, 2010 Acknowledgements: por6ons based on slides by Raghu Ramakrishnan and Johannes Gehrke. Class Agenda Last 6me: Query evalua6on techniques; external sor6ng Today: Finish with external sor6ng Physical query operators Reading Chapters 13 and 14 of Ramakrishnan and Gehrke (or Chapter 13 of Silberschatz et al) Announcements Grades for Part 1: Monday Quiz #1 in class next Wednesday (now reflected on web page); review session in class Monday Quiz #2 (along with "Awards Ceremony") will be during final exam slot External Sor6ng, con6nued Using B+ Trees for Sor6ng Scenario: table to be sorted has B+ tree index on sor6ng column(s) Idea: can retrieve records in order by traversing leaf pages Is this a good idea? Cases to consider: B+ tree is clustered Good idea! Could be a very bad idea! B+ tree is not clustered Clustered BTree Usedfor Sor6ng + Tree Used for Sorting Clustered B+ Cost: root to the le^most Cost: root to the leftleaf, then retrieve all leaf most leaf, then retrieve pages (index is clustered) all leaf pages (Alternative 1) Alternative 2 is used? If Each page fetched just once Additional cost of retrieving data records: each page fetched just Always be`er than once. external sor6ng! Index (Directs search) Data Entries ("Sequence set") Data Records * Always better than external sorting!
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 15 Unclustered B+ Tree Used for Sor6ng Leaves of tree have record ids, rather than records themselves In worst case, one I/O per data record! Index (Directs search) ustered B+ Tree Used for Sorting root to the leftleaf, then retrieve f pages native 1) ernative 2 is used? ional cost of ving data records: page fetched just Data Entries ("Sequence set") Data Records * Always better than external sorting! External Sor6ng vs Unclustered Index # of data pages External Sorting vs. Unclustered Index
Unclustered index N 100 1,000 10,000 100,000 1,000,000 Sorting 200 2,000 40,000 600,000 8,000,000 p=1 100 1,000 10,000 100,000 1,000,000 p=10 1,000 10,000 100,000 1,000,000 10,000,000 p=100 10,000 100,000 1,000,000 10,000,000 100,000,000 10,000,000 80,000,000 10,000,000 100,000,000 1,000,000,000
* p: p = # of records per page # of records per page * B=1,000 and block size=32 for sorting B = 1000 and block size p=100 isexternal sor6ng value. * = 32 for the more realistic Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 17 p = 100 is the more realis6c value Summary of External Sor6ng External sor6ng is important; DBMS may dedicate part of buffer pool for sor6ng! External merge sort minimizes disk I/O cost Pass 0: produces sorted runs of size B (# of buffer pages) # of runs merged at a 6me depends on B and block size Larger block size means less I/O cost per page Larger block size means smaller # runs merged In prac6ce, # of runs rarely more than 2 or 3 Summary: External o-Way External Merge SortMerge Sort 3,4 3,4 6,2 2,6 9,4 4,9 8,7 7,8 5,6 5,6 3,1 1,3 1,3 5,6 2 2 Input file PASS 0 1-page runs PASS 1 2 2-page runs PASS 2 2,3 4,4 6,7 1,2 3,5 6 4-page runs 2,3 4,6 4,7 8,9 we read + write e in file. n the file => the f passes 2 N +1 st is: log 2 N +1 ) 8,9 PASS 3 1,2 2,3 3,4 4,5 6,6 7,8 9 ide and conquer: les and merge 8-page runs ent Systems 3ed, R. Ramakrishnan and J. Gehrke 4 2-way merge sort can be generalized to n-way merge sort, using as many interal buffer pages as we have available Physical Rela6onal Operators, Part 1: Joins Rela6onal Opera6ons We will consider how to implement: Selec<on () Projec<on () Join (&) Union (U) Difference (-) Aggrega<on Selects a subset of rows from rela6on Deletes/reorders columns from rela6on Allows us to combine two rela6ons Tuples in one rela6on, but not the other Tuples in either rela6on SUM, MIN, etc. and GROUP BY Since each opera6on returns a rela6on, opera6ons can be composed. A^er we cover the opera6ons in isola6on, we will discuss how to op6mize queries formed by composing them Schema for Running Examples Sailors(sid: integer, sname: string, ra6ng: integer, age: float) Reserves(sid: integer, bid: integer, day: date, rname: string) Reserves: each tuple is 40 bytes long, 100 tuples per page, 1000 pages Sailors: each tuple is 50 bytes long, 80 tuples per page, 500 pages Equality Joins With One Join Column select *! from Reserves R, Sailors S! where R.sid = S.sid! R & S (& is bow6e) Common! Must be carefully op6mized. R S is large, so R S followed by selec6on is inefficient Assume: M tuples in R, pR tuples per page, N tuples in S, pS tuples per page In our examples, R is Reserves and S is Sailors Will consider more complex join condi6ons later Cost metric: # of I/Os Simple Nested Loops Join for each tuple r in R do for each tuple s in S do if r and s agree on join a`ribute then add <r,s> to result For each tuple in the outer rela6on R, we can the en6re inner rela6on S Cost: M + pR*M*N = 1000 + 100*1000*500 I/Os Page-oriented nested loops join: for each page of R, get each page of S, and write out matching pairs of tuples <r,s> where r is in R-page and s is in S-page Cost: M + M*N = 1000 + 1000*500 Index Nested Loops Join for each tuple r in R do for each tuple s in S do if r and s agree on join a`ribute then add <r,s> to result If there is an index on the join a`ribute of one rela6on (say S), can make it the inner and exploit the index Cost: M + ((M * pR) * cost of finding matching S tuples) For each R tuple, cost of probing S index is about 1.2 for hash index, 2-4 for B+ tree. Cost of then finding S tuples depends on clustering Clustered index: usually 1 I/O per group of tuples with a given key; unclustered: up to 1 I/O per tuple in group of tuples with a given key Block Nested Loops Join Join Block Nested Loops Use Use one page as an input bbuffer sfor scanning thene one page as an input uffer for canning the inner S, o inner S, one tpage as buffer, and all remaining pages tuse all page as he output the output buffer, and o hold block pages remaining of outer Rto hold ``block'' of outer R. For each matching tuple i in R-block, s in S-page, add For each matching tuple rrn R-block, s in S-page, add <r,s> to result. Then read n Then read next repeat. <r, s> to result. ext R-block, scan S, and R-block, scan S, etc. R&S Hash table for block of R (k < B-1 pages) Join Result ... ...
Input buffer for S Output buffer ...
8 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke Sort-Merge Join Sort R and S on the join a`ribute, then scan them to do a merge (on join a`ribute), and output result tuples Advance scan of R un6l current R-tuple current S-tuple, then advanced scan of S un6l current S-tuple current R-tuple; do this un6l current R-tuple = current S-tuple At this point, R-tuple matches current S-tuple (and all following S- tuples with same value); output <r,s> for all pairs of such tuples Then resume scanning R and S R is scanned once; each S "group" is scanned once per matching R tuple. Example of Sort-Merge Join
sid 22 28 31 44 58 sname rating age dustin 7 45.0 yuppy 9 35.0 lubber 8 55.5 guppy 5 35.0 rusty 10 35.0 Example of Sort-Merge Join sid 28 28 31 31 31 58 bid 103 103 101 102 101 103 day 12/4/96 11/3/96 10/10/96 10/12/96 10/11/96 11/12/96 rname guppy yuppy dustin lubber lubber dustin Cost: M log M + N log N + ~(M + N) Cost: M log M + N log N + (M+N) With 35, 100 or 300 buffer pages, both Reserves and Sailors can be sorted in 2 passes; total join cost: 7500. In worst case M + N could actually be M*N, but unlikely The cost of scanning, M+N, could be M*N (very unlikely!) Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke (BNL cost: 2500 to 15000 I/Os) 11 Refinement of Sort-Merge Join Refinement of Sort-Merge Join We can combine the merging phases in the sorting of R and S with the merging required for the join.
With B > L , where L is the size of the larger relation, using the sorting refinement that produces runs of length 2B in Pass 0, # runs of each relation is < B/2. Allocate 1 page per run of each relation, and `merge' while checking the join condition. Cost: read+write each relation in Pass 0 + read each relation in (only) merging pass (+ writing of result tuples). In example, cost goes down from 7500 to 4500 I/Os. In practice, cost of sort-merge join, like the cost of external sorting, is linear.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 12 Hash Join Par66on both Hash-Join rela6ons using hash Partition both func6on h: R tuples relations using hash fn h: R tuples in par66on i will in partition tuples only match iSwill only match S tuples in in par66on i Read in a par66on of R, hash it using Read in a partition h' ( h!). Scan using of R, hash it matching h!). Scan h2 (<> par66on of matching for S, search partition of S, search for matches. matches. partition i.
OUTPUT 1 INPUT 2 hash function Partitions 1 2 ...
Disk Partitions of R & S
hash fn h B-1 B-1 B main memory buffers Disk Join Result
Hash table for partition Ri (k < B-1 pages) h2
Input buffer for Si Output buffer Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke Disk B main memory buffers Disk
View Full Document
This note was uploaded on 04/29/2010 for the course ECS 152 taught by Professor Mr. during the Spring '10 term at University of Great Falls.
- Spring '10