Demonstration of Oracle “Ignoring” an Index Hint

July 19, 2010 (Modified July 26, 2010, January 18, 2011)

In an earlier blog article I showed that adding an index hint is sufficient to allow Oracle to use a normal (non-function based) index on a column when that column is included in the WHERE clause inside a function.  Another blog article listed reasons why the Oracle optimizer may select not to use an index, with the help of the book “Expert One-On-One Oracle”.  I also wrote a blog article about the various hints that are available in 11g R1 and 11g R2.  I just stumbled across a forum post of mine from a year ago that included a test case regarding Oracle ignoring hints, so I thought that I would include that test case here (one of the initial justifications for setting up this blog is that I had difficulty with locating my previously created test cases).

First, hints are directives – Oracle’s optimizer cannot ignore hints unless:

• The hint is invalid due to the wrong alias used in the hint
• The hint is malformed
• The hint is incompatible with another hint
• The query was transformed by the optimizer into a form that is incompatible with the hint before the optimizer applied the hint
• The hint, if followed, would cause the wrong results to be returned (see the forum thread for an explanation)

(Late Additions to the List, added July 26, 2010:)

• Bugs in Oracle Database cause the hint to be lost (see Jonathan Lewis’ comment in the above forum post, and the examples on his blog – for example)
• The hint, which appears like a comment, is removed before the query is sent to Oracle Database (see Mark W. Farnham’s comment below)
• The hint specifies the use of a feature that is explicitly disabled by an initialization parameter
• The _OPTIMIZER_IGNORE_HINTS initialization parameter is set to TRUE
• An off-shoot of bullet point #5, an INSERT statement includes an APPEND hint and the table to be modified includes triggers and/or foreign key constraints (see Mohamed Houri’s comments below)

(Late Addition to the List, added January 18, 2011:)

• An Oracle keyword, such as FAST, or PERFORMANCE, or COMMENT, or even a colon ( : ), is specified before the list of hints (see Adding Comments to SQL Statements Improves Performance?).

Here is the setup for the test case from the forum thread:

CREATE TABLE T15(
  C1 NUMBER NOT NULL,
  C2 VARCHAR2(50) NOT NULL,
  C3 NUMBER,
  C4 VARCHAR2(300));

INSERT INTO T15
SELECT
  ROWNUM,
  TO_CHAR(ROWNUM,'0000000')||'A',
  DECODE(MOD(ROWNUM,1000),0,NULL,ROWNUM),
  LPAD('A',300,'A')
FROM
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL <= 10000) V1,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL <= 1000) V2;

CREATE INDEX IND_T15_C1_C2 ON T15(C1,C2);
CREATE INDEX IND_T15_C3 ON T15(C3);

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

The above created the table T15, the composite index IND_T15_C1_C2 on the columns C1 and C2, and the index IND_T15_C3 on just the C3 column. The table is large enough due to column C4 that Oracle will probably select to use an index, when possible, rather than performing a full table scan. Now, let’s see what happens when we try to determine the number of rows in table T15:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

The optimizer selected to use the composite index IND_T15_C1_C2 rather than a full table scan or the much smaller index IND_T15_C3 on just the column C3. Let’s try a hint to use the index IND_T15_C3:

SELECT /*+ INDEX(T1 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

Oracle still used the IND_T15_C1_C2 index, even though I hinted to use the IND_T15_C3 index. But wait, there is a problem. The table is actually T15, not T1. I have included this example, as it is an easy mistake to make when typing SQL statements. Let’s try again with a correctly formed hint in the SQL statement:

SELECT /*+ INDEX(T15 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10174   (1)| 00:02:03 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10174   (1)| 00:02:03 |
-------------------------------------------------------------------------------

SET AUTOTRACE OFF

Oracle still ignored the hint to use the IND_T15_C3 index, and selected to use the IND_T15_C1_C2 index instead (again). Oh, Oracle does not index NULL values in a non-composite B*Tree index (or when alll values are NULL in a composite index), so using that index may yield the wrong result. We can fix that problem:

UPDATE
  T15
SET
  C3=0
WHERE
  C3 IS NULL;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T15',CASCADE=>TRUE,NO_INVALIDATE=>FALSE)

Let’s try again:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10559   (1)| 00:02:07 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10559   (1)| 00:02:07 |
-------------------------------------------------------------------------------

Notice that the optimizer did not select to use the IND_T15_C3 index, let’s help it with a hint:

SELECT /*+ INDEX(T15 IND_T15_C3) */
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 4197727756

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 | 10559   (1)| 00:02:07 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C1_C2 |    10M| 10559   (1)| 00:02:07 |
-------------------------------------------------------------------------------

Notice that the optimizer still ignored the index hint and used the larger index. Let’s see if we can help the optimizer by telling it that column C3 cannot hold a NULL value:

ALTER TABLE T15 MODIFY (C3 NUMBER NOT NULL);

SELECT
  COUNT(*)
FROM
  T15;

Execution Plan
----------------------------------------------------------
Plan hash value: 877827156

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |  6700   (1)| 00:01:21 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C3 |    10M|  6700   (1)| 00:01:21 |
----------------------------------------------------------------------------

Oracle finally used the correct smaller index, without needing a hint.

——

What else might cause Oracle’s optimizer to “ignore” a hint?  Last year Jonathan Lewis posed this question, with a lot of interesting responses showing possible reasons for the FULL hint to be ignored.  I supplied two test cases in the comments of that blog article, so I thought that I would reproduce those test cases here.

Test Case #1:

CREATE TABLE T2 AS
SELECT
 ROWNUM C1,
 ROWNUM*2 C2,
 LPAD(' ',500,' ') C3
FROM
 DUAL
CONNECT BY
 LEVEL <= 48000;

CREATE UNIQUE INDEX T1_PK ON T2(C1);
CREATE UNIQUE INDEX T1_N2 ON T2(C1,C2);

ALTER TABLE T2 MODIFY (
 C1 NOT NULL,
 C2 NOT NULL);

CREATE OR REPLACE VIEW T1 AS
SELECT /*+ INDEX(T2) */
 C1,
 C2
FROM
 (SELECT
   *
 FROM
   T2) T2;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T2')

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 1213398864

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |   101 (100)|          |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T1_PK | 48000 |   101   (1)| 00:00:02 |
------------------------------------------------------------------

DROP INDEX T1_PK;

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 824454759

------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |   134 (100)|          |
|   1 |  SORT AGGREGATE  |       |     1 |            |          |
|   2 |   INDEX FULL SCAN| T1_N2 | 48000 |   134   (1)| 00:00:02 |
------------------------------------------------------------------

DROP INDEX T1_N2;

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 3321871023

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T2   | 48000 |  1015   (1)| 00:00:13 |
-------------------------------------------------------------------

—

Test Case #2:

In schema 1:

CREATE TABLE T1 AS
SELECT
 ROWNUM C1,
 ROWNUM*2 C2,
 LPAD(' ',500,' ') C3
FROM
 DUAL
CONNECT BY
 LEVEL <= 48000;

CREATE UNIQUE INDEX T1_PK ON T1(C1);
CREATE UNIQUE INDEX T1_N2 ON T1(C1,C2);

ALTER TABLE T1 MODIFY (
 C1 NOT NULL,
 C2 NOT NULL);

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

CREATE OR REPLACE VIEW T1_VIEW AS
SELECT /*+ INDEX_FFS(T1_V) */
 C1,
 C2
FROM
 (SELECT
   *
 FROM
   T1) T1_V;

CREATE OR REPLACE PUBLIC SYNONYM T1 FOR T1_VIEW;

GRANT SELECT ON T1_VIEW TO PUBLIC;
GRANT SELECT ON T1 TO PUBLIC;

In schema 2:

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 1018460547

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |    29 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_PK | 48000 |    29   (0)| 00:00:01 |
----------------------------------------------------------------------- 

In schema 1:

DROP INDEX T1_PK;

In schema 2:

select /*+ full(t) no_index(t) */ count(*) from t1 t;

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

Plan hash value: 177081169

-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |       |    38 (100)|          |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T1_N2 | 48000 |    38   (0)| 00:00:01 |
-----------------------------------------------------------------------

In schema 1:

DROP INDEX T1_N2;

In schema 2:

select /*+ full(t) */ count(*) from t1 t;

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

Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |  1015 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 48000 |  1015   (1)| 00:00:13 |
-------------------------------------------------------------------

—

Jonathan pointed out in his blog article that there is another very sensible reason for Oracle’s optimizer to “ignore” the FULL hint… the table was defined as an index organized table.