In a recent OTN thread a person asked an interesting question: why isn’t my index being used? A query of a table with 8,000,000 rows should quickly return exactly 3 rows when an available index is used, and that index is used when the WHERE clause is simply:

WHERE
C200000020 LIKE 'BOSS' || '%'

However, the application is submitting a WHERE clause that includes an impossible condition in an OR clause, like the following, which is not much different from stating OR 1=2:

WHERE
C200000020 LIKE 'BOSS' || '%'
OR 'BOSS' = ''

That constant1 = constant2 predicate, at least on Oracle Database 10.1 and above, is sufficient to keep the index from being used, thus the query performs a full table scan. But why?

I think that we need a test case to see what is happening. First, we will create a simple table with our column of interest and a large column that should help to discourage full table scans:

CREATE TABLE T1 (
C200000020 VARCHAR2(20),
PADDING VARCHAR2(250));

Next, we will insert 10,000,000 rows into the table such that an index built on the column C200000020 will have a very high clustering factor, and 3 rows will have a value that begins with BOSS (as a result of the DECODE statement):

A full table scan, just like the original poster in the OTN thread experienced. Notice that the optimizer is now predicting that 100,000 rows (1% of the rows) will be retrieved. Repeating, 1% of the rows and a full table scan. Let’s generate a 10053 trace for the SQL statement:

The unknown result of the constant in the WHERE clause (‘BOSS’ = ”) caused Oracle to predict that the cardinality will be (1 row) + (1% of the rows) = 100,001. With a clustering factor of 10,120,176 the optimizer is (possibly) convinced that it will need to perform single block physical reads of a large number of table blocks to read the 100,001 rows that it expects to retrieve, so it decided that a full table scan would complete faster. But the situation is worse than that – it did not even consider an index access path. As a demonstration, I will manually set the index’s clustering factor to a low value and check the execution plan again:

Note that the optimizer did not (or could not) obey the hint. It decided to apply the ‘BOSS’=” predicate first, so maybe that is the problem. Let’s try a hint to force the optimizer to apply the predicates in order, rather than based on calculated cost:

The predicate section of the execution plan changed, but the optimizer still will not consider an index access path for the SQL statement. There is a chance that the OP could do something to force the index access path by hacking a stored outline for the query, but my guess is that the restriction on the C200000020 column changes from time to time, so an outline likely will not work. The OP could try to file an Oracle bug report because the optimizer completely ignored the index access paths (as shown in the 10053 trace file), but a better course of action would be to have the application submitting the SQL statement fixed.

Let’s try a small variation on the original test SQL statement. Let’s see what happens when we add a space between the two ‘ characters:

So, it appears that if the optimizer is presented with a zero length VARCHAR being compared with another VARCHAR in the WHERE clause, there could be unexpected cases were index access paths will not be used even when hinted.

Toon Koppelaars mentioned in the OTN thread that the WHERE clause should be using bind variables, and suggested the following for the WHERE clause:

( (T131.C200000020 LIKE (:B0 || '%')) OR (:B0 IS NULL))

I agree with Toon regarding the use of bind variables. Unfortunately, it does not look like bind variables improve the situation, at least in my test case.

I cannot use AUTOTRACE due to the risk that it will display an incorrect execution plan due to the bind variables, so I will use DBMS_XPLAN.DISPLAY_CURSOR along with a GATHER_PLAN_STATISTICS hint in the SQL statement. First the statistics collection (to correct any manual adjustment to the index’s clustering factor that was performed earlier) and bind variable setup:

Cases where Oracle’s optimizer ignores index hints are typically indications of bugs in the optimizer – as we saw, the optimizer did not even consider (generate a calculated cost for) an index access path when no space appeared between the two ‘ characters in the original SQL statement. Other cases of Oracle’s optimizer ignoring hints may be found here: Demonstration of Oracle “Ignoring” an Index Hint.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:

<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: