Simple Nested Loops Join

• For each tuple in the outer relation R, we scan the entire inner relation S.

  • Cost: M + pR * M * N = 1000 + 100*1000*500 I/Os.

• Page-oriented Nested Loops join: For each page of R, get each page of S, and write out matching pairs of tuples <r, s>, where r is in R-page and S is in S-page.

  • Cost: M + M*N = 1000 + 1000*500
  • If smaller relation (S) is outer, cost = 500 + 500*1000

foreach tuple r in R do

foreach tuple s in S do

if ri == sj then add <r, s> to result

Previous slide

Next slide

Back to first slide

View graphic version