08_qp_opt

08_qp_opt - CPS216: Data-intensive Computing Systems Query...

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

View Full Document Right Arrow Icon
CPS216: Data-intensive Computing Systems Query Optimization (Cost- based optimization) Shivnath Babu
Background image of page 1

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

View Full DocumentRight Arrow Icon
Query Optimization Problem Pick the best plan from the space of physical plans
Background image of page 2
Cost-Based Optimization • Prune the space of plans using heuristics • Estimate cost for remaining plans – Be smart about how you iterate through plans • Pick the plan with least cost Focus on queries with joins
Background image of page 3

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

View Full DocumentRight Arrow Icon
Heuristics for pruning plan space • Predicates as early as possible • Avoid plans with cross products • Only left-deep join trees
Background image of page 4
Physical Plan Selection Logical Query Plan P1 P2 …. Pn C1 C2 …. Cn Pick minimum cost one Physical plans Costs
Background image of page 5

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

View Full DocumentRight Arrow Icon
Review of Notation • T (R) : Number of tuples in R • B (R) : Number of blocks in R
Background image of page 6
Simple Cost Model Cost (R S) = T(R) + T(S) All other operators have 0 cost Note: The simple cost model used for illustration only
Background image of page 7

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

View Full DocumentRight Arrow Icon
Cost Model Example R S T X T(R) + T(S) T(X) + T(T) Total Cost: T(R) + T(S) + T(T) + T(X)
Background image of page 8
Selinger Algorithm Dynamic Programming based • Dynamic Programming: – General algorithmic paradigm – Exploits “principle of optimality” – Useful reading: • Chapter 16, Introduction to Algorithms, Cormen, Leiserson, Rivest
Background image of page 9

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

View Full DocumentRight Arrow Icon
Optimal for “whole” made up from optimal for “parts”
Background image of page 10
Image of page 11
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 41

08_qp_opt - CPS216: Data-intensive Computing Systems Query...

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

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