A Typical Query Optimizer
151
(e)
Equivalent maximally pushed form:
σ
c
1
(
π
l
1
(
π
l
2
(
σ
c
2
(
R
))
×
S
)).
(f)
Equivalent maximally pushed form:
π
l
(
σ
c
1
(
π
l
1
(
π
l
2
(
σ
c
2
(
R
))
×
S
))).
Answer 15.6
Answer omitted.
Exercise 15.7
Consider the following relational schema and SQL query. The schema
captures information about employees, departments, and company finances (organized
on a per department basis).
Emp(
eid:
integer
,
did:
integer
,
sal:
integer
,
hobby:
char(20)
)
Dept(
did:
integer
,
dname:
char(20)
,
ﬂoor:
integer
,
phone:
char(10)
)
Finance(
did:
integer
,
budget:
real
,
sales:
real
,
expenses:
real
)
Consider the following query:
SELECT
D.dname, F.budget
FROM
Emp E, Dept D, Finance F
WHERE
E.did=D.did
AND
D.did=F.did
AND
D.ﬂoor=1
AND
E.sal
≥
59000
AND
E.hobby = ‘yodeling’
1. Identify a relational algebra tree (or a relational algebra expression if you prefer)
that reﬂects the order of operations a decent query optimizer would choose.
2. List the join orders (i.e., orders in which pairs of relations can be joined to compute
the query result) that a relational query optimizer will consider.
(Assume that
the optimizer follows the heuristic of never considering plans that require the
computation of crossproducts.) Brieﬂy explain how you arrived at your list.
3. Suppose that the following additional information is available: Unclustered B+
tree indexes exist on
Emp.did
,
Emp.sal
,
Dept.ﬂoor
,
Dept.did
, and
Finance.did
.
The system’s statistics indicate that employee salaries range from 10,000 to 60,000,
employees enjoy 200 different hobbies, and the company owns two ﬂoors in the
building. There are a total of 50,000 employees and 5,000 departments (each with
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
This is the end of the preview.
Sign up
to
access the rest of the document.
 Fall '12
 Dr.Vishak
 Relational model, Query optimizer, emp, Typical Query Optimizer

Click to edit the document details