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.
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.