GATE | Gate IT 2005 | Question 85

Q82_Part B

A database table T1 has 2000 records and occupies 80 disk blocks. Another table T2 has 400 records and occupies 20 disk blocks. These two tables have to be joined as per a specified join condition that needs to be evaluated for every pair of records from these two tables. The memory buffer space available can hold exactly one block of records for T1 and one block of records for T2 simultaneously at any point in time. No index is available on either table.

If, instead of Nested-loop join, Block nested-loop join is used, again with the most appropriate choice of table in the outer loop, the reduction in number of block accesses required for reading the data will be

(A) 0
(B) 30400
(C) 38400
(D) 798400


Answer: (B)

Explanation:
Number of block access = n_r * b_s + b_r
where b_r and b_s are number of blocks in relations R and S respectively, and n_r is the number of tuples in relation R.

We select the relation with small number of tuples as outer relation R. So, R is T2.
Number of block access = 400 * 80 + 20 = 32020

The memory buffer space holds one block of records for T1 and one block of records for T2 simultaneously.
So, number of block accesses is 80 * 20 + 20 = 1620

So, Total number of block access = 32020 – 1620 = 30400


Thus, option (B) is correct.


Please comment below if you find anything wrong in the above post.

Quiz of this Question

My Personal Notes arrow_drop_up

Save