cs411-09-queryoptimization - CS411 Database Systems 09:...

Info iconThis preview shows pages 1–20. Sign up to view the full content.

View Full Document Right Arrow Icon
1 CS411 Database Systems 09: Query Optimization
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Why Do We Learn This?
Background image of page 2
3 Optimization • At the heart of the database engine • Step 1: convert the SQL query to some logical plan • Step 2: find a better logical plan, find an associated physical plan
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 SQL –> Logical Query Plans
Background image of page 4
5 Converting from SQL to Logical Plans Select a1, …, an From R1, …, Rk Where C a1,…,an ( C (R1 x R2 x … x Rk)) a1,…,an ( b1, …, bm, aggs ( C (R1 x R2 x x Rk))) Select a1, …, an From R1, …, Rk Where C Group by b1, …, bm
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
6 Optimization: Logical Query Plan • Now we have one logical plan • Algebraic laws: – foundation for every optimization • Two approaches to optimizations: – Rule-based (heuristics): apply laws that seem to result in cheaper plans – Cost-based: estimate size and cost of intermediate results, search systematically for best plan
Background image of page 6
7 Motivating Example Select S.name, C.instructor From Students S, Enrollment E, Course C Where S.dept = ‘CS’ and S.sid=E.sid and E.cid = C.cid
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
8 The three components of an optimizer We need three things in an optimizer: • Algebraic laws • An optimization algorithm • A cost estimator
Background image of page 8
9 Algebraic Laws
Background image of page 9

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
10 Algebraic Laws • Commutative and Associative Laws –R S = S R, R (S T) = (R S) T S = S R, R (S T) = (R S) T S = S R, R (S T) = (R S) T • Distributive Laws (S T) = (R S) (R T) Q : How to prove these laws? Make sense?
Background image of page 10
11 Algebraic Laws • Laws involving selection: C AND C’ (R) = C ( C’ (R)) = C (R) C’ (R) C OR C’ (R) = C (R) U C’ (R) C (R S) = C (R) S • When C involves only attributes of R C (R – S) = C (R) – S C (R S) = C (R)  C (S) C (R S) = C (R) S Q : What do they mean? Make sense?
Background image of page 11

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
12 Algebraic Laws • Example: R(A, B, C, D), S(E, F, G) F=3 (R D=E S) = ? A=5 AND G=9 (R D=E S) = ?
Background image of page 12
13 Algebraic Laws • Laws involving projections M (R S) = N ( P (R) Q (S)) • Where N, P, Q are appropriate subsets of attributes of M M ( N (R)) = M N (R) • Example R(A,B,C,D), S(E, F, G) A,B,G (R D=E S) = ? ( ? (R) ? (S)) Q : Again, what do they mean? Make sense?
Background image of page 13

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
14 Behind the Scene: Oracle RBO and CBO • Oracle 7 (1992) prior (since 1979): RBO. • Oracle 7-10: RBO + CBO. • Oracle 10g (2003): CBO.
Background image of page 14
15 Behind the Scene: Oracle RBO and CBO
Background image of page 15

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon