Slideshow:
Finding an optimal query plan
Review: the SQL parser first reads in an SQL query and converts it to an initial query plan:
Finding an optimal query plan
Then the initial query plan must be transformed into an optimal (= best) query plan:
The optimal query plan must be an equivalent query plan (= produce the same output set) but with "better" performance characteristics
Tools to find an optimal query plan:
algebraic laws
Example of an algebraic law:
Example applying Algebraic Law
Initial query plan:
Example applying Algebraic Law
Algebraic law: σstarName=name ( MovieStar × StarsIn ) ≡ MovieStar ⋈starName=name StarsIn
Example applying Algebraic Law
Algebraic law: σbirthdate LIKE '%1960' ( MovieStar ⋈ StarsIn ) ≡ MovieStar ⋈ σbirthdate LIKE '%1960'(StarsIn)
Example applying Algebraic Law
We want the smaller input relation as the left input relation in a join (⋈) operator:
Comment on Algebraic Law
- After
parsing a
SQL query to
an initial (logical) query plan:
we must transform the initial (logical) query plan to an optimal (logical) query plan:
- Fact:
- A logical query plan is an Relational Algebra expression
- Transforming
one (logical) query plan
to another
(logical) query plan is:
- Re-writing one Relational Algebra expression into another equivalent Relational Algebra expression
- Algebraic Laws:
- Algebraic law = an elementary equivalence of 2 (relational algebra) expressions
Algebraic laws will enable us to transform the initial (logical) query plan to an optimal (logical) query plan
- Comment on the
Algebraic law in general
- Most of the
Algebraic laws will
not be
(very) useful in
query optimization....
(I.e.: most of the laws do not reduce the running time of a query by much)
- Most of the
Algebraic laws will
not be
(very) useful in
query optimization....