Slideshow:
Review: the
Sort-Join algorithm
Pseudo code:
Use M-1 buffers and read all tuples with
the smallest join value in relation R
Use 1 buffer to scan relation S;
While ( R ≠ empty OR S ≠ empty )
{
Let r = the current smallest join value ∈ R
Let S = the current smallest join value ∈ S
if ( r < s )
{
Use M-1 buffers and read all tuples with
the next smallest join value in relation R;
}
else if ( s < r )
{
Use 1 buffer to scan to the next join value in relation S;
}
else /* join R and S */
{
Scan and join all tuples in S with join attr = r;
// Use next join value in R
Use M-1 buffers and read all tuples with
the next smallest join value in relation R;
}
}
|
IO cost of
the Sort-Join algorithm
Buffer requirement of
the Sort-Join algorithm
All joining tuples (for a specific join value) of relation R must be contained in (M−1) buffers:
An alternate (clever) way to use
the Sort-Join algorithm
I.e.: we can adapt the Sort-Join algorithm to use an ordered index !!!
- Phase 1: perform a
complete
TPMMS on
both relations
and materialize the
result on disk
- Relation R:
- Sort relation R using the TPMMS algorithm
- Relation S:
- Sort relation S using the TPMMS algorithm
Graphically: (this is the TPMMS algorithm)
- Relation R:
- Phase 2: join
the sorted relations:
- Use 1 buffer to
read relation R
- The smallest join value may occupy more than 1 buffer
- Use 1 buffer to
read relation S
- The smallest join value may occupy more than 1 buffer
- If necessary
(= when smallest join value
may occupy
more than
1 buffer):
- Use the remaining M−2 buffers to store tuples in R and/or S that contain all smallest joining attribute values
The sort-join Algorithm:
Read R until all tuples with 1st join value are stored in memory buffers; Read the first block of S; While ( R ≠ empty OR S ≠ empty ) { Let r = the current smallest join value ∈ R Let S = the current smallest join value ∈ S if ( r < s ) { Situation:
skip all tuples with join attr y1 in R;
}
else if ( s < r )
{
skip all tuples with join attr y1 in S;
}
else /* r = s = y1 */
{ /* ===================================================
Join on join value r = s = y1
=================================================== */
read S as long as join attr = s (= y1);
Join tuples in S with join attr = y1;
When done:
reuse buffers;
Read R until all tuples with next smallest join value
are stored in memory buffers;
}
}
- Use 1 buffer to
read relation R
- Cost:
- Read the
relation R
once:
B(R)
- Read the
relation S
once:
B(S)
- Total cost:
Cost of sort-join = B(R) + B(S)
Note:
- The sort-join algorithm is
a one-pass algorithm !!!
(The sort-join algorithm processes the smallest value first, and will not re-visit the processed values again !!!)
- Read the
relation R
once:
B(R)
- Memory constraint:
(according to the text book)
- All joining tuples
(for a specific
join value)
of relation R
must be
contained in
(M−1) buffers:
- All joining tuples
(for a specific
join value)
of relation R
must be
contained in
(M−1) buffers:
- Important fact:
- The sort-join algorithm
can be
used to
join
2 relations
if:
- We can access the join values in the relations in an ordered (sorted) manner !!!
Fact:
- We can
access the
join values in
the relations
in an
ordered (sorted) manner
if:
- We have an ordered index on the relation !!!
- The sort-join algorithm
can be
used to
join
2 relations
if: