Slideshow:
Memory buffer constraint in choosing a join (⋈) algorithm
How do you pick the best algorithm considering the memory buffer constraint ?
Special opportunities:
these algorithms has lowest cost
- use them if possible
Sort-join: (is better than one-pass due to lower buffer utilization)
Special opportunities:
these algorithms has lowest cost
- use them if possible
Zigzag-join: (is better than one-pass due to lower buffer utilization)
Special opportunities:
these algorithms has lowest cost
- use them if possible
Index-join using a extremely small first input relation
See: click here
When no special opportunities:
find lowest cost abiding to
buffer constraint
Choices of join algorithms:
|
When no special opportunities:
find lowest cost abiding to
buffer constraint
Best possible choice:
When no special opportunities:
find lowest cost abiding to
buffer constraint
2nd best choice:
When no special opportunities:
find lowest cost abiding to
buffer constraint
Otherwise:
The choice depends on the number of available buffers (2-pass needs sqrt(B(S)) buffers !)
- Fact:
- Unlike
the selection σ
(that can use 1 buffer !!!)
an important factor
that impact the
choice for
an algorithm for
join ⋈ is:
- Memory requirement !!!
- Unlike
the selection σ
(that can use 1 buffer !!!)
an important factor
that impact the
choice for
an algorithm for
join ⋈ is:
- Factors that influence
the choice of
algorithm for
⋈:
- Available # buffers
to execute the
query
-
V(R,a)
- V(R,a) will determine the output size of the join results
(We will need to store the output of some join results when the query is executed)
- Available # buffers
to execute the
query
- Recommendations:
- Special opportunities:
- If
both
input relations R and S are
sorted:
- Use the (one-pass) sort-join algorithm (see: click here)
With:
- IO Cost =
B(R) + B(S)
( Sort join is a one-pass algorithm and scans R and S once) !!!
- Buffer requirement:
-
M-1 buffers must hold
all join values
in one of the
relations:
(We use 1 buffer to scan for joining tuples in the other relation)
B(R) B(S) -------- ≤ M-1 or ------- ≤ M-1 V(R,a) V(S,a)
-
M-1 buffers must hold
all join values
in one of the
relations:
- If
both
input relations have an
ordered index on the
join attribute:
- Use the zig-zag join algorithm (see: click here)
With:
- IO Cost =
B(R) + B(S) if
index is
clustering
-
IO Cost =
T(R) + T(S) if
index is
non-clustering
( Zig-zag join is also a one-pass algorithm) !!!
- Buffer requirement:
- We
extract the
tuples and
store them
in separate buffers:
-
M-1 buffers must hold
all join values
in one of the
relations
(We use 1 buffer to scan for joining tuples in the other relation)
B(R) B(S) -------- ≤ M-1 or ------- ≤ M-1 V(R,a) V(S,a)
- We
extract the
tuples and
store them
in separate buffers:
- small R(a,b) ⋈(index) S(b,c):
- R(a,b) is very small and
- There is an index on S.b
then:
- use index join (see: click here)
Reason:
- The IO cost is
usually
less than
the one-pass algorithm
(See example in click here)
- If
both
input relations R and S are
sorted:
- If available buffers
≥ B(S)
(S is the
smaller relation):
- Use one-pass join algorithm (see: click here)
- If available buffers ≥
1/3 ×
B(S) + 1
(S is the
smaller relation):
- Use nested-loop join algorithm (see: click here)
Reason:
- The nested-loop join algorithm
has lower disk IO when
M−1 ≥
1/3×B(S):
B(S) IO cost of nested loop = B(S) + ------ B(R) // M−1 ≥ 1/3 B(S) M-1 ≤ B(S) + 3 B(R) // B(S)/(M-1) ≤ 3
- Therefore,
the number of disk I/Os incurred
by the nested-loop algorithm is:
# disk IOs ≤ B(S) + 3 × B(R)which is better than a 2-pass algorithm (which has cost 3 B(S) + 3 B(R))
- Use a 2-pass
(hash-based is
preferred)
join algorithm
- The IO cost = 3B(R) + 3B(S)
- And if necessary, use a
multi-pass algorithm....
- The IO cost ≥ 5B(R) + 5B(S) (3 pass)
- The IO cost ≥ 7B(R) + 7B(S) (4 pass)
- And so on...
Note:
- The choice of
any of the (above) algorithms
is not available if:
- The condition is
not met
For example:
- If there is no index, then you cannot choose an index join or zig-zag join
- The memory (buffer) requirement is not satisfied !!!
- The condition is
not met
- Special opportunities:
- Pseudo code:
minCost = Infinite; if ( R and S sorted ) { joinCost = B(R) + B(S); if ( joinCost < minCost ) { minCost = joinCost; Method = "SortJoin"; } } if ( R and S have ordered index ) { if ( Index is clustering ) joinCost = B(R) + B(S); else joinCost = T(R) + T(S); if ( joinCost < minCost ) { minCost = joinCost; Method = "ZigZag-Join"; } } if ( S has an index ) { if ( Index is clustering ) joinCost = B(R) + T(R) × B(S)/V(S,Y); else joinCost = B(R) + T(R) × T(S)/V(S,Y); if ( joinCost < minCost ) { minCost = joinCost; Method = "IndexJoin"; } } // Nested loop joinCost = B(S) + B(S)/(M-1) * B(R); if ( joinCost < minCost ) { minCost = joinCost; Method = "NestedLoop-Join"; } // Then test 1-pass, 2-pass... etc