A database table T1 has 2000 records and occupies 80 disk blocks. Another…
2005
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
Answer: B. 30400 — In Nested-loop join, for each record in T1 (2000 records), we scan all 400 records of T2. With T1 having 80 blocks and T2 20, the total block accesses are:…
- A.
0
- B.
30400
- C.
38400
- D.
798400
Attempted by 21 students.
Show answer & explanation
Correct answer: B
In Nested-loop join, for each record in T1 (2000 records), we scan all 400 records of T2. With T1 having 80 blocks and T2 20, the total block accesses are: (80 × 400) + 20 = 32,020. In Block nested-loop join, we use the smaller table (T2) as outer loop. We read each block of T2 (20 blocks), and for each, scan all 80 blocks of T1. So total accesses = (20 × 80) + 20 = 1,620. The reduction is 32,020 - 1,620 = 30,400. Option B is correct. Distractors like C and D overestimate the reduction by miscalculating access counts or using wrong table order. Option A ignores any improvement, which is incorrect as Block nested-loop significantly reduces I/O.