What causes Oracle SQL execution plan to change based on where clause value?

Question

Why does the Oracle execution plan change based on a where clause value alone? I'm not talking bind variables, but in-place values in the SQL.

For example running Explain Plan on these queries in SQL Developer:

select col1, col2 from table1 where col1 >= '4520/85/36 99.99.99'; -- uses index on col1
select col1, col2 from table1 where col1 >= '4520/85/36 00.00.00'; -- full table scan

What can cause Oracle to choose a different execution plan like this? FYI, neither of the above values exist in the table, but they represent a range of numerically related values.

Another observation, by simply removing the other column changes the execution plan again:

select col1, col2 from table1 where col1 >= '4520/85/36 00.00.00'; -- full table scan
select col1 from table1 where col1 >= '4520/85/36 00.00.00'; -- uses index on col1

If the question is not clear, please let me know what further information would be helpful.

Edit: I have rebuilt the table indexes and gathered statistics, I have even dropped and recreated the index. The number of rows in this table is around 12 million.

Answer 1

When bind variables are not used, similar query with different data in where clause will be treated as different queries. In such cases, execution plan is worked out for each query independently.

Arriving at execution plan is result of multiple inputs, one of them being Oracle's understanding of data distribution in the table (histograms, based on last statistics collected). If there is any skew in data distribution (especially for indexed column), oracle can pick either full table scan or index scan based on value in where clause. Other factors include presence of index on the field, partition of table etc.

Also, removing column of the select clause has no effect on the actual execution plan, however Oracle thinks its a different query and arrives at plan independently.

If you desire consistent execution plans, then using bind variable is right solution. Also, you can avoid parse of query, plan computation etc each time when query is fired (as long sql_id of the query remains same, and its in cache, plan will not be recomputed).

Also, know that, there is a feature called bind peak which affects query execution plan based on data in bind variable even when bind variables are used.

This thread very interesting discussion on this topic.

Answer 2

Oracle is pretty smart ;) It can basically calculate whats the best way to get the data and if it calculates that full table scan is better then it uses it. Are there differences in cost in explain plan?

If you want to decide this for Oracle you can use hints.

Answer 3

In the first example, it is possible that the first where clause is satisfied by just 3% of all rows in the base table. When you change the RHS to the other value, perhaps 60% of all rows will satisfy the condition. In the first case the optimizer will choose to use the index; in the second it may choose a full table scan (since using an index has its own overhead). Besides, what really matters is how many rows the optimizer thinks will be retrieved; this is why the stats must be up to date, and why wildly non-uniformly-distributed data in your table may cause strange choices by the optimizer even if the stats are current.

In the second example, since you ONLY need the col1 values, and those are present in the index already, the optimizer will use just the index no matter what fraction of the rows will be returned (because, even when the condition is satisfied, the engine doesn't need to touch the base table; the needed value, of col1 only, is already available from the index). If you need to retrieve both col1 and col2, though, finding "matches" through the index is not enough - when a match is found, the engine still needs to retrieve the row from the base table to read the col2 value. So if it expects a relatively large fraction of the rows to be returned, it will just do a full table scan instead.

If you had a composite index on col1, col2 (with col1 leading) you might see the optimizer choose to use that index instead, and not do a full table scan no matter how many rows it expects to retrieve.