Slideshow:
Equivalence of logical query plans
Query plan A ≡ query plan B if and only if they always compute the same result set
Difference between
equivalent query plans
The optimal (lowest cost) logical query plan
The optimal (lowest cost) logical query plan
- The Algebraic Laws that
we studied in the
previous chapter transform
a relation algebra expression into
another
equivalent
relation algebra expression
Example:
- These 2 relational algebra expressions
computes the
same result set:
(1) πfname,lname(σssn=essn(employee×δ(πessn(dependent)))) (2) πfname,lname(employee⋈ssn=essnδ(πessn(dependent)))
Graphically:
- These 2 relational algebra expressions
computes the
same result set:
- Althought,
both algebraic expressions are
equivalent, the
expression:
(2) πfname,lname(employee⋈ssn=essnδ(πessn(dependent)))
will produce a (much) smaller number of tuples as intermediate result set(s):
Furthermore, there exists (space and time) efficient algorithms for join (⋈).
(The only viable algorithm to compute the cartesian product (×) on a large input relation is the nested loop algorithm which is not time efficient)
- Optimal
logical query plan:
- The optimal (logical) query plan for an initial (logical) query plan = an equivalent relation algebra expression for the initial (logical) query plan where the intermediate result sets have the least number of tuples
- The bad news on
finding the optimal query plan:
- The problem of
finding the optimal query plan is
NP-complete
Wikipedia: List of NP-complete problems
Look for: Boolean conjunctive query
The fact that query optimization is NP-complete means that we do not have (right now) any efficient way to find a solution (= optimal query plan)
- But we still want
(need ?) to
solve the problem !!!
(I.e.: life must continue !)
For practical purposes, we try to find a near optimal query plan using some heuristics
- The problem of
finding the optimal query plan is
NP-complete