Slideshow:
Result of the heuristic-based query optimization
Previously, we studied applying highly cost reducing Algebraic Laws to obtain a optimal logical query plan:
Choice of different join ordering
We can consider each input set to the join operation as an input relation (= a table of tuples):
Choice of different join ordering
If there are > 2 input relations to the (generalized) join operator:
We have to find the best join ordering of the join operations:
How to evaluate different join ordering ?
The best join ordering is the one that has the least # tuples in all the result sets:
Because # tuples is proportional to # disk IO needed
Important observation on the result sets
The set of tuples selected by the top most ⋈ operation in every join ordering are equal:
Reason: all the different query plans are equivalent (the Relational Algebra expressions are equal to each other) !!!
We will use this fact to simplify how we compute the total # tuples produced by a query plan
Estimating the
# tuples in a result set
In order to find (= estimate) the # tuples in the output of an operation:
We will study these topic in the next few webpage....
- Overview of the
a logical query optimization
process:
- Use the Relational Algebra Laws to
find
least cost
logical query plan
without
considering the
ordering of the
join operations
(I.e.: the query plan has a smaller # tuples in the intermediate results)
Example:
- If there are
more than 2
input relations:
Then:
- Find the ordering of the join operations that results in the smallest # tuples in the intermediate results in the join tree
Example:
- If we have 3 input relations:
We must find a join ordering that produce the smaller # tuples in the intermediate results in the join tree:
Notice that the end result of all the joins are equal:
The only difference is the intermediate result sets
- Use the Relational Algebra Laws to
find
least cost
logical query plan
without
considering the
ordering of the
join operations
- The pre-requisite knowledge
needed in finding the
least cost
logical query plan:
- We need
statistical information on the
input relations
to:
- Estimate the size (# tuples) of the intermediate results in the relational algebra operations in the logical query plan
(The more detailed statistical information we have on the input relations, the more accurate (better) the estimates of the intermediate result sets)
- We must also
learn:
- How to estimate the # tuples in the intermediate result sets using the statistical information
- We need
statistical information on the
input relations
to: