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( )
- 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)
- Requires only
2 input buffers
- 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...)
- The tuple-based
nested-loop join
can be
easily implemented 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:
- Initially:
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)
================================================= */
}
}
- getNext( ) will
output the
next tuple in
R ⋈ S
- Close( ):
Close( ) { R.Close( ); S.Close( ); }