---

The Tuple-based Nested-loop Join Algorithm

---

---

---

 

Slideshow:

Tuple-based nested-loop join (R ⋈ S) algorithm
   
Iterator implementation of the tuple-based nested-loop join (R ⋈ S) algorithm

Notice that:   getNext( )  must return the next (one !) tuple in the join (R ⋈ S) result
Iterator implementation of the tuple-based nested-loop join (R ⋈ S) algorithm - open( )
   
State:
 
The red arrows (→) point to the next unread tuple in R and S
Iterator implementation of the tuple-based nested-loop join (R ⋈ S) algorithm - getNext( )
getNext( ) { while ( true ) { /* ======================================================== (1) Get the next tuple r ∈ R (and try joining with s) ======================================================= */ r = R.getNext(); // Try to get next tuple in R if ( r == NotFound ) { /* ========================================================= Tuple s has now joined with every tuple ∈ R Use next tuple ∈ S in the join operation if possible ========================================================= */ s = S.getNext( ); if ( s == NotFound ) // Last tuple { return NotFound; // Done !!! } R.Close(); // R.Open( ); // Rewind R to beginning r = R.getNext(); // r = current tuple in R } /* ================================================= (2) Try to return next tuple in R ⋈ S ================================================ */ if ( r(Y) == s(Y) ) { return (r,s); // Return next tuple of Join } /* ================================================= r and s failed to join - LOOP and try again ================================================= */ } }
Iterator implementation of the tuple-based nested-loop join (R ⋈ S) algorithm - close( )
 
❮ ❯



---

---

---

---

• Tuple-based Nested-Loop Join
  • Algorithm for R(X,Y) ⋈ S(Y,Z):

    for ( each tuple s ∈ S ) do { for ( each tuple r ∈ R ) do { if ( r(Y) == s(Y) ) { output (r, s); } } }

    ---

    ---

    Worst case performance:

    # Disk I/O = T(S) × T(R)

    Advantage:

    Requires only 2 input buffers (1 buffer to read tuples for relation S and 1 buffer to read tuples for relation R)

    ---

    ---

  • Note:

    The tuple-based nested-loop join can be easily implemented as an iterator         (You can implement any algorithm as an iterator, but the tuple-based nested-loop algorithm is especially easy because the open( ) and close( ) functions are trivial...)

  ---

  ---

  ---

  ---

  ---

  ---

  ---

  ---

• Implementing the tuple-base nested-loop join as an iterator
  • Iterator for the R ⋈ S operation:

    ---

    ---

  • Implementation of Open( ):

    Open( ) { R.Open( ); S.Open( ); s = S.getNext(); // s = current tuple of S }

    Graphically:

    ---

    ---

  • Implementation of getNext():

    Note:

    getNext( ) will output the next tuple in R ⋈ S I.e.: Initially: getNext( ) will return the tuple (x, a, c, x) New state of the iterator:

    ---

    getNext( ) algorithm in pseudo code:

    /* ------------------------------------------------------------------ getNext( ): output the next (1 !!!) tuple in the join R ⋈ S ----------------------------------------------------------------- */ getNext( ) { /* ============================================================== Note: At this point in the code, s contains the current tuple ∈ S ============================================================== */ while ( true ) { // Loop exits when: // 1. we found a tuple ∈ R that joins with s // 2. R ⋈ S is done (returns NotFound) /* ======================================================== (1) Get the next tuple r ∈ R (and try joining with s) ======================================================= */ r = R.getNext(); // Try to get next tuple in R /* ======================================================== (2) Check if R has reached the end of file: If R has ended: (A) get next tuple in S (B) Restart R from the beginning ======================================================= */ if ( r == NotFound ) { /* ========================================================= Tuple s has now joined with every tuple ∈ R (A) Use next tuple ∈ S in the join operation ========================================================= */ s = S.getNext( ); if ( s == NotFound ) { /* **************************************** We have processed the last tuple ∈ S: **************************************** */ return NotFound; // Done !!! } /* ================================================= The new tuple s ∈ S must join with all tuples in R (B) Restart R from the beginning ================================================= */ R.Close(); // Close first R.Open( ); // Reset R to beginning r = R.getNext(); // r = current tuple in R } /* ================================================= At this point in the code, we have: s = current tuple ∈ S for the Join operation r = current tuple ∈ R for the Join operation ================================================= */ /* ================================================= (2) Try to return next tuple in R ⋈ S ================================================ */ if ( r(Y) == s(Y) ) { return (r,s); // Return next tuple of Join } /* ================================================= r and s failed to join Repeat and try the next tuple in R (and if necessary get next tuple in S) ================================================= */ } }

    ---

    ---

  • Close( ):

    Close( ) { R.Close( ); S.Close( ); }