cs411-08-queryprocessing - 1 CS411 Database Systems 8:...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

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

Unformatted text preview: 1 CS411 Database Systems 8: Query Processing 2 Why Do We Learn This? 2 3 Outline • Logical/physical operators • Cost parameters and sorting • One-pass algorithms • Nested-loop joins • Two-pass algorithms 4 Query Execution Query compiler Execution engine Index/record mgr. Buffer manager Storage manager storage User/ Application Query or update Query execution plan Record, index requests Page commands Read/write pages 5 Logical v.s. Physical Operators • Logical operators – what they do – e.g., union, selection, project, join, grouping • Physical operators – how they do it – e.g., nested loop join, sort-merge join, hash join, index join 6 Query Execution Plans Purchase Person Buyer=name City=‘urbana’ phone>’5430000’ buyer (Simple Nested Loops) SELECT S.sname FROM Purchase P, Person Q WHERE P.buyer=Q.name AND Q.city=‘urbana’ AND Q.phone > ‘5430000’ Query Plan: • logical tree • implementation choice at every node • scheduling of operations. (Table scan) (Index scan) Some operators are from relational algebra, and others (e.g., scan, group) are not. 7 How do We Combine Operations? • The iterator model. Each operation is implemented by 3 functions: – Open: sets up the data structures and performs initializations – GetNext: returns the the next tuple of the result. – Close: ends the operations. Cleans up the data structures. • Enables pipelining! 8 Cost Parameters • Cost parameters – M = number of blocks that fit in main memory – B(R) = number of blocks holding R – T(R) = number of tuples in R – V(R,a) = number of distinct values of the attribute a • Estimating the cost: – Important in optimization (next lecture) – Compute I/O cost only – We compute the cost to read the tables – We don’t compute the cost to write the result (because pipelining) 9 Sorting • Two pass multi-way merge sort • Step 1: – Read M blocks at a time, sort, write – Result: have runs of length M on disk • Step 2: – Merge M-1 at a time, write to disk – Result: have runs of length M(M-1) M 2 • Cost: 3B(R), Assumption: B(R) M 2 10 Scanning Tables • The table is clustered (I.e. blocks consists only of records from this table): – Table-scan: if we know where the blocks are...
View Full Document

This note was uploaded on 02/17/2012 for the course CS 411 taught by Professor Winslett during the Spring '07 term at University of Illinois at Urbana–Champaign.

Page1 / 41

cs411-08-queryprocessing - 1 CS411 Database Systems 8:...

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

View Full Document Right Arrow Icon
Ask a homework question - tutors are online