No of block access, completely depends on the type of JOIN algo we are using to access the blocks.
Mainly, we have 2 types of algorithms:
- Nested Loop Join
- Block Nested Loop Join
For (a) R JOIN S , means R is in outer loop and S is in inner loop
No of Block access (Nested Loop Join -: Record By Record): X + n*Y
No of Block access (Block Nested Loop Join -: Block By Block): X + X*Y
For (b) S JOIN R , means S is in outer loop and R is in inner loop
No of Block access (Nested Loop Join -: Record By Record): Y + m*X
No of Block access (Block Nested Loop Join -: Block By Block): Y + Y*X