- Consider the
possible join ordering when there
are 3 relations:
Form 1: ( R ⋈ S ) ⋈ T ( S ⋈ R ) ⋈ T ( R ⋈ T ) ⋈ S ( T ⋈ R ) ⋈ S ( S ⋈ T ) ⋈ R ( T ⋈ S ) ⋈ R Form 2: R ⋈ ( S ⋈ T ) R ⋈ ( T ⋈ S ) S ⋈ ( R ⋈ T ) S ⋈ ( T ⋈ R ) T ⋈ ( R ⋈ S ) T ⋈ ( S ⋈ R )
- You can clearly see
the permutation of
R, S, T in the
possible orderings:
Form 1: ( • ⋈ • ) ⋈ • Form 2: • ⋈ ( • ⋈ • )
- Conclusion:
- The number of
different join orderings of
n relations
is exponentially large !!!
(Because the number of permutations is exponentially large)
- The number of
possible join trees to
consider is
just too large
- We need to reduce the search space....
- The number of
possible join trees to
consider is
just too large
- The number of
different join orderings of
n relations
is exponentially large !!!
- Left-deep tree:
- Left-deep
join tree = a join tree
of the following form:
- Left-deep
join tree = a join tree
of the following form:
- Example: left-deep join tree
- Example: 3-way join:
The possible left-deep join trees are:
# left deep tree = 6 (= 3!)
- Clearly:
- Number of left-deep join trees with N relations = N ! (factorial))
- Claims:
- Left-deep join trees
interacts
very well with
commonly used
join (implementation) algorithms,
such as:
- One-pass
join algorithms
(= the preferred algorithm for
minimizing the
number of disk IOs)
benefit greatly
when join plan is
a left-deep tree
- Nested-loop join algorithm (= preferred algorithm for minimizing the memory utilization) also benefit greatly when join plan is a left-deep tree
- One-pass
join algorithms
(= the preferred algorithm for
minimizing the
number of disk IOs)
benefit greatly
when join plan is
a left-deep tree
In other words:
- Query plans based on:
- Left-deep join trees and
- Commonly used join implementations (algorithms)
will tend to be:
- more efficient
than:
- Other kinds of join trees and
- Commonly used join implementations (algorithms)
- Left-deep join trees
interacts
very well with
commonly used
join (implementation) algorithms,
such as:
- Consider the processing steps
of the following
join:
R ⋈ S ⋈ T ⋈ Uusing the one-pass join algorithm.
- Processing
R ⋈ S ⋈ T ⋈ U
using a left-deep join tree:
- Query plan using a
left-deep join tree:
The one-pass join algorithm will build a search index on the left operand:
- Notice how
the
result
of each join operation
is
passed through
pipelining
to the next operation:
Specifically: the join ⋈2 must wait for the all result tuples from ⋈1 to build its search index:
Only when ⋈1 is done, the ⋈2 will finish building the search index and ⋈2 start output tuples to ⋈3:
- Conclussion:
- Although the
join operation
⋈3 is
active:
- ⋈3 is idle and is waiting for tuples output by ⋈2
(If there are more join operations in the left-deep tree, all these join operations will also be idle (waiting) !!!)
- Only the
join operation
⋈ 2 is
semi-active:
- ⋈2 is accumulating tuples from ⋈1 into its buffers
Initially:
- Only ⋈1 is active !!!
- Although the
join operation
⋈3 is
active:
- Buffer utilization in
the processing of
⋈1:
- ⋈1 will need to
allocate buffer for
the one-pass join algorithm:
-
⋈2
must allocate buffers to
store (receive) the
tuples from
R ⋈ S:
Notice that:
- The join operation
⋈2 will
not output
any tuple until
all tuples from
⋈1
has been
received !!!
(Because phase 1 -- building search index -- must complete before phase 2 -- join tuples --- can start !!!)
Therefore:
- The
join operation
⋈1
can release its
buffers allocated for
the
join algorithm
when ⋈2
starts:
- ⋈1 will need to
allocate buffer for
the one-pass join algorithm:
- Query plan using a
left-deep join tree:
- Now consider the
processing of
R ⋈ S ⋈ T ⋈ U
using a another type of join tree
(e.g., a
right-deep join tree)
- Query plan using a
right-deep join tree:
- Phase 1 of the
one-pass algorithm is:
- Build a
search index for the
left operand:
Because each left operand is a relation, all the join operators will be able to read tuples from the relation and build their index !!!
- Build a
search index for the
left operand:
- Result:
The one-pass algorithms will request a huge amount of memory buffers !!!
- Query plan using a
right-deep join tree:
- Conclussion:
- The left-deep tree
will naturally
suppress
the memory requests made by
the one-pass join algorithm
- As a result:
- Memory requirement of the query plan is reduced !!!!
- The left-deep tree
will naturally
suppress
the memory requests made by
the one-pass join algorithm
- Recall the nested-loop
join algorithm:
while ( S ≠ empty ) { Read M - 1 blocks of S and organize them into a search structure (e.g., hash table)' Rewind R; while ( R ≠ empty ) { Read 1 block (b) of R; for ( each tuple t ∈ block b ) do { Find the tuples s1, s2, ... of S (in the search structure) that join with t Output (t,s1), (t,s2), ... } } } - Consider the processing steps
of the following
join:
R ⋈ S ⋈ T ⋈ Uusing the nested-loop join algorithm.
- Processing
R ⋈ S ⋈ T ⋈ U
using a left-deep join tree:
- Query plan using a
left-deep join tree:
- The result is
again
passed through
pipelining:
- All the
join operators are
active; and
the nested-loop join algorithm
at each ⋈ operation will
first read one tuple
(or (M-1) blocks of tuples)
from the first (= left) operand:
The ⋈ 1 operator will scan the relation S once for each tuple
(That's the behavior of the nested loop join algorithm)
- Eventually, the join
⋈ 1 will
produce a tuple and
pass it to
⋈ 2:
The ⋈ 2 operator will now scan the relation T once for each tuple
(Again, that's the behavior of the nested loop join algorithm)
- And so on...
The nested-loop algorithm behaves as it should !!!
- Query plan using a
left-deep join tree:
- Now consider the
processing of
R ⋈ S ⋈ T ⋈ U
using a another type of join tree
(e.g., a
right-deep join tree)
- Query plan using a
right-deep join tree:
- Let's focus on
the join operator ⋈ 3:
The operator ⋈ 3 read one tuple from its first (=left) operand
- Then operator ⋈ 3 will
read all tuples from
its second (= right) operand:
We will execute this query once:
- Next,
the operator ⋈ 3 read
another tuple from its
first (=left) operand:
- Then operator ⋈ 3 will
read all tuples again from
its second (= right) operand:
Because the second operand is a query, we will execute this query again:
The nested-loop join algorithm will read its first relation (T and R) multiple times !!!
- This is not the
behavior of the
nested-loop join algorithm !!!
(Because the nested-loop join algorithm always read its first relation exactly once !!!)
- This is not the
behavior of the
nested-loop join algorithm !!!
- Query plan using a
right-deep join tree:
- Conclussion:
- The nested-loop join algorithm
behaves as expected
when using a
left-deep join tree
- Some of the nested-loop join algorithm in the query plan will scan their first relation multiple times using a different join tree !!!
- The nested-loop join algorithm
behaves as expected
when using a
left-deep join tree
- This choice should now be
obvious:
- We will limit our ordering of joins to those that correspnd to a left-deep join tree
- Recall:
the number of
left-deep join trees of
N relations is
equal to N !
- Join:
The possible left-deep join trees for the join are:
- Join:
- We will study an
algorithm to find the
best
left-deep join tree
next.....