239 views
Consider two relations $R$ and $S$ of size $1200$ bytes and $1000$ bytes respectively. The size of a tuple in both relations is $20$ bytes. The block size of the system is $100$ Bytes. How many extra block transfers would be required, in the worst case, if nested loop join is used instead of block nested loop join technique to compute the natural join?

### 1 comment

ans is 480

$\text{Relation}\ R \ \text{require} \ \frac {1200}{100} = 12 \ \text {blocks}$

$\text{Relation}\ S \ \text{require} \ \frac {1000}{100} = 10 \ \text {blocks}$

$\text{Relation}\ R \ \text{has #tuples} = \ \frac {1200}{20} = 60 \ \text {tuples}$

$\text{Relation}\ S \ \text{has #tuples} = \ \frac {1000}{20} = 50 \ \text {tuples}$

Since $S$ is smaller thus it will be chosen as inner relation. In the worst case, one block of each relation can fit in memory, so the number of block transfers requires for nested join is $(50 × 12) + 10 = 610$ and for block nested join is $(12 × 10) + 10 = 130$

$\text {Therefore,}$ if we use a nested join instead of a block nested join we will require $610− 130 = 480$ extra block transfers.

$\text {So, ans is 480}$

Reference

Question 4

https://gateoverflow.in/147193/nptel