Question: Tables R(a, b) S(a, c, d) will be Joined over the attributes a. Catalog information is as follows: NPages (R) = M =2000 pages, 80

Tables R(a, b) S(a, c, d) will be Joined over the attributes a. Catalog information is as follows: NPages (R) = M =2000 pages, 80 records per page NPages (S)= N = 300 pages, 40 records per page No idx on R.a and S.a. DBMS does not use prefecthing based double buffer and blocked I/O methods and 1 buffer page is used as input and output buffer. Rep.Sel. also not used. Suppose the query results are written to the screen. Since buffer pool size = B = 50 pages; a) How many Disk I/O results in this Join operation with BNL (block nested loop) with R in outer loop and S in inner loop? b) How many Disk I/O results in this Join operation with BNL (block nested loop) with S in outer loop and R in inner loop? c) What will be the lowest / largest Disk I/O cost of this Join operation with sort-merge-join, with S in the outer loop and R in the inner loop? (We turn the R and S tables into sequential tables. Then we merge the sequential R and S tables.) d) What is the Disk I/O cost of this Join operation with Hash Join? Assume that the pieces are approximately the same size. (External table, interior table selection is important!) e) What will be the Disk I/O cost of this Join operation with Hybrid Join? Assume that the pieces are approximately the same size. (External table, interior table selection is important!)

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!