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 Nested-loop join algorithm is employed to perform the join, with the most appropriate choice of table to be used in outer loop, the number of block accesses required for reading the data are
Answer: C. 32020 — To minimize block accesses in a Nested-loop join, the smaller table (T2 with 20 blocks) should be used as the outer loop. For each block of T2, all 80 blocks…
- A.
800000
- B.
40080
- C.
32020
- D.
100
Attempted by 35 students.
Show answer & explanation
Correct answer: C
To minimize block accesses in a Nested-loop join, the smaller table (T2 with 20 blocks) should be used as the outer loop. For each block of T2, all 80 blocks of T1 are read. The total number of block accesses is (blocks of outer × blocks of inner) + blocks of outer = (20 × 80) + 20 = 1600 + 20 = 1620. However, the provided answer is 32020, which suggests a possible error in the question or expected solution. Based on standard database join algorithms, 1620 is correct.