This preview has intentionally blurred sections. Sign up to view the full version.View Full Document
Unformatted text preview: Evaluation of Relational Operators 139 (a) With 5 buffer pages, the cost of the Index Nested Loops joins remains the same, but the cost of the Block Nested Loops join will increase. The new cost of the Block Nested Loops join is T otalCost = N + M ∗ d N B − 2 e = 67 , 200 And now the cheapest solution is the Index Nested Loops join with S as the outer relation. (b) If S contains 10 tuples then we’ll need to change some of our initial assump- tions. Now all of the S tuples fit on a single page, and it will only require a single I/O to access the (single) leaf in the index. Also, each tuple in S will match 1,000 tuples in R. Block Nested Loops: T otalCost = N + M ∗ d N B − 2 e = 1 , 001 Index Nested Loops with R as the outer relation: T otalCost = 1 , 000 + 10 , 000 ∗ (1 + 1) = 21 , 000 Index Nested Loops with S as the outer relation: T otalCost = 1 + 10 ∗ (3 + 100) = 1 , 031 Block Nested Loops is still the best solution. 3. SORT-MERGE: With 15 buffer pages we can sort R in three passes and S in two passes. The cost of sorting R is 2 ∗ 3 ∗ M = 6 , 000, the cost of sorting S is 2 ∗ 2 ∗ N = 800, and the cost of the merging phase is M + N = 1 , 200. T otalCost = 6 , 000 + 800 + 1 , 200 = 8 , 000 HASH JOIN: With 15 buffer pages the first scan of S (the smaller relation) splits it into 14 buckets, each containing about 15 pages. To store one of these buckets (and its hash table) in memory will require f ∗ 15 pages, which is more than we have available. We must apply the Hash Join technique again to all partitions of R and S that were created by the first partitioning phase. Then we can fit an entire partition of S in memory. The total cost will be the cost of two partioningentire partition of S in memory....
View Full Document
- Fall '12
- Relational model, Query optimizer, Hash join, Join, buffer pages