Unformatted text preview: ECS 165B: Database System Implementa6on Lecture 11 UC Davis April 21, 2010 Acknowledgements: por6ons based on slides by Raghu Ramakrishnan and Johannes Gehrke. Class Agenda Last 6me: Overview of DavisDB project, Part 2: indexing Today: Query evalua6on techniques: sor6ng Reading Chapters 12 and 13 of Ramakrishnan and Gehrke (or Chapter 13 of Silberschatz et al) Announcements Code review sign-up sheet posted (see email I sent out for link); code reviews happening today through Monday Repository updates: TestIX.cpp (sample tests for indexing); page file manager bugfixes; (not quite) final version of TestRM.cpp* Grades for Part 1: Friday? Discussion sec8on Friday @11am: B+ tree jam session Quiz #1 in class next Wednesday Overview of Query Evalua6on Techniques Background material for Part 4 of the DavisDB project; some concepts we saw in Lecture 7 include: evalua8on plan rela6onal algebra query drawn as a tree; annotated evalua8on plan each rela6onal operator (e.g., "join") is annotated with the physical operator that will be used to perform the opera6on (e.g., "index nested loops join") query op8mizer takes a SQL query, produces an efficient annotated evalua6on plan query execu8on engine executes the annotated evalua6on plan Logical versus Physical Operators Logical operator join: E1 || E2 projec6on: (E) predicate: R selec6on: (E) Physical operator nested loops join, index nested loops join, sort-merge join, ... projec6on file scan, index scan, ... selec6on selec6on w/base predicate: (R) file scan with condi8on, index scan with condi8on, ... File scan (with condi6on): RecordFileScan (DavisDB Part 1) Index scan (with condi6on): IndexScan (Part 2) Also underlies index nested loops join Others will be implemented in QueryEngine (Part 4) Recall: Sort-Merge Join Join: Sort-Merge (R i=j S) of R and S Sort R and S on the join column, then scan them to do a ``merge'' (on join col.), and output result tuples.
Advance scan of R until current R-tuple >= current S tuple, then advance scan of S until current S-tuple >= current R tuple; do this until current R tuple = current S tuple. At this point, all R tuples with same value in Ri (current R group) and all S tuples with same value in Sj (current S group) match; output <r, s> for all pairs of such tuples. Then resume scanning R and S. Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke R is scanned once; each S group is scanned once per matching R tuple. (Multiple scans of an S group are likely to find needed pages in buffer.) 12 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 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)
The cost of scanning, M+N, could be M*N (very unlikely!) With 35, 100 or 300 buffer pages, both Reserves and Sailors can be sorted in 2 passes; total join cost: 7500.
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13 Something to Consider in Part 2 (Indexing) In Part 4, nested loops join and index nested loops join will be the only join algorithms you will be required to implement Sort-merge join will be op6onal (XC), *but*, here's something to do in Part 2 that will make it easier Scan of B+ tree: required to return all record ids matching condi6on; not required to return them in order! May be a liple extra work to have your scan return them in order, depending on details of your implementa6on... *But* this will let you use the index to do the sort for sort- merge join, if R and S are both indexed on the join apribute We'll look at this again in a few slides Plan for Upcoming Lectures Rest of today: we'll talk about external sor8ng, needed for sort-merge join, duplicate elimina6on, ... Next lecture: we'll focus on the other physical query operators Subsequent lectures: genera6ng physical plans (annotated evalua6on plans) from logical plans (evalua6on plans, aka rela6onal algebra) ...
View Full Document
- Spring '10
- Relational model, Tuple, R. Ramakrishnan