- Facts:
- Many of the Algebraic Laws does
not reduce the
result set by
a significant number
Example:
If tuples in R and S are unqiue (= no duplicates), then size(δ(R)) = size(R)
So:
- Evaulating the cost of different algebraic expressions can time consuming and have little benefit
- Many of the Algebraic Laws does
not reduce the
result set by
a significant number
- The most useful
Algebraic laws are:
(1) σcond( R × S ) = R ⋈cond S if cond contains attributes from R and S // This rule will allow use to // switch over to a join algorithm (2) σcond( R ⋈ S ) = σcond(R) ⋈ S if cond only contains attributes from R // This rule will reduce // the result set significantly (3) σc1 and c2( R ) = σc1( σc2( R ) ) // This rule allow us to use // use rule (2) in more situations
- Common practice in
logical query plan optimization:
- Database systems often (almost always) use heuristics to find the best logical query plan
- Heuristics:
- Break up compound
conjunctive conditions
in SELECT
into cascaded select operations
Then push individual select operations as far down the logical query tree as possible
Graphically:
- Convert
a selection + cartsian product
into a join:
- Add projection (π) operators to remove the non-essential attributes from the intermediate results
- Break up compound
conjunctive conditions
in SELECT
into cascaded select operations
- Example:
- SQL query:
SELECT lname FROM employee, works_on, project WHERE pname = 'Aquarius' and pnumber = pno and essn = ssn and bdate > '1957-12-31'
- Initial
logical query plan:
- Break up
select condition and
move down
as far as
possible:
- Replace
σ + × with
⋈:
Result:
Notice:
- The join has
3 input relations
- The join operation is
both
associative and
communitative
Therefore:
- We can re-order the join operations
Then we will proceed with next step which is to find the best join ordering.
- The join has
3 input relations
- SQL query:
- Comment:
- We can optionally add projection operators to reduce the intermediate result size