Slideshow:
Left deep trees
The number of left deep trees for a N-way join is
N!
Why left-deep trees are
preferred
in query processing
Consequently:
- Consider all
possible join ordering of
3 relations
R, S and T:
Form 1:
Form 2:
- You can clearly see
the permutation of
R, S, T for
each tree-"structure":
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: