- The most common
initial query plan:
- Fact:
- Before you can improve a query plan (or anything), you need a measure to quantify the improvement
- Measuring the
improvement of
query plans:
- The ultimate measure is:
- Executing time
-
A good measure of
the excution time
without having to
execute the query is:
- number of disk IO operations performed by the execution of the query plan
We can compute the number of disk I/O operations by:
- Knowledge the size of the
input relations
- The algorithms used
(see the chapters on the different implementations of various algorithms !!!)
- The ultimate measure is:
- Comparing 2
query plans:
- Query plan 1:
- Query plan 2:
- Query plan 2 is better because it perform less disk IO operations
- Query plan 1:
- Consider the following
2 query plans:
- Query plan 1:
- Query plan 2:
Question:
- Which query plan is better ????
Answer:
- The answer to the
question is determined by:
- The size of the
intermediate result relations
produced by the
query plans:
because:
- The size will
determine the
number of disk IO performed
by the
upper relational operators:
- The size of the
intermediate result relations
produced by the
query plans:
- Query plan 1:
- Very important fact:
- The size of the
result set of
a Relational Operator
is not dependent
on its
implementation algorithm
because:
- The size of the
result set of
a Relational Operator
is not dependent
on its
implementation algorithm
because:
- Facts:
- In the optimization of
logical query plans,
we will:
- Move the relational algebra operators up/down the query tree
- This movement of
relational algebra operators will
change the
size of
the
intermediate result sets
As mentioned above:
- The size of the intermediate result sets is independent from the implementation algorithm of the relational operation !!!
- In the optimization of
logical query plans,
we will:
- Simplifying the
cost computation of
query plans:
- When we compare
2 different
logical query plans,
we can use
the size
of the
intermediate results
as cost measure
- Furthermore, we do not need to consider the implementation algorithm of the Relational Algebra operation in the optimization of a logical query plan !!!
- When we compare
2 different
logical query plans,
we can use
the size
of the
intermediate results
as cost measure
- Cost measure used to
compare between
2 different logical query plans:
- cost of a logical query plan = the number of tuples in the intermediate results
- Steps in the
optimization of
a logical query plan:
- Use the Relational Algebra Laws to
re-write
the order of the
Relational Algebra expression
(So that the new expression has a smaller intermediate results)
Example:
- If there are
more than 2 relations
involved:
then determine an ordering of the join operations that results in the smallest number of tuples in the intermediate results:
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
re-write
the order of the
Relational Algebra expression
- The key to
a successful query optimization:
- We need to have
statistical information on the
input relations
(The more information we have on the relations, the more accurate (better) the estimates of the intermediate result sets)
- We must also learn how to use the statistical information to estimate the number of tuples in the intermediate result sets
- We need to have
statistical information on the
input relations