Chapter 14- Operator Implementation 2 (10-2)

Chapter 14- Operator Implementation 2 (10-2) - Evaluating...

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

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

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

View Full Document
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
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 . . .

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

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

{[ snackBarMessage ]}

Page1 / 9

Chapter 14- Operator Implementation 2 (10-2) - Evaluating...

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

View Full Document
Ask a homework question - tutors are online