Test Case Showing Oracle Database 11.2.0.1 Completely Ignoring an Index Even when Hinted

22 09 2010

September 22, 2010

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):

INSERT INTO
  T1
SELECT
  DECODE(MOD(ROWNUM,3000000),0,'BOSS'||ROWNUM,
  CHR(90-MOD(ROWNUM-1,26))||
  CHR(75+MOD(ROWNUM,10))||
  CHR(80+MOD(ROWNUM,5))||
  'S'||ROWNUM) C200000020,
  RPAD('A',200,'A') PADDING
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=10000) V1,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=1000) V2;

Now to create the index and collect statistics:

CREATE INDEX IND_T1 ON T1(C200000020);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

Let’s take a look at the execution plans:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%';

Plan hash value: 634656657

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   213 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C200000020" LIKE 'BOSS%')
       filter("C200000020" LIKE 'BOSS%')

An index access, just like we had hoped.  The optimizer is predicting a single row to be retrieved.  Let’s try the other query:

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    20M| 82353   (1)| 00:16:29 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|    20M| 82353   (1)| 00:16:29 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')

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:

ALTER SYSTEM FLUSH SHARED_POOL;

SET AUTOTRACE OFF

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'T1_10053';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

Inside the 10053 trace, my 11.2.0.1 test database produced the following:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000000  #Blks:  303031  AvgRowLen:  213.00
Index Stats::
  Index: IND_T1  Col#: 1
    LVLS: 2  #LB: 32323  #DK: 9939968  LB/K: 1.00  DB/K: 1.00  CLUF: 10120176.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 10000000.000000  Rounded: 100001  Computed: 100001.02  Non Adjusted: 100001.02
  Access Path: TableScan
    Cost:  82352.89  Resp: 82352.89  Degree: 0
      Cost_io: 82073.00  Cost_cpu: 7150017105
      Resp_io: 82073.00  Resp_cpu: 7150017105
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 82352.89  Degree: 1  Resp: 82352.89  Card: 100001.02  Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  T1[T1]#0
***********************
Best so far:  Table#: 0  cost: 82352.8851  card: 100001.0195  bytes: 21300213
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

*********************************
Number of join permutations tried: 1
*********************************

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:

EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME=>USER,INDNAME=>'IND_T1',CLSTFCT=>10000)

SET AUTOTRACE TRACEONLY EXPLAIN

ALTER SYSTEM FLUSH SHARED_POOL;

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    20M| 82353   (1)| 00:16:29 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|    20M| 82353   (1)| 00:16:29 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')

Still a full table scan.  If we had generated a 10053 trace, we would see that the clustering factor for the index was indeed adjusted from what we saw earlier:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000000  #Blks:  303031  AvgRowLen:  213.00
Index Stats::
  Index: IND_T1  Col#: 1
    LVLS: 2  #LB: 32323  #DK: 9939968  LB/K: 1.00  DB/K: 1.00  CLUF: 10000.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 10000000.000000  Rounded: 100001  Computed: 100001.02  Non Adjusted: 100001.02
  Access Path: TableScan
    Cost:  82352.89  Resp: 82352.89  Degree: 0
      Cost_io: 82073.00  Cost_cpu: 7150017105
      Resp_io: 82073.00  Resp_cpu: 7150017105
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: TableScan
         Cost: 82352.89  Degree: 1  Resp: 82352.89  Card: 100001.02  Bytes: 0

Let’s force the execution plan with an index hint to see what happens:

SELECT /*+ INDEX(T1 IND_T1) */
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    20M| 82353   (1)| 00:16:29 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|    20M| 82353   (1)| 00:16:29 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')

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:

SELECT /*+ ORDERED_PREDICATES INDEX(T1 IND_T1) */
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    20M| 82353   (1)| 00:16:29 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|    20M| 82353   (1)| 00:16:29 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C200000020" LIKE 'BOSS%' OR 'BOSS'='')

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:

SET AUTOTRACE TRACEONLY EXPLAIN

ALTER SESSION SET TRACEFILE_IDENTIFIER = 'T1_10053-3';
ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = ' ';

Execution Plan
----------------------------------------------------------
Plan hash value: 634656657
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   213 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C200000020" LIKE 'BOSS%')
       filter("C200000020" LIKE 'BOSS%')

ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';

Notice by looking at the Predicate Information section of the plan that Oracle removed the nonsensical OR clause.  The 10053 trace file showed this:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000000  #Blks:  303031  AvgRowLen:  213.00
Index Stats::
  Index: IND_T1  Col#: 1
    LVLS: 2  #LB: 32323  #DK: 9939968  LB/K: 1.00  DB/K: 1.00  CLUF: 10000.00
Access path analysis for T1
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 10000000.000000  Rounded: 1  Computed: 1.03  Non Adjusted: 1.03
  Access Path: TableScan
    Cost:  82255.34  Resp: 82255.34  Degree: 0
      Cost_io: 82073.00  Cost_cpu: 4658017105
      Resp_io: 82073.00  Resp_cpu: 4658017105
kkofmx: index filter:"T1"."C200000020" LIKE 'BOSS%'
  Access Path: index (RangeScan)
    Index: IND_T1
    resc_io: 4.00  resc_cpu: 29226
    ix_sel: 0.000000  ix_sel_with_filters: 0.000000
    Cost: 4.00  Resp: 4.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: IND_T1
         Cost: 4.00  Degree: 1  Resp: 4.00  Card: 1.03  Bytes: 0

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:

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE)

VARIABLE B0 VARCHAR2(50)
VARIABLE B1 VARCHAR2(50)
VARIABLE B2 VARCHAR2(50)

EXEC :B0 := 'BOSS'
EXEC :B1 := 'BOSS'
EXEC :B2 := ''

SET AUTOTRACE OFF

Since we do not know the intention of the developer, I will try a couple of combinations to see what happens:

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
WHERE
  C200000020 LIKE :B0 || '%'
  OR :B1 = :B2;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  b47qzqbb6wymu, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C200000020
LIKE :B0 || '%'   OR :B1 = :B2

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
|*  1 |  TABLE ACCESS FULL| T1   |      1 |    100K|      3 |00:00:00.34 |     303K|
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((:B1=:B2 OR "C200000020" LIKE :B0||'%'))

The above resulted in the same full table scan that we saw earlier.

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
WHERE
  C200000020 LIKE :B0 || '%'
  OR :B0 = :B2;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  1vdyc7t6wazhz, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C200000020
LIKE :B0 || '%'   OR :B0 = :B2

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
|*  1 |  TABLE ACCESS FULL| T1   |      1 |    100K|      3 |00:00:00.34 |     303K|
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((:B0=:B2 OR "C200000020" LIKE :B0||'%'))

Specifying the B0 bind variable twice did not help.

SELECT /*+ GATHER_PLAN_STATISTICS */
  *
FROM
  T1
WHERE
  C200000020 LIKE :B0 || '%'
  OR :B0 IS NULL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  8n1bg0z9j0001, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   * FROM   T1 WHERE   C200000020
LIKE :B0 || '%'   OR :B0 IS NULL

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
|*  1 |  TABLE ACCESS FULL| T1   |      1 |    500K|      3 |00:00:00.34 |     303K|
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((:B0 IS NULL OR "C200000020" LIKE :B0||'%'))

Specifying that the B0 bind variable IS NULL did not help either, but notice the change in the predicted cardinality (the 100,000 rows increased to 500,000 rows).

Let’s try an index hint:

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 IND_T1) */
  *
FROM
  T1
WHERE
  C200000020 LIKE :B0 || '%'
  OR :B0 IS NULL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

SQL_ID  a241dy7mvudtk, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1 IND_T1) */   * FROM   T1
WHERE   C200000020 LIKE :B0 || '%'   OR :B0 IS NULL

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.34 |     303K|
|*  1 |  TABLE ACCESS FULL| T1   |      1 |    500K|      3 |00:00:00.34 |     303K|
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter((:B0 IS NULL OR "C200000020" LIKE :B0||'%'))

The optimizer still did not use the index.

—————–

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.


Actions

Information

3 responses

22 09 2010
niall litchfield

Charles, is this a null effect? I.E does the behavior reproduce with ‘BOSS’ = ‘SUPERCALIFRAGILISTICEXPEALIDOCIOUS’? I agree that Oracle should know that a string of length 4 is not null, it wouldn’t know that a bind was of course. Can’t test right now

22 09 2010
Charles Hooper

In my opinion, if ” is treated as NULL, the second half of the OR clause should always be false, because nothing is ever equal to NULL, not even NULL. I agree with you that with the bind variables, the second half of the OR clause *could* be true, even if it cannot be true on the initial hard parse – a full table scan should be expected in that case.

Before running the tests, I would assume that ‘BOSS’ = ‘SUPERCALIFRAGILISTICEXPEALIDOCIOUS’ should be handled the same as the case where I used a single space between the two ‘ characters. Here are the test results:

SET AUTOTRACE TRACEONLY EXPLAIN
 
SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = 'SUPERCALIFRAGILISTICEXPEALIDOCIOUS';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 634656657
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   213 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C200000020" LIKE 'BOSS%')
       filter("C200000020" LIKE 'BOSS%')

Above used an index range scan, as would be expected.

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = NULL;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 634656657
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   213 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C200000020" LIKE 'BOSS%')
       filter("C200000020" LIKE 'BOSS%')

Above used an index range scan, as would be expected.

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' IS NULL;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 634656657
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   213 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C200000020" LIKE 'BOSS%')
       filter("C200000020" LIKE 'BOSS%')

Above used an index range scan, as would be expected.

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = ' ';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 634656657
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   213 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   213 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C200000020" LIKE 'BOSS%')
       filter("C200000020" LIKE 'BOSS%')

Above used an index range scan, as would be expected.

SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    20M| 82353   (1)| 00:16:29 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|    20M| 82353   (1)| 00:16:29 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')

The above used a full table scan because the index access paths were not even considered.

22 09 2010
Charles Hooper

Randolf Geist demonstrated in the OTN thread that the OP’s table definition is probably missing a NOT NULL constraint. Randolf was able to generate an execution plan with a NOT NULL constraint on the C200000020 column when an index hint was also provided. Randolf used a slightly different test case, so I have adapted his test case to work with my sample table. Let’s take a look at the execution plans:

ALTER TABLE T1 MODIFY (C200000020 NOT NULL);
ALTER SYSTEM FLUSH SHARED_POOL;
 
SET AUTOTRACE TRACEONLY EXPLAIN
 
SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100K|    20M| 82353   (1)| 00:16:29 |
|*  1 |  TABLE ACCESS FULL| T1   |   100K|    20M| 82353   (1)| 00:16:29 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')

Without an index hint the optimizer still selected to perform a full table scan.

Now with the index hint:

SELECT /*+ INDEX(T1 IND_T1) */
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3418867520
 
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   100K|    20M|  9980K  (1)| 33:16:04 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1     |   100K|    20M|  9980K  (1)| 33:16:04 |
|   2 |   INDEX FULL SCAN           | IND_T1 |    10M|       | 31855   (1)| 00:06:23 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter('BOSS'='' OR "C200000020" LIKE 'BOSS%')

An index full scan, so we are using the index now. But look at the estimated time – it jumped from just 16 minutes to 33 hours and 16 minutes!

Let’s test the performance (your results will be different):

SET AUTOTRACE OFF
SET TIMING ON
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
SELECT
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
SELECT /*+ INDEX(T1 IND_T1) */
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%'
  OR 'BOSS' = '';
 
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
SELECT /*+ INDEX(T1 IND_T1) */
  *
FROM
  T1
WHERE
  C200000020 LIKE 'BOSS' || '%';

The results that I received follow:

SQL> SELECT
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    C200000020 LIKE 'BOSS' || '%'
  7    OR 'BOSS' = '';
 
C200000020
--------------------
PADDING
---------------------------------------
BOSS3000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

BOSS6000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

BOSS9000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


Elapsed: 00:00:04.49
SQL>
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Elapsed: 00:00:00.06
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Elapsed: 00:00:00.06
SQL>
SQL> SELECT /*+ INDEX(T1 IND_T1) */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    C200000020 LIKE 'BOSS' || '%'
  7    OR 'BOSS' = '';

C200000020
--------------------
PADDING
---------------------------------------
BOSS3000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

BOSS6000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

BOSS9000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


Elapsed: 00:02:32.81
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Elapsed: 00:00:00.15
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

Elapsed: 00:00:00.09
SQL>
SQL> SELECT /*+ INDEX(T1 IND_T1) */
  2    *
  3  FROM
  4    T1
  5  WHERE
  6    C200000020 LIKE 'BOSS' || '%';

C200000020
--------------------
PADDING
---------------------------------------
BOSS3000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

BOSS6000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

BOSS9000000
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


Elapsed: 00:00:00.07

4.49 seconds to perform the full table scan, 2 minutes and 32.81 seconds for the index full scan, and 0.07 seconds to do it the correct way.

The table and index extent sizes follow:

SELECT
  SEGMENT_NAME SEGMENT,
  COUNT(*) EXTENTS,
  BYTES/1024 EXT_SIZE_KB,
  (COUNT(*) * BYTES)/1048576 TOTAL_MB
FROM
  DBA_EXTENTS
WHERE
  OWNER=USER
  AND SEGMENT_NAME IN ('IND_T1','T1')
GROUP BY
  SEGMENT_NAME,
  BYTES
ORDER BY
  SEGMENT_NAME,
  BYTES;
 
SEGMENT       EXTENTS EXT_SIZE_KB   TOTAL_MB
---------- ---------- ----------- ----------
IND_T1             16          64          1
IND_T1             63        1024         63
IND_T1             24        8192        192
T1                 16          64          1
T1                 63        1024         63
T1                120        8192        960
T1                 21       65536       1344

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




Follow

Get every new post delivered to your Inbox.

Join 142 other followers

%d bloggers like this: