Home > DBA, Oracle, SQL > Day 3: The Twelve Days of SQL: There isn’t always a single optimal query plan for a SQL query

Day 3: The Twelve Days of SQL: There isn’t always a single optimal query plan for a SQL query


On the third day of Christmas, my true love gave to me
Three French hens.

Day 3: There isn’t always a single optimal query plan for a SQL query (Day 2: SQL is a non-procedural language) (Day 4: The way you write your query matters)

That the SQL optimizer chooses an optimal query plan (or one that is fairly close to optimal) is an article of faith for many and the presumption is that there is always an optimal query plan for a SQL query. But, as a well-known public figure once observed, “it depends on what the meaning of the word ‘is’ is.” What is a SQL statement; that is, what qualifies as a SQL statement? Here are two simple examples from the Human Resources sample schema. You will agree that each is a valid SQL statement.

select * from employees where first_name like ‘Lex’ and last_name like ‘De Haan’

select * from employees where first_name like :b1 and last_name like :b2

The Employees table is indexed on the first_name and last_name columns. In the case of the second query, there is probably no single query plan that is optimal for all values of the bind variables b1 and b2. For each combination of values, a different execution plan may be optimal: a full table scan, an index range scan followed by table access, or an index full scan followed by table access.

Which was to be demonstrated.

P.S. Dan Tow is a proponent of “robust execution plans” which work well across a wide range of likely data distributions. You can read more about such plans in his book.

Day 2: SQL is a non-procedural language

Day 4: The way you write your query matters

Categories: DBA, Oracle, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: