MadeEasy Test Series: Databases - Joins

Question

Table T1 4000 record and 50 disk block Table T2  300 record 20 block has joined with specific condition, No index is available if nested loop join with most appropriate table in outer loop No of block access to read the data are ……….

what it would be if nested block joined used?

Comments

See here.

https://gateoverflow.in/3848/gate2005-it-82b

---

in case of nested loop join =>

for each record in the outer loop we access all the blocks in the inner loop so no. of block access are = Bo+(Ro*Bi)

=20+(300*50

=15020

in case of block nested loop join =>

for each block in the outer loop all the blocks of the inner loop are accessed and join in calculated So no. of block access are = Bo+(Bo*Bi)

=20+(20*50)

=1020

Answer 1

@Abhisek Tiwari 4 watch this video for concept https://www.youtube.com/watch?v=rT4eI3p3tVk

R natural join S

Here, R is outer relation and S is inner relation. For a minimum number of block accesses, outer relation should be smaller.

Therefore R is T2

NR = Total Number of records in R

BR = Number of blocks in R

Number of total block accesses = BR + NR*BS (for nested loop join) = 15020

Number of total block accesses = BR + BR*BS (for Block nested loop join) = 1020