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

Info iconThis preview shows pages 1–19. 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? • Is relational model possible? – Relational algebra is way too complicated • SQL, a declarative language, is good, but it is implementable? efficient? How to support efficient query processing
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 P a1,…,an ( s C (R1 x R2 x … x Rk)) P a1,…,an ( g b1, …, bm, aggs ( s 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 – R ∩ S = S ∩ R, R ∩ (S ∩ T) = (R ∩ S) ∩ T – R S = S R, R (S T) = (R S) T • Distributive Laws – R (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: s C AND C’ (R) = s C ( s C’ (R)) = s C (R) ∩ s C’ (R) s C OR C’ (R) = s C (R) U s C’ (R) s C (R S) = s C (R) S • When C involves only attributes of R s C (R – S) = s C (R) – S s C (R S) = s C (R) s C (S) s C (R ∩ S) = 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) s F=3 (R D=E S) = ? s A=5 AND G=9 (R D=E S) = ?
Background image of page 12
13 Algebraic Laws • Laws involving projections P M (R S) = P N ( P P (R) P Q (S)) • Where N, P, Q are appropriate subsets of attributes of M P M ( P N (R)) = P M N (R) • Example R(A,B,C,D), S(E, F, G) P A,B,G (R D=E S) = P ? ( P ? (R) P ? (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
16 Rule-based Optimization
Background image of page 16
17 Heuristic Based Optimizations • Query rewriting based on algebraic laws • Result in better queries most of the time • Heuristics number 1: – Push selections down • Heuristics number 2: – Sometimes push selections up, then down
Background image of page 17

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

View Full DocumentRight Arrow Icon
18 Predicate Pushdown Product Company maker=name s price>100 AND city=“Urbana” pname Product Company maker=name price>100 pname city=“Urbana” The earlier we process selections, less tuples we need to manipulate higher up in the tree (but may cause us to loose an important ordering of the tuples, if we use indexes).
Background image of page 18
Image of page 19
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/17/2012 for the course CS 411 taught by Professor Winslett during the Fall '07 term at University of Illinois at Urbana–Champaign.

Page1 / 71

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

This preview shows document pages 1 - 19. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online