This preview shows pages 1–3. Sign up to view the full content.
1
Query Optimization
CPS 116
Introduction to Database Systems
Announcements (Thu. Dec. 1)
±
Extra credit (20 points) due next Tuesday
±
Homework #4 deadline extended—due next
Thursday (Dec. 8)
±
Sign up (via email) for a 30minute slot in the
project demo period, Dec. 1214
²
Two “public” demo slots available right after final exam
±
Final exam 24pm Dec. 13
²
Open book, open notes
²
Focus on the second half of the course
²
Sample final available
2
3
Query optimization
±
One logical plan
!
“best” physical plan
±
Questions
²
How to enumerate possible plans
²
How to estimate costs
²
How to pick the “best” one
±
Often the goal is not getting the optimum plan, but
instead avoiding the horrible ones
1 second
1 hour
1 minute
Any of these will do
4
Plan enumeration in relational algebra
±
Apply relational algebra equivalences
)
Join reordering:
£
and
!
are associative and
commutative (except column ordering, but that is
unimportant)
!
!
R
S
T
!
!
S
R
T
!
!
R
T
S
…
===
5
More relational algebra equivalences
±
Convert
¾
p

£
to/from
!
p
:
¾
p
(
R
£
S
) =
R
!
p
S
±
Merge/split
¾
’s:
¾
p
1
(
¾
p
2
R
) =
¾
p
1
A
p
2
R
±
Merge/split
¼
’s:
¼
L
1
(
¼
L
2
R
) =
¼
L
1
R
, where
L
1
μ
L
2
±
Push down/pull up
¾
:
¾
p
A
pr
A
ps
(
R
!
p’
S
) = (
¾
pr
R
)
!
p
A
p’
(
¾
ps
S
), where
²
pr
is a predicate involving only
R
columns
²
ps
is a predicate involving only
S
columns
²
p
and
p’
are predicates involving both
R
and
S
columns
±
Push down
¼
:
¼
L
(
¾
p
R
) =
¼
L
(
¾
p
(
¼
L L’
R
)), where
²
L’
is the set of columns referenced by
p
that are not in
L
±
Many more (seemingly trivial) equivalences…
²
Can be systematically used to transform a plan to new ones
6
Relational query rewrite example
¼
title
¾
Student
.
name
=“Bart”
A
Student
.
SID
=
Enroll
.
SID
A
Enroll
.
CID
=
Course.CID
£
Enroll
Course
£
Student
¼
title
¾
Enroll
.
CID
=
Course.CID
£
Enroll
Course
£
Student
¾
Student
.
SID
=
Enroll.SID
¾
Student
.
name
= “Bart”
Push down
¾
¼
title
!
Enroll
.
CID
=
Course
.
CID
Enroll
Course
Student
!
Student
.
SID
=
Enroll
.
SID
¾
name
= “Bart”
Convert
¾
p

£
to
!
p
This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document 2
7
Heuristicsbased query optimization
±
Start with a logical plan
±
Push selections/projections down as much as possible
²
Why? Reduce the size of intermediate results
²
Why not? May be expensive; maybe joins filter better
±
Join smaller relations first, and avoid cross product
²
Why? Reduce the size of intermediate results
²
Why not? Size depends on join selectivity too
±
Convert the transformed logical plan to a physical
plan (by choosing appropriate physical operators)
8
SQL query rewrite
±
More complicated—subqueries and views divide a
query into nested “blocks”
²
Processing each block separately forces particular join
methods and join order
²
Even if the plan is optimal for each block, it may not be
This is the end of the preview. Sign up
to
access the rest of the document.
This document was uploaded on 01/17/2012.
 Spring '09

Click to edit the document details