08_qp_opt

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

This preview shows pages 1–14. Sign up to view the full content.

CPS216: Data-intensive Computing Systems Query Optimization (Cost- based optimization) Shivnath Babu

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

View Full Document
Query Optimization Problem Pick the best plan from the space of physical plans
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

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

View Full Document
Heuristics for pruning plan space Predicates as early as possible Avoid plans with cross products Only left-deep join trees
Physical Plan Selection Logical Query Plan P1 P2 …. Pn C1 C2 …. Cn Pick minimum cost one Physical plans Costs

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

View Full Document
Review of Notation T (R) : Number of tuples in R B (R) : Number of blocks in R
Simple Cost Model Cost (R S) = T(R) + T(S) Cost (R S) = T(R) + T(S) All other operators have 0 cost Note: The simple cost model used for illustration only

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

View Full Document
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)
Selinger Algorithm Dynamic Programming based Dynamic Programming: General algorithmic paradigm Exploits “principle of optimality” Useful reading: Chapter 16, Introduction to Algorithms, Cormen, Leiserson, Rivest

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

View Full Document
Principle of Optimality Optimal for “whole” made up from optimal for “parts”
Principle of Optimality Query: R1 R2 R3 R4 R5 R3 R2 R4 R1 R5 Optimal Plan:

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

View Full Document
Principle of Optimality Query: R1 R2 R3 R4 R5 R3 R2 R4 R1 R5 Optimal Plan: Optimal plan for joining R3, R2, R4, R1
Principle of Optimality Query: R1 R2 R3 R4 R5 R3 R2 R4 R1 R5 Optimal Plan: Optimal plan for joining R3, R2, R4

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

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

## This document was uploaded on 01/17/2012.

### Page1 / 41

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

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

View Full Document
Ask a homework question - tutors are online