Slideshow:
Further adaptations: missing ordered index
Further adaptations: missing ordered index
Adaptation case 1: when M ≥ B(R) + 1
If M ≥ B(R) + 1 → use the one-pass join algorithm !!!
Adaptation case 2:
when M ≤ B(R)
and M > sqrt(B(R))
If M > sqrt(B(R)) → Use pass 1 of the TPMMS algorithm to sort relation R into k sorted fragments:
Write the sorted fragments back to disk
Note: k < M - otherwise, the next step will not have enough buffers....
IO cost for this step = 2 B(R)
Adaptation case 2:
when M ≤ B(R)
and M > sqrt(B(R))
Use k buffers for the k sorted fragments and (M−k−1) buffer to hold the joining tuples for R.
Use the zig-zag join on the k sorted fragments Ri and use the ordered index on relation S to access S in a sorted order:
IO cost for this step = B(R) + B(S)
Total cost = 3B(R) + B(S) !!! (it is little more efficient than the 2 pass join algorithms)
- Recall:
- Zig-zag join
requires that:
- R must have an ordered index on the joining attribute
- S must have an ordered index on the joining attribute
- Zig-zag join
requires that:
- Suppose:
- R
does not have
an ordered index
on the joining attribute
(S does have an ordered index on the joining attribute)
- R
does not have
an ordered index
on the joining attribute
- Adapting the
zig-zag join:
- We partially sort
the relation R
- Use the sort-join algorithm on the (partially) sorted relation R (and access relation S using its ordered index)
- We partially sort
the relation R
- If
B(R) ≤ M ---
use the one-pass join algorithm !!
(You will incur a little additional buffer swapping IO cost, but the additional disk IO will be minimal for 1 missing buffer)
- If
B(R) > M
and
sqrt(B(R)) < M:
- Use
pass 1 of the
TPMMS algorithm
to sort
relation R into
k
(k < M) sorted fragments:
IO cost for this step = 2 B(R)
Note:
- Do not use pass 2 of TPMMS because it will incur too many disk IO cost !!!
- Use k buffers for
the k sorted fragments and
(M−k−1) buffer
to hold the joining tuples for
R.
Use the zig-zag join on the k sorted fragments Ri and use the ordered index on relation S to access S in a sorted order:
IO cost for this step = B(R) + B(S)
Grant total IO cost = 3 B(R) + B(S)
(It's a little lower than the 2-pass join algorithms !)
- Use
pass 1 of the
TPMMS algorithm
to sort
relation R into
k
(k < M) sorted fragments:
|
|
- Consider the following
scenario:
relation R(X,Y) relation S(Y,Z) -------------------------------------------- B(R) = 1000 blks B(S) = 500 blocks T(R) = 10000 tuples T(S) = 5000 tuples R is clustered S is clustered No index on R Clustering sorted index on S V(S,Y) = 100
Available memory: M = 101 buffers
- Adapted
the zig-zag join algorithm:
- Because:
M = 101 buffers B(R) = 1000 blocks
we must use TPMMS to sort R
-
For simplicity (on
calculation), we will
use 100 buffers
(even tough we have M = 101) to
sort
R:
- We use 10 buffers to
read the
sorted chunks of
R
We use 1 buffer to scan for matching tuples in S
And use the remaining buffers to store joining tuples from relation R:
Algorithm:
Read all tuples with the smallest join key value in the sorted chunks on R (store then in the 90 buffers) Use the clustering index on S to find/scan all tuples with the smallest join key value in S; Join these 2 set of tuples if join value are equal;Requirement:
- All tuples with smallest join values in R must fit in 90 buffers
Cost of this Join algorithm:
(1) Sort R into chunks and save sorted chunks on disk: 2 × B(R) blocks
(2) Join sorted chunks of R and S (using clusting index on S): (2a) We need to scan all sorted chunks of R: We will use: B(R) disk I/Os (2b) Because the sorted index on S is clustering: We will scan S once (see: click here) # blocks read: B(S) disk I/Os
# disk IOs to join R and S = 3B(R) + B(S) blocks
- Because:
- Total cost of the
example:
Total cost = 3 B(R) + B(S) = 3 × 1000 + 500 // In example = 3500 blocks (We beat TPMMS based join which has cost: 3B(R) + 3B(S) = 4500 block disk IOs)Note:
- We have assumed that the IO cost for the reading the index is negligible !!!