block access

Question

Answer 1

Number of blocks needed for relation R     =  Total number of records of R / Records in one block

                                                             =  2000 / [ Block size / Record size ]

                                                             =  2000 / [ 100 / 5 ]

                                                             =  100

Similarly number of blocks for relation S   =   500 / 20

                                                             =   25

We know :

Number of block accesses for block nested loop join  =   X + X * Y   where X is in outer loop and Y in inner loop 

Hence to get minimum number of block accesses we have to choose relation S as outer loop for comparison with relation R.. 

Hence number of block accesses needed in this scenario  =    25 + 25 * 100

                                                                                          =    2525

If R were in outer loop and S in inner loop then 

Number of block accesses                                                   =    100 + 25 * 100 

                                                                                          =    2600

Hence minimum number of block accesses                        =   min(2525 , 2600)

                                                                                           =  2525

Suggested reading : https://gateoverflow.in/76143/block-nested-loop-join

Answer 2

Size of record is 5 Byte, block size is 100 Byte then

#f records per block = 100/5 =20 Records/Block

Relation R contain =2000 records then

#f blocks occupied by Relation R = 2000/20 =100 Blocks (let it X)

similarly, for S , 25 Blocks (let it Y)

minimum no of block access will be if we do S joins R

= Y+Y.X =25+25*100=2525

if we do R joins S

=X+X.Y =100+100*25 =2600