Slideshow:
One more optimization step to consider
There is one more optimization step that we must consider...
One more optimization step to consider
Specifically: we can re-order the join operations !!!
One more optimization step to consider
Sample join ordering 1:
One more optimization step to consider
Sample join ordering 2:
One more optimization step to consider
Sample join ordering 3:
And so on...
There are 2 different tree forms and each tree form has 3! choices...
For a total of 2×3! (= 12) possible join orderings
Different join ordering can produce
different output set (size)
Notable observation:
Different intermediate result sets can have in different # tuples and therefore different cost measure !!!
What's next ?
- Consider the
optimized logical query plan
from the previous example:
(I have omitted the projections (π) to keep the figure small)
- Because the
join (⋈) operation is
both
associative and
communitative
(like the addition (+) operation),
we can perform the
2 join operations
is any order.
Abstractly, the above query plan is actually as follows:
This means that we have different choices of query plans !!
Some sample of choices of query plans are:
- Plan 1:
- Plan 1:
- Plan 1:
- And so one (there are 3! = 6 different plans possible !)
- Plan 1:
- Notable fact:
- The order in which the
join (⋈) operations are
processed
can result in
different
intermediate result sets
Example:
The join operation σbdate > '1957'(employee) ⋈ works_on and the join operation σpname = 'A'(project) ⋈ works_on will produce different intermediate result sets for the rest of the query plan !
- The order in which the
join (⋈) operations are
processed
can result in
different
intermediate result sets
- In the next section,
we will explore:
- The effect of
the
order of
the join operations
on the
cost
of a logical query plan
- How to determine the best join ordering that produce the least number of tuples as intermediate result sets
- The effect of
the
order of
the join operations
on the
cost
of a logical query plan