Slideshow:
Not all Algebraic Laws are equally useful...
Some algebraic laws will not reduce the input size by much...
No all Algebraic Laws are equally useful...
Some algebraic laws will reduce the input size significantly...
Heuristic to find best logical query plan:
Apply only the highly cost reducing algebraic laws (instead of trying all algebraic laws)
The list of highly cost reducing
Algebraic Laws -
× elimination
The list of highly cost reducing
Algebraic Laws -
pushing σ inwards
The list of highly cost reducing
Algebraic Laws -
break up conjuntions
The list of highly cost reducing
Algebraic Laws -
remove unneeded attributes
Comment: the projection operation can be added to the Table/Index scan operation without incurring additional (or much) cost !
Heuristic-based logical query plan optimization
- Step 1
Heuristic-based logical query plan optimization
- Step 2
Heuristic-based logical query plan optimization
- Step 3
- Some
of the Algebraic Laws does
not reduce the
result set
much (if any)
- Examples:
δ( R ⋈ S )
= δ(R) ⋈
δ(S)
R= R(X,Y) S = S(Y,Z) R = {(a,1), (b,2), (c,2)} S = {(1,x), (2,y), (2,z)} δ( R ⋈ S ) = δ( {(a,1), (b,2), (c,2)} ⋈ {(1,x), (2,y), (2,z)} ) The ⋈ is a 3 tuples ⋈ 3 tuples operation
If we compute δ( R ⋈ S ) by using: δ( R ⋈ S ) = δ(R) ⋈ δ(S) = δ( (a,1), (b,2), (c,2) ) ⋈ δ( (1,x), (2,y), (2,z) ) = ( {(a,1), (b,2), (c,2)} ) ⋈ ( {(1,x), (2,y), (2,z)} ) The ⋈ is still a 3 tuples ⋈ 3 tuples operation There is no reduction/saving !!
- Examples:
δ( R ⋈ S )
= δ(R) ⋈
δ(S)
- Some of the
Algebraic Laws have
hugh reductions:
- Example:
σcond(
R ⋈ S ) =
σcond(R) ⋈ S
R= R(X,Y) S = S(Y,Z) R = {(a,1), (b,2), (c,2)} S = {(1,x), (2,y), (2,z)} σX=a( R ⋈ S ) = σX=a( {(a,1), (b,2), (c,2)} ⋈ {(1,x), (2,y), (2,z)} ) The ⋈ is a 3 tuples ⋈ 3 tuples operation
If we compute σX=a( R ⋈ S ) by using: σX=a( R ⋈ S ) = σX=a(R) ⋈ S = {(a,1)} ⋈ {(1,x), (2,y), (2,y)} The ⋈ is a 1 tuple ⋈ 3 tuples operation !!!
- Example:
σcond(
R ⋈ S ) =
σcond(R) ⋈ S
- The frequently used
Algebraic laws
in query optimization are:
-
σcond(
R × S ) = R ⋈cond S
Because:
- There are no
memory-efficient algorithms
for
R × S ---
see
click here
- We do have memory-efficient algorithms for R ⋈ S !!!
- There are no
memory-efficient algorithms
for
R × S ---
see
click here
-
σcond
( R ⋈ S ) =
σcond(R)
⋈ S
Because:
- The join operation
"R ⋈ S" uses
the entire relation
R as
input
- One the other hand,
the join operation
"σcond(R)
⋈ S" uses
a subset of R as
input
- There is significant saving in processing cost !!!
- The join operation
"R ⋈ S" uses
the entire relation
R as
input
-
σc1 and c2( R )
=
σc1(
σc2( R ) )
Same argument as for case (2) above (we can use a subset as input which result in big saving)
-
Add projection (π) between
relational operations to
remove the
unused attributes in the
upper level of the
query plan
Example:
-
σcond(
R × S ) = R ⋈cond S
- Heuristic used to
find
a optimal logical query plan:
- Break up compound
conjunctive (AND) conditions
Push select (σ) operations as far down the logical query tree as possible
Graphically:
- Convert
a selection + cartesian product
into a join:
- Add
projection (π)
operators to
remove the
non-essential attributes
from the intermediate results:
- Break up compound
conjunctive (AND) conditions