Slideshow:
Recap of what we have learned so far in
query optimization
Goal: find the best possible execution for an SQL query (as a relational algebra tree)
Recap of what we have learned so far in
query optimization
Goal: find the best possible execution for an SQL query (as a relational algebra tree)
Recap of what we have learned so far in
query optimization
Goal: find the best possible execution for an SQL query (as a relational algebra tree)
What's left to do ?
Goal: find the best possible execution for an SQL query (as a relational algebra tree)
What's left to do ?
Next: select the best feasible algorithm for each operation and the result passing scheme:
- What have we
learned:
- Minimize the
cost of a
logical query plan using
Algebraic Laws:
Example:
- If the
logical query plan
has
more than 2 relations:
- Find the best join ordering
Graphically:
- Minimize the
cost of a
logical query plan using
Algebraic Laws:
- Result of
these 2 steps
- We obtain the optimal logical query plan
Graphically:
- What things
remain to be done:
- Select
an (implementation) algorithm for
each
relational operator
in the logical query plan
- Decide
how to
pass the
result from
one operator to
the next:
- Use pipelining
- Fast
- But very memory intensive
- Or use
materialization
- Slower
- But uses less main memory
- Use pipelining
- Select
an (implementation) algorithm for
each
relational operator
in the logical query plan
- Fact:
- For most relation algebra
operator, it is trivial how
to select an
algorithm:
if ( enough memory available for one-pass algorithm ) { Use the one-pass algorithm; } else if ( enough memory available for two-pass algorithm ) { Use a 2-pass algorithm; } else ....
- For most relation algebra
operator, it is trivial how
to select an
algorithm:
- There are 2 exceptions:
- Selection
σC
- Have an index on the selection attribute can significantly reduce the # disk IOs
- Join
⋈C
- Have an index on the join attribute can significantly reduce the # disk IOs
- Selection
σC
- The difference between
different implementations
of
the
same relational operation
are:
-
IO cost
(= number of disk IOs)
and
- Memory requirement
-
IO cost
(= number of disk IOs)
and
- Best algorithm for
a relational operator:
- Best algorithm for a relation operator = the algorithm that has the lowest IO cost and can operate within the given memory constraints