Slideshow:
Review: uncorrelated sub-query
Rule for re-writing a
2-argument selection for
uncorrelated sub-queries
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 1
Query:
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 1
Query:
(1) Convert the inner query to a query plan:
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 1
Query:
(2) Convert the outer query using a 2-argument selection:
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 1
Query:
(3) Convert the 2-argument selection query using the above rule:
Looking ahead: further optimization
Query:
We can replace the selection + cartesian product by a join operation:
Looking ahead: further optimization
Query:
Because name is a key attribute, we can omit the δ operator:
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 2
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 2
Query:
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 1
Query:
(1) Convert the inner query to a query plan:
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 1
Query:
(2) Convert the outer query using a 2-argument selection:
Re-writing a
2-argument selection for
uncorrelated sub-queries
- Example 1
Query:
(3) Convert the 2-argument selection query using the above rule:
Looking ahead: further optimization
Query:
We can replace the selection + cartesian product by a join operation:
Note: the δ operator is necessary to suppress duplicates essn values !!!
Why the δ operator is
necessary
- Uncorrelated sub-query:
- Uncorrelated sub-query = a sub-query that can be executed independently without the outer query
- Example:
SELECT movieTitle FROM StarsIn WHERE starName IN (SELECT name // Sub-query FROM MovieStar WHERE birthday LIKE '%1960')The sub-query is uncorrelated because we can execute the sub-query without the outer query:
SELECT name FROM MovieStar WHERE birthday LIKE '%1960'That is because the sub-query does not use any attribute in the outer query !!!
- Re-write rule
for un-correlated sub-query:
Comment:
- The
2-argument
σ*
is transformed into
σ(
arg1 × δ(arg2) )
(We use 2 input relations in σ !!!)
- The duplicate elimination (δ)
is needed to
suppress
duplicate output values
- The IN operator is
equivalent to
= ANY in
SQL
- The = compare operator is inherited by the transformation from σ* into σ( arg1 × δ(arg2) )
- The <R-cond>
(A IN ... or A = ANY ...) must be
above the
relation R because:
- The <R-cond> uses some attribute (A !!!) from relation R !!!
- The meaning of
transformation is:
- An attribute value R.A
∈ (= ANY)
(set of values in sub-query)
iff
- When we pair up R.A with every value in the sub-query (by means of a cartesian product), there is a value that is equal to R.A
- An attribute value R.A
∈ (= ANY)
(set of values in sub-query)
iff
- The
2-argument
σ*
is transformed into
σ(
arg1 × δ(arg2) )
- SQL query:
(Find movie title of movies with a star born in the year 1969) SELECT movieTitle FROM StarsIn WHERE starName IN (SELECT name // Uncorrelated Sub-query FROM MovieStar WHERE birthday LIKE '%1960')
- First, convert the
inner query to
a logical query plan (tree):
- Then,
convert the
outer query
to a logical query plan
using a
2-argument selection:
- Re-write the
2-argument selection using the
re-write rule:
This is the initial query plan for the query
- We can use the Algebraic Laws
to
optimize the
initial query plan:
- Re-write
the σstarName = name
and the ×
as a
join ⋈:
- Because name is a
key in
the relation
MovieStar,
the δ (dupl. elim.) is
useless:
We will study query optimization more in dept later.
- Re-write
the σstarName = name
and the ×
as a
join ⋈:
- Relations:
Employee: ssn fname lname --------- ------ -------- 123456789 John Smith 333445555 Frankl Wong 999887777 Alicia Zelaya 987654321 Jennif Wallace 666884444 Ramesh Narayan Dependent: essn name relationship --------- ---------- ------------ 333445555 Alice DAUGHTER 333445555 Joy SPOUSE 987654321 Abner SPOUSE 123456789 Micheal SON 123456789 Elizabeth SPOUSE - Query:
- Find fname, lname of employees who has a dependent
- SQL query:
SELECT fname, lname FROM employee WHERE ssn IN (SELECT essn // Uncorrelated Sub-query FROM dependent )
- The inner query is:
- The logical query plan
using a
2-argument selection is:
- Re-write the
2-argument selection using the
re-write rule:
This is the initial query plan for the query
- We can use the Algebraic Laws to
simplify/optimize the
initial query plan
into the following:
- Re-write
the σstarName = name
and the ×
as a:
join ⋈:
- Re-write
the σstarName = name
and the ×
as a:
join ⋈:
- Note:
- Because essn is a
not
a key in
the relation
dependent:
- The δ (dupl. elim.) is
necessary !!!
- The δ (dupl. elim.) is
necessary !!!
- Example:
suppose we
remove the
δ operator:
Without the duplicate removal (δ) operation, we will output John Smith multiple times !!!
- Because essn is a
not
a key in
the relation
dependent: