Lecture11 - ECS 165B: Database System Implementa6on Lecture...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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

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.

Ask a homework question - tutors are online