Examples of Block Nested Loops
Cost: Scan of outer + #outer blocks * scan of inner
With Reserves (R) as outer, and 100 pages of R:
- Cost of scanning R is 1000 I/Os; a total of 10 blocks.
- Per block of R, we scan Sailors (S); 10*500 I/Os.
- If space for just 90 pages of R, we would scan S 12 times.
With 100-page block of Sailors as outer:
- Cost of scanning S is 500 I/Os; a total of 5 blocks.
- Per block of S, we scan Reserves; 5*1000 I/Os.
With sequential reads considered, analysis changes: may be best to divide buffers evenly between R and S.