Query Optimization

Imperative query execution plan:

Declarative SQL query

Ideally: Want to find best plan. Practically: Avoid worst plans!

Goal:

Purchase

Person

Buyer=name

City=‘seattle’

phone>’5430000’

buyer

(Simple Nested Loops)

?

(Table scan)

(Index scan)

SELECT S.buyer

FROM Purchase P, Person Q

WHERE P.buyer=Q.name AND

Q.city=‘seattle’ AND

Q.phone > ‘5430000’

Inputs:

• the query

• statistics about the data (indexes, cardinalities, selectivity factors)

• available memory

Next slide

Back to first slide

View graphic version