For the query TA,B,C,D(R A-C S), assume the following: R is 10 pages; each R tuple...

Transcribed Image Text:

For the query TA,B,C,D(R A-C S), assume the following: R is 10 pages; each R tuple is 300 bytes. S is 100 pages; each S tuple is 500 bytes. The combined size of attributes A, B, C and D is 450 bytes. A and B are in R and have combined size of 200 bytes; C and D are in S. A is a key for R. Each S tuple joins with exactly one R tuple. The page size is 1024 bytes. The buffer size M is 3 pages. 1/2 a) Show what are the minimum page I/O costs if the join uses the (optimized) block nested-loop join method. i. ii. 11. iii. iv. Page I/O cost for join eliminating all unwanted attributes during the join. Page I/O cost for projection using external sorting and removing duplicate tuples on-the-fly during the merge passes. iii. Total query processing page I/O cost. b) Show what are the minimum page I/O costs if the join uses the merge join method. i. Page I/O cost to sort R. Page I/O cost to sort S. Page I/O cost to join R and S. Total query processing page I/O cost. For the query TA,B,C,D(R A-C S), assume the following: R is 10 pages; each R tuple is 300 bytes. S is 100 pages; each S tuple is 500 bytes. The combined size of attributes A, B, C and D is 450 bytes. A and B are in R and have combined size of 200 bytes; C and D are in S. A is a key for R. Each S tuple joins with exactly one R tuple. The page size is 1024 bytes. The buffer size M is 3 pages. 1/2 a) Show what are the minimum page I/O costs if the join uses the (optimized) block nested-loop join method. i. ii. 11. iii. iv. Page I/O cost for join eliminating all unwanted attributes during the join. Page I/O cost for projection using external sorting and removing duplicate tuples on-the-fly during the merge passes. iii. Total query processing page I/O cost. b) Show what are the minimum page I/O costs if the join uses the merge join method. i. Page I/O cost to sort R. Page I/O cost to sort S. Page I/O cost to join R and S. Total query processing page I/O cost.