Join Algorithm 1: Block Nested Loop

1
Join Algorithm 1 Block Nested Loop

• This presentation is prepared by Yufei
  Tao.http//www.cse.cuhk.edu.hk/taoyf

2
Join algorithms

• Joins are one of the most expensive operators in
  databases. ?????????????????
• Next we will learn several algorithms for
  performing joins. ???,????????????
• The first one is block nested loop (BNL). ?????
• BNL is designed to handle any join condition,
  i.e., queries of the form ????????????????SEL
  ECT FROM T1, T2 WHERE any join condition

3
Problem settings ????

• Two tables T1 and T2 whose tuples are stored in
  n1 and n2 pages, respectively. ????????T1?T2??????
  ???n1?n2????
• Memory has M pages.??????M????
• SELECT FROM T1, T2WHERE any join condition
• How to answer the above query in small I/O
  cost???????I/O????????????

4
Block nested loop (BNL) ????

• Without loss of generality, assume n1 lt
  n2.?????,??n1 lt n2?
• Allocate M 1 memory pages to T1, and 1 page to
  T2.??M 1??????T1,????????T2?
• Load the first M 1 pages of T1 into
  memory.?T1??M 1?????????
• Load the first page of T2 into memory.
  ?T2????????????
• Join the tuples in memory.???????????????

5
BNL (cont.)

• Use the memory page allocated to T2 to read the
  2nd page of T2.?T2????????????????????
• Then, join the tuples in memory.???????????????

6
BNL (cont.)

• Similarly, read each page of T2 in turn, and join
  it with the M 1 pages of T1 in
  memory.???,?T2????????T1??M 1????????

7
BNL (cont.)

• Similarly, read each page of T2 in turn, and join
  it with the M 1 pages of T1 in
  memory.???,?T2????????T1??M 1????????

8
BNL (cont.)

• Similarly, read each page of T2 in turn, and join
  it with the M 1 pages of T1 in
  memory.???,?T2????????T1??M 1????????

9
BNL (cont.)

• Repeat the above with the next M 1 pages of
  T1.??,?T1??M 1???????,????T2,???????

10
BNL (cont.)

• Continue in this fashion.????????
• Totally T2 is scanned ?n1 / (M - 1)?
  times.T2??????n1 / (M - 1)???
• T1 is scanned only once.T1???????
• Total cost ?n1 / (M - 1)? ? n2 n1.???I/O???
• T1 is called the outer relation.T1???????
• T2 is called the inner relation.T2??????

11
BNL (cont.)

• BNL always uses the smaller relation to be the
  outer relation.?????????????????
• Smaller relation as the outer?????
• Cost ?n1 / (M - 1)? ? n2 n1.
• Larger relation as the outer???????????????
• Cost ?n2 / (M - 1)? ? n1 n2.
• Usually the first cost is smaller. ??,??????????

12
Optimal cost ???????

• Two tables T1 and T2 whose tuples are stored in
  n1 and n2 pages, respectively. ????????T1?T2??????
  ???n1?n2????
• Assume no index at all.???????????T1?T2???
• SELECT FROM T1, T2WHERE any join condition
• Obviously, no algorithm can solve the query in
  less than n1 n2 I/Os. ???,????????????n1
  n2?I/O????????????

13
Optimality of BNL ????????

• BNL cost ?n1 / (M - 1)? ? n2 n1????????
• When n1 M 1, the above cost equals the
  optimal cost n1 n2.??????,???????????
• In other words, the memory is large enough to
  hold the smaller relation plus one more
  page.????,?????????????,???????????????