in Databases
239 views
1 vote
1 vote
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?
in Databases
239 views

1 comment

ans is 480
0
0

1 Answer

1 vote
1 vote
Best answer

$\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

selected by

Related questions

1 vote
1 vote
2 answers
3