Download Slides on Query Optimization - Advanced Database Systems | EECS 584 and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
10/6/08 1
Query Optimization
Based on:
Selinger et al. Access Path Selection in a
Relational Database Management System, 1979
Chaudhuri. An Overview of Query Optimization
in Relational Systems, 1998
Presentation by: Kristen LeFevre
10/6/08 2
Relational Query Processing
Two Key Components:
Query Execution Engine
Implements a set of physical operators
Physical operators combine to form a query
execution plan
Query Optimizer
Generates the input for the execution engine
Chooses a good plan!
10/6/08 3
Query Execution Plan
Operator Interface: open(), getNext(),
close()
Intermediate Results (multiple ops):
Pipelined: Tuples resulting from one operator
fed directly into the next
Materialized: Create a temporary table to store
intermediate results
10/6/08 4
Example
Sort Clustered Index Scan B
Table Scan A
Merge-Join
(A.x=B.x)
Index Scan C
Index Nested Loop
(A.x=C.x)
Often many different plans that produce the same result!! 10/6/08 5
Example -- Alternative
Table Scan A Table Scan C
Nested Loop
(A.x=C.x)
Table Scan B
Nested Loop
(C.x=B.x)
10/6/08 6
Query Optimizer
Given parsed representation of SQL query,
produce an efficient execution plan
Key aspects of optimizer:
Search space (Which plans do we consider?)
Cost estimation technique to evaluate each
plan in the search space
Plan enumeration algorithm
10/6/08 10
System R Statistics
(Stored in the system catalogs)
TCARD(T) : # pages in table T
NCARD(T) : # tuples in table T
P(T) : holdover from when a page could
hold tuples from different relations
ICARD(I) : # distinct keys in index
NINDX(I) : # pages in index
10/6/08 11
Step 1: Selectivity Estimation
Using statistics, assign selectivity factor F for
each boolean predicate
Very, very rough estimate!
attr = val
F = 1/ICARD(I) if suitable index exists
1/10 otherwise
val1 < attr < val
F = (val2-val1) / (highkey - lowkey) if index exists
1/4 otherwise
exp1 AND exp
F(exp1) * F(exp2)
exp1 OR exp
F(exp1) + F(exp2) - F(exp1) * F(exp2)
10/6/08 12
Alternatives
Histograms over a single column
Multidimensional histograms
Sampling
Age
Frequency
Came about after System R
10/6/08 13 Step 2: Single-Table Cost Estimation For each relation, calculate the cost of scanning the relation using each suitable index and table scan Cost = #I/Os + W * RSI Calls
tuples RSS returns
Example: SELECT * FROM Employees WHERE Name = ‘Bob’ AND Salary = 50000 Assume: Clustered index on Name Unclustered index on Salary
Three Alternatives:
(estimate cost of each)
1. Use Name index
2. Use Salary index
3. Table scan
10/6/08 14 Step 3: Join Method and Ordering System R: Only consider left-deep join trees Used to restrict the search space Left-deep plans can be fully pipelined. Intermediate results not written to temporary files. Not all left-deep trees are fully pipelined (e.g., SM join). A^ B C D A B C D A B C D outer (^) inner
Linear Tree: at least 1 child in every join node is a base relation
10/6/08 15 Enumeration of Left-Deep Plans
Decide:
Join order Join method for each join
Enumerated using N passes (if N relations joined):
Pass 1: Find best 1-relation plan for each relation. Pass 2: Find best way to join result of each 1-relation plan (as outer) to another relation. (All 2-relation plans.) Pass N: Find best way to join result of a (N-1)-relation plan (as outer) to the N’th relation. (All N-relation plans.)
For each subset of relations, retain only:
Cheapest plan overall, plus Cheapest plan for each interesting order of the tuples.
