---

Index join vs. the one-pass join algorithm

---

---

---

 

Slideshow:

Comparing the performance of the index-join and one-pass join algorithms
   
Compare the performance (# disk IOs) of:
Index join algorithm using a clustering index One-pass join algorithm
Comparing the performance of the index-join and one-pass join algorithms
   
Performance (# disk IOs) of the Index Join algorithm (using a clustering index):

Comparing the performance of the index-join and one-pass join algorithms
   
Performance (# disk IOs) of the One-pass Join algorithm:

The one-pass join algorithm is clearly superior !!!
So why (when) do we use the Index Join algorithm ???
Comparing the performance of the index-join and one-pass join algorithms
   
Again:   compare the performance (# disk IOs) of:
Index join algorithm using a clustering index One-pass join algorithm
Comparing the performance of the index-join and one-pass join algorithms
   
Performance (# disk IOs) of the Index Join algorithm (using a clustering index):

Comparing the performance of the index-join and one-pass join algorithms
   
Performance (# disk IOs) of the One-pass Join algorithm:

The Index Join algorithm is now superior !!!
So when do we use have a (very) small relation R ???
When can we use index join ?
 
❮ ❯



---

---

---

---

• Comparing the index-join and the one-pass algorithm
  • Consider the following scenario 1: (large relation R)

    relation R(X,Y) relation S(Y,Z) -------------------------------------------- B(R) = 1000 blks B(S) = 500 blocks T(R) = 10000 tuples T(S) = 5000 tuples V(S,Y) = 100

    ---

    ---

    ---

    ---

  • Assuming that:

    There is a clustering index on Y of relation S:

    ---

    ---

    Cost of (= # disk IO used) the Index Join algorithm:

    B(S) # disk IO = B(R) + T(R) × ------- V(S,Y) 500 = 1000 + 10000 × ------ 100 = 51,000 blocks

    Cost of (= # disk IO used) the one-pass join algorithm :

    # disk IO = B(R) + B(S) (See: click here) = 1000 + 500 = 1500 blocks

    ---

    ---

  • Note:

    The cost of the two-pass join algorithm is: # disk IO = 3 B(R) + 3 B(S) (See: click here) = 4500 blocks So even a two-pass algorithm performs better than the index-join algorithm !!!

  • $64,000 question:

    🤔   Why bother to use the index-join algorithm ????   🤔

  ---

  ---

  ---

  ---

• Situation where index-join performs better
  • Consider the following scenario 2: (tiny relation R)

    relation R(X,Y) relation S(Y,Z) -------------------------------------------- B(R) = 1 blks B(S) = 500 blocks T(R) = 10 tuples T(S) = 5000 tuples V(S,Y) = 100

    ---

    ---

    ---

    ---

  • Assume that:

    There is a clustering index on Y of relation S:

    # disk IO used by the index Join algorithm:

    B(S) # disk IO = B(R) + T(R) × ------- V(S,Y) 500 = 1 + 10 × ----- 100 = 51 blocks

    # disk IO used by the one-pass join algorithm based on TPMMS

    # disk IO = B(R) + B(S) (See: click here) = 1 + 500 = 501 blocks

  ---

  ---

  ---

  ---

• When to use the Index-Join algorithm
  • When to use index-join:

    One of the relations in the join is very small      and      The other (large) relation has an index on the join attribute(s)

    ---

    ---

  • Note:

    Small input relations are often created by the query optimization step Example: Query: select * from employee, department where dno = dnumber and salary > 50000 Initial query plan: σsalary > 50000 ( employee ⋈dno=dnumber department ) Optimized query plan: σsalary > 50000 (employee) ⋈dno=dnumber department σsalary > 50000 (employee) is a very small relation !!!