�ݺ�ߣ

DBMS 2001 Notes 6: Query Compilation 1
Principles of Database
Management Systems
6: Query Compilation and
Optimization
Pekka Kilpeläinen
(partially based on Stanford CS245 slide
originals by Hector Garcia-Molina, Jeff Ullman
and Jennifer Widom)

Overview
• We have studied recently:
– Algorithms for selections and joins, and their costs
(in terms of disk I/O)
• Next: A closer look at query compilation
– Parsing
– Algebraic optimization of logical query plans
– Estimating sizes of intermediate results
• Focus still on selections and joins
• Remember the overall process of query
execution:

{P1,P2,…..}
{P1,C1>...}
parse
convert
apply laws
estimate result sizes
consider physical plans estimate costs
pick best
execute
Pi
answer
SQL query
parse tree
logical query plan
“improved” l.q.p
l.q.p. +sizes
statistics

Step 1: Parsing
• Check syntactic correctness of the query, and
transform it into a parse tree
– Based on the formal grammar for SQL
– Inner nodes nonterminal symbols (syntactic
categories for things like <Query>, <RelName>, or
<Condition>)
– Leaves terminal symbols: names of relations or
attributes, keywords (SELECT, FROM, …),
operators (+, AND, OR, LIKE, …), operands (10,
'%1960', …)
– Also check semantic correctness: Relations and
attributes exist, operands compatible with
operators, …

Example: SQL query
Consider querying a movie database with relations
StarsIn(title, year, starName)
MovieStar(name, address, gender, birthdate)
SELECT title
FROM StarsIn, MovieStar
WHERE starName = name AND birthdate LIKE ‘%1960’ ;
(Find the titles for movies with stars born in 1960)

Example: Parse Tree
<Query>
<SFW>
SELECT <SelList> FROM <FromList> WHERE <Condition>
<Attribute> <RelName> , <FromList> AND
title StarsIn <RelName>
<Condition> <Condition>
<Attribute> = <Attribute> <Attribute> LIKE <Pattern>
starName name birthdate ‘%1960’
MovieStar

Step 2:
Parse Tree −> Logical Query
Plan• Basic strategy:
SELECT A, B, C
FROM R1, R2
WHERE Cond ;
becomes
πA,B,C[σCond(R1 x R2)]

Example: Logical Query Plan
Πtitle
σstarName=name and birthdate LIKE ‘%1960’
StarsIn MovieStar
×

Step 3: Improving the L.Q.P
• Transform the logical query plan into an
equivalent form expected to lead to
better performance
– Based on laws of relational algebra
– Normal to produce a single optimized form,
which acts as input for the generation of
physical query plans

Example: Improved Logical Query Plan
Πtitle
starName=name
StarsIn
σbirthdate LIKE ‘%1960’
MovieStar

Step 4: Estimate Result Sizes
• Cost estimates of database algorithms
depend on sizes of input relations
−> Need to estimate sizes of
intermediate results
– Estimates based on statistics about
relations, gathered periodically or
incrementally

Example: Estimate Result Sizes
Need expected size
StarsIn
MovieStar
σ

Steps 5, 6, ...
• Generate and compare query plans
– generate alternate query plans P1, …, Pk by
selecting algorithms and execution orders
for relational operations
– Estimate the cost of each plan
– Choose the plan Pi estimated to be "best"
• Execute plan Pi and return its result

Example: One Physical Plan
Parameters: join order,
memory size, project attributes,...
Hash join
SEQ scan index scan Parameters:
Select Condition,...
StarsIn MovieStar

Next: Closer look at ...
• Transformation rules
• Estimating result sizes

Relational algebra optimization
• Transformation rules
(preserve equivalence)
• What are good transformations?

Rules: Natural joins
R S = S R (commutative)
(R S) T = R (S T) (associative)
• Carry attribute names in results, so order
is not important
−> Can evaluate in any order

(R x S) x T = R x (S x T)
R x S = S x R
R U (S U T) = (R U S) U T
R U S = S U R
Rules:
Cross products & union similarly
(both associative & commutative):

Rules: Selects
1. σp1∧p2(R) =
2. σp1vp2(R) =
σp1 [ σp2 (R)]
[ σp1 (R)] U [ σp2 (R)]
1. Especially useful (applied left-to-right):
Allows compound select conditions to be
split and moved to suitable positions
(See next)
NB: ∧ = AND; v = OR

Rules: Products to Joins
Definition of Natural Join:
R S = πL[σC(R x S)] ;
Condition C equates attributes common to R
and S, and πL projects one copy of them out
Applied right-to-left
- definition of general join applied similarly

Let p = predicate with only R attribs
q = predicate with only S attribs
m = predicate with attribs common to R,S
σp (R S) =
σq (R S) =
Rules: σ + combined
[σp (R)] S
R [σq (S)]
More rules can be derived...

σp∧q (R S) = [σp (R)] [σq (S)]
σp∧q∧m (R S) =
σm [(σp R) (σq S)]
σpvq (R S) =
[(σp R) S] U [R (σq S)]
Derived rules for σ +

Derivation for first one;
Others for homework:
σp∧q (R S) =
σp [σq (R S) ] =
σp [(R σq (S) ] =
[σp (R)] [σq (S)]

Rules for σ, π combined with X
similar...
e.g., σp (R X S) = ?

σp1∧p2 (R) → σp1 [σp2 (R)]
σp (R S) → [σp (R)] S
R S → S R
Some “good” transformations:
• No transformation is always good
• Usually good: early selections

Outline - Query Processing
• Relational algebra level
– transformations
– good transformations
• Detailed query plan level
– estimate costs
– generate and compare plans
next

• Estimating cost of query plan
(1) Estimating size of intermediate results
−>
(2) Estimating # of I/Os
(considered last week)

Estimating result size
• Maintain statistics for relation R
– T(R) : # tuples in R
– L(R) : Length of rows,
# of bytes in each R tuple
– B(R): # of blocks to hold all R tuples
– V(R, A) :
# distinct values in R for attribute A

Example
R A: 20 byte string
B: 4 byte integer
C: 8 byte date
D: 5 byte string
A B C D
cat 1 10 a
cat 1 20 b
dog 1 30 a
dog 1 40 c
bat 1 50 d
T(R) = 5 L(R) = 37
V(R,A) = 3 V(R,C) = 5
V(R,B) = 1 V(R,D) = 4

Size estimates for product W = R1xR2
T(W) =
L(W) =
T(R1) × T(R2)
L(R1) + L(R2)

L(W) = L(R)
T(W) = ?
Estimates for selection W = σA=a (R)
= AVG number of tuples that satisfy
an equality condition on R.A
= …

Example
R V(R,A)=3
V(R,B)=1
V(R,C)=5
V(R,D)=4
AVG size of, say, σA=val(R)?
T(σA=cat(R))=2, T(σA=dog(R))=2, T(σA=bat(R))=1
=> (2+2+1)/3 = 5/3
A B C D
cat 1 10 a
cat 1 20 b
dog 1 30 a
dog 1 40 c
bat 1 50 d

Size of W = σZ=a(R) in general:
Assume: Only existing Z values a1, a2, …
are used in a select expression Z= ai,
each with equal probalility 1/V(R,Z)
=> the expected size of σZ=val(R) is
E(T(W)) = Σi 1/V(R,Z) * T(σZ=a
i (R))
= T(R)/V(R,Z)

What about selection W=σz ≥ val (R) ?
T(W) = ?
• Estimate 1: T(W) = T(R)/2
– Rationale: On avg, 1/2 of tuples satisfy the condition
• Estimate 2: T(W) = T(R)/3
– Rationale: Acknowledges the tendency of
selecting "interesting" (e.g., rare tuples) more
frequently
<, ≤ and < similary

Size estimates for W = R1 R2
Let X = attributes of R1
Y = attributes of R2
X ∩ Y = ∅
Same as R1 x R2
Case 1

W = R1 R2 X ∩ Y = A
R1 A B C R2 A D
Case 2
Assumption:
V(R1,A) ≤ V(R2,A) ⇒ πA(R1) ⊆ πA(R2)
V(R2,A) ≤ V(R1,A) ⇒ πA(R2) ⊆ πA(R1)
“Containment of value sets” [Sec. 7.4.4]

Why should “containment of value sets” hold?
Consider joining relations
Faculties(FName, …) and
Depts(DName, FName, …)
where Depts.FName is a foreign key, and
faculties can have 0,…,n departments.
Now V(Depts, FName) ≤ V(Faculties,
FName), and referential integrity requires that
πFName(Depts) ⊆ πFName(Faculties)

R1 A B C R2 A D
a
Estimating T(W) when V(R1,A) ≤ V(R2,A)
Take
1 tuple
Match
Each estimated to match with T(R2)/V(R2,A)
tuples ...
so T(W) = T(R1)×T(R2)/V(R2, A)

• V(R1,A) ≤ V(R2,A): T(W) = T(R1)T(R2)/V(R2,A)
• V(R2,A) ≤ V(R1,A): T(W) = T(R2)T(R1)/V(R1,A)
[A is the join attribute]
=> in general:
T(W) = T(R1)T(R2)/max{V(R1,A), V(R2,A) }

With similar ideas, can estimate sizes of:
• W = ΠAB (R) ….. [Sec. 7.4.2]
• W = σA=a∧B=b (R) = σA=a (σB=b (R));
Ass. A and B independent =>
T(W) = T(R)/(V(R, A) x V(R, B))
• W=R(A,X,Y) S(X,Y,B); [Sec. 7.4.5]
Ass. X and Y independent => …
T(W) = T(R)T(S)/(max{V(R,X), V(S,X)} x
max{V(R,Y), V(S,Y)})
• Union, intersection, diff, …. [Sec. 7.4.7]

Note: for complex expressions, need
intermediate estimates.
E.g. W = [σA=a (R1) ] R2
Treat as relation U
T(U) = T(R1)/V(R1,A) L(U) = L(R1)
Also need an estimate for V(U, Ai) !

To estimate V (U, Ai)
E.g., U = σA=a (R)
Say R has attributes A,B,C,D
V(U, A) = ?
V(U, B) = ?
V(U, C) = ?
V(U, D) = ?

Example
R V(R,A)=3
V(R,B)=1
V(R,C)=T(R)=5
V(R,D)=3
U = σA=x (R)
A B C D
cat 1 10 10
cat 1 20 20
dog 1 30 10
dog 1 40 30
bat 1 50 10
V(U, D) = V(R,D)/T(R,A) ... V(R,D)
V(U,A) =1 V(U,B) =1 V(U,C) = T(R)
V(R,A)

V(U,Ai) for Joins U = R1(A,B) R2(A,C)
V(U,A) = min { V(R1, A), V(R2, A) }
V(U,B) = V(R1, B)
V(U,C) = V(R2, C)
[Assumption called
“preservation of value sets”, sect. 7.4.4]
Values of non-join
attributes preserved

Example:
Z = R1(A,B) R2(B,C) R3(C,D)
• R1: T(R1) = 1000 V(R1,A)=50 V(R1,B)=100
• R2: T(R2) = 2000 V(R2,B)=200 V(R2,C)=300
• R3: T(R3) = 3000 V(R3,C)=90 V(R3,D)=500

T(U) = T(R1) ×T(R2)/max{V(R1,B), V(R2,B)}
= 1000×2000/200 = 10,000;
V(U,A) = V(R1,A) = 50
V(U,C) = V(R2,C) = 300
V(U,B) = min{V(R1,B), V(R2,B)}= 100
Intermediate Result:
U(A,B,C)= R1(A,B) R2(B,C)

Z = U(A,B,C) R3(C,D)
T(Z) = T(U)×T(R3)/ max{V(U,C), V(R3,C)}
= 10,000×3000/300
= 100,000
V(Z,A) = V(U,A) = 50
V(Z,B) = V(U,B) = 100
V(Z,C) = min{V(U,C), V(R3,C)} = 90
V(Z,D) = V(R3,D) =500

Outline/Summary
• Estimating cost of query plan
– Estimating size of results done!
– Estimating # of IOs last week
• Generate and compare plans
– skip this
• Execute physical operations of query plan
– Sketched last week

�ݺ�ߣ

Compilation

Recommended

More Related Content

What's hot (9)

Viewers also liked (15)

Similar to Compilation (20)

Compilation