√2011-10-02-OperatorImplementation2 [Compatibility Mode]

√2011-10-02-OperatorImplementation2 [Compatibility Mode]

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

View Full Document Right Arrow Icon
Evaluating Relational Operators: Part II Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1 Relational Operators ± Select ± Project ± Join Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2 ± Set operations (union, intersect, except) ± Aggregation Example SELECT * FROM Reserves R Sailor S Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3 ± No indices on Sailor or Reserves Reserves R, Sailor S, WHERE R.sid = S.sid
Background image of page 1

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

View Full DocumentRight Arrow Icon
Tuple Nested Loop Join foreach tuple r in R do Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4 foreach tuple s in S do if r.sid == s.sid then add <r, s> to result ± R is “outer” relation ± S is “inner” relation Analysis ± Assume ² M pages in R, p R tuples per page •M = 1000 , p R = 100 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5 ² N pages in S, p S tuples per page Select •N = 500 , p S = 80 ± Total cost = M + p R * M * N ± Main problem: depends on # tuples per page ² Ignore cost of writing out result ² Same for all join methods Page Nested Loop Join foreach page p1 in R do foreach page p2 in S do foreach r in p1 do Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6 foreach s in p2 do if r.sid == s.sid then add <r, s> to result ± R is “outer” relation ± S is “inner” relation
Background image of page 2
Analysis ± Assume ² M pages in R, p R tuples per page •M = 1000 , p R = 100 Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7 ² N pages in S, p S tuples per page Select •N = 500 , p S = 80 ± Total cost = M + M * N ± Main problem: does not use all buffer pages ± Note: Smaller relation should be “outer” ² Better for S to be “outer” in this case! Block Nested Loops Join ± Use one page as an input buffer for scanning the inner S, one page as the output buffer, and use all remaining pages to hold ``block ’’ of outer R. ² For each matching tuple r in R-block, s in S-page, add Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8 <r, s> to result. Then read next R-block, scan S, etc . . . . . . . R & S Hash table for block of R (k < B-1 pages) Input buffer for S Output buffer . . .
Background image of page 3

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

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

This note was uploaded on 12/07/2011 for the course CS 4410 taught by Professor Vollset during the Spring '07 term at Cornell University (Engineering School).

Page1 / 9

&acirc;ˆš2011-10-02-OperatorImplementation2 [Compatibility Mode]

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

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