Slideshow:
Reviews: Steps of the query optimization process
(1) Query re-writing:
(We studied a heuristic in this webpage)
Reviews: Steps of the query optimization process
(2) Find the best join ordering:
(We studied a heuristic in this webpage)
Join ordering and join tree
Each join ordering corresponds uniquely with a join tree
Join ordering and join tree
- Example 1
Join ordering and join tree
- Example 2
So: finding the best join ordering is equivalent to finding the best join tree
- 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
- Fact:
- The join operator is
associative and
communitative
- It's just like the
add (+) operator:
4 + 7 + 6 + 3 = (4 + 6) + (3 + 7) = 20
We can re-order the sequence of operations
- It's just like the
add (+) operator:
- The join operator is
associative and
communitative
- In general:
- When there are ≥ 3 relations
in a join operations:
R ⋈ S ⋈ T ....we must find an order of join that yields the best performance
- When there are ≥ 3 relations
in a join operations:
- Note:
- Due to a asymmetry
in the implementation algorithms,
we will need to pick an
order even when there
are only 2 relations:
R ⋈ S <====> S ⋈ Rbecause the reunning time is better when the first relation is smaller in many implementations !!!
- Due to a asymmetry
in the implementation algorithms,
we will need to pick an
order even when there
are only 2 relations:
- Join tree:
- Join tree = a binary tree where the internal nodes are join operators and the leaf nodes are "relations"
- Examples:
- Join tree 1:
This tree represents this expression:
( ( R ⋈ S ) ⋈ T ) ⋈ U
- Join tree 2:
This tree represents this expression:
( R ⋈ S ) ⋈ ( T ⋈ U )
- Join tree 1:
- In general:
- A join tree will
represent a
unique ordering
of join operations
(We will use join trees to represent the join order explicitly)
- A join tree will
represent a
unique ordering
of join operations