What Would Cause a NO_INDEX Hint to Not Work as Expected?

11 07 2011

July 11, 2011

Recently, the following search keywords were used to access an article on my site, and that search triggered an idea for another blog article:

no_index hint oracle 10g not working 

In Oracle Database, hints are directives that must be obeyed (with a couple of minor exceptions that include bugs).  I started wondering what might cause a NO_INDEX hint to not work as expected.  Let’s create a test table for a couple of experiments:

CREATE TABLE T3(
  C1 NUMBER,
  C2 NUMBER,
  C3 NUMBER,
  C4 VARCHAR2(300));

INSERT INTO
  T3
SELECT
  ROWNUM C1,
  TRUNC(ROWNUM/10000) C2,
  MOD(ROWNUM,10000) C3,
  LPAD('A',300,'A')
FROM
  DUAL
CONNECT BY
  LEVEL<=1000000;

CREATE INDEX IND_T3_C1 ON T3(C1);
CREATE INDEX IND_T3_C2 ON T3(C2);
CREATE INDEX IND_T3_C3 ON T3(C3);

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100)

SET LINESIZE 140
SET PAGESIZE 1000 

Let’s try a simple query that accesses the table, and display the execution plan for that query (note that these test SQL statements are being executed on Oracle Database 11.2.0.2):

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1<=20;

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

SQL_ID  0s5xrvx04309f, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1<=20

Plan hash value: 1371903174

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     20 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=20) 

The IND_T3_C1 index was automatically selected to assist data retrieval.

Let’s try a NO_INDEX hint just to verify that the hint can work as expected:

SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1<=20;

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

SQL_ID  d0gpwhvg7629r, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
  C3,   SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1<=20

Plan hash value: 4161002650

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     20 |00:00:00.01 |   45583 |
|*  1 |  TABLE ACCESS FULL| T3   |      1 |     20 |     20 |00:00:00.01 |   45583 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1"<=20) 

As can be seen above, a full table scan is performed for data retrieval, rather than the index that was used in the previous example.

Let’s try another example with the NO_INDEX hint:

SELECT /*+ NO_INDEX(IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1<=20;

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

SQL_ID  4p5xpu625cw5a, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
C3,   SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1<=20

Plan hash value: 1371903174

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     20 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=20) 

That did not work quite as someone might think – you must specify the table/view name in the NO_INDEX hint.

Let’s try another example:

SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3 T
WHERE
  C1<=20;

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

SQL_ID  fnjc3pc41a2mh, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
  C3,   SUBSTR(C4,1,10) C4 FROM   T3 T WHERE   C1<=20

Plan hash value: 1371903174

---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |     20 |00:00:00.01 |       8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       8 |
|*  2 |   INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=20) 

Again, the index was used despite the NO_INDEX hint – if you alias a table/view, you must specify the alias in the NO_INDEX hint.  This seems to be a common problem when people report in Internet forums that Oracle hints do not work as expected.

Another example:

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ NO_INDEX(T3 IND_T3_C1) */
       C1
     FROM
       T3
     WHERE
       C1<=20);

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

Plan hash value: 587667290

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |     20 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN SEMI              |           |      1 |     20 |     20 |00:00:00.01 |      13 |   705K|   705K| 1125K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     20 |     20 |00:00:00.01 |       4 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       3 |       |       |          |
|*  4 |   TABLE ACCESS FULL          | T3        |      1 |     20 |     20 |00:00:00.01 |       9 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   3 - access("C1"<=20)
   4 - filter("C1"<=20) 

As seen by the above, the IND_T3_C1 index was still used even though the hint was correctly formed.  What is wrong?  The scope of the index hint is only in the subquery found in the WHERE clause and that hint does not apply to the main portion of the SQL statement – the hint did work in the scope of the subquery.  Note that the execution plan shows that the query was transformed into a simple join.

A similar SQL statement, with the NO_INDEX hint relocated:

SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+  */
       C1
     FROM
       T3
     WHERE
       C1<=20);

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

SQL_ID  3n76qa6km68r2, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(T3 IND_T3_C1) GATHER_PLAN_STATISTICS */   C1,   C2,
  C3,   SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+  */
      C1      FROM        T3      WHERE        C1<=20)

Plan hash value: 3266157401

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |     20 |00:00:00.07 |   45586 |       |       |          |
|*  1 |  HASH JOIN SEMI    |           |      1 |     20 |     20 |00:00:00.07 |   45586 |   705K|   705K| 1140K (0)|
|*  2 |   TABLE ACCESS FULL| T3        |      1 |     20 |     20 |00:00:00.01 |   45581 |       |       |          |
|*  3 |   INDEX RANGE SCAN | IND_T3_C1 |      1 |     20 |     20 |00:00:00.01 |       5 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C1"<=20)
   3 - access("C1"<=20) 

As can be seen above, the IND_T3_C1 index was still used because the NO_INDEX scope is only in the main body of the SQL statement, not in the subquery found in the WHERE clause.  Once again, the execution plan shows that the query was transformed into a simple join.

OK, so we saw in the previous examples that query transformations happened and Oracle’s optimizer was able to keep track of the scope of the NO_INDEX hint, even when the query was tranformed into a simple join.  Let’s try another example, this time with a NO_QUERY_TRANSFORMATION hint, a NO_INDEX hint, and an INDEX hint: 

SELECT /*+ INDEX(T3 IND_T3_C1) NO_QUERY_TRANSFORMATION GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ NO_INDEX(T3 IND_T3_C1) */
       C1
     FROM
       T3
     WHERE
       C1<=20);

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

SQL_ID  bbs14bbywgfq2, child number 0
-------------------------------------
SELECT /*+ INDEX(T3 IND_T3_C1) NO_QUERY_TRANSFORMATION
GATHER_PLAN_STATISTICS */   C1,   C2,   C3,   SUBSTR(C4,1,10) C4 FROM
T3 WHERE   C1 IN     (SELECT /*+ NO_INDEX(T3 IND_T3_C1) */        C1
  FROM        T3      WHERE        C1<=20)

Plan hash value: 371539318

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |     20 |00:00:00.01 |   45723 |
|*  1 |  FILTER             |      |      1 |        |     20 |00:00:00.01 |   45723 |
|   2 |   TABLE ACCESS FULL | T3   |      1 |   1000K|   1000K|00:00:00.16 |   45583 |
|*  3 |   FILTER            |      |   1000K|        |     20 |00:00:00.11 |     140 |
|*  4 |    TABLE ACCESS FULL| T3   |     20 |      1 |     20 |00:00:00.01 |     140 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   3 - filter(:B1<=20)
   4 - filter(("C1"=:B1 AND "C1"<=20)) 

Note in the above that no query transformation happened, but also notice that two full table scans were performed – it *appears* that the INDEX hint was ignored.  For extra credit, explain why the optimizer could not apply the INDEX hint.

What if we create a view with an embedded hint?

CREATE VIEW V3 AS
SELECT /*+ INDEX(TV3 IND_T3_C2) */
  C1,
  C2,
  C3,
  C4
FROM
  T3 TV3
WHERE
  C2 <= 20; 

Now a query with a NO_INDEX hint that uses that view:

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ NO_INDEX(V3 IND_T3_C2) */
       C1
     FROM
       V3
     WHERE
       C1<=200);

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

SQL_ID  agjapbkt2n8av, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+ NO_INDEX(V3
IND_T3_C2) */        C1      FROM        V3      WHERE        C1<=200)

Plan hash value: 1309751330

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    200 |00:00:00.01 |      54 |    |          |          |
|*  1 |  HASH JOIN RIGHT SEMI        |           |      1 |     42 |    200 |00:00:00.01 |      54 |  1452K|  1452K| 1282K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     42 |    200 |00:00:00.01 |      13 |    |          |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |       3 |    |          |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |    200 |    200 |00:00:00.01 |      41 |    |          |          |
|*  5 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |      17 |    |          |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C2"<=20)
   3 - access("C1"<=200)
   5 - access("C1"<=200) 

As seen by the above, the NO_INDEX hint was applied and the conflicting hint that was embedded in the view was not applied.

For fun, let’s reverse the location of the INDEX and NO_INDEX hints to see if the NO_INDEX hint always overrides the INDEX hint.  First, the view definition:

CREATE OR REPLACE VIEW V3 AS
SELECT /*+ NO_INDEX(TV3 IND_T3_C2) */
  C1,
  C2,
  C3,
  C4
FROM
  T3 TV3
WHERE
  C2 <= 20; 

Now the query:

 SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+ INDEX(V3 IND_T3_C2) */
       C1
     FROM
       V3
     WHERE
       C1<=200);

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

SQL_ID  5j745zr4dmqzx, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+ INDEX(V3
IND_T3_C2) */        C1      FROM        V3      WHERE        C1<=200)

Plan hash value: 3864333899

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    200 |00:00:00.06 |   10017 |       |       |          |
|*  1 |  HASH JOIN RIGHT SEMI        |           |      1 |     42 |    200 |00:00:00.06 |   10017 |  1452K|  1452K| 1269K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     42 |    200 |00:00:00.01 |    9976 |       |       |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C2 |      1 |    209K|    209K|00:00:00.02 |     410 |       |       |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |    200 |    200 |00:00:00.01 |      41 |       |       |          |
|*  5 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |      17 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C1"<=200)
   3 - access("C2"<=20)
   5 - access("C1"<=200)

As can be seen by the above, the NO_INDEX hint in the view was not applied due to the conflicting INDEX hint in the main query.  So, that is another case where the NO_INDEX hint could appear to not work as expected.  Just for confirmation that the NO_INDEX hint in the view works as expected, we will re-execute the query without the INDEX hint:

SELECT /*+ GATHER_PLAN_STATISTICS */
  C1,
  C2,
  C3,
  SUBSTR(C4,1,10) C4
FROM
  T3
WHERE
  C1 IN
    (SELECT /*+  */
       C1
     FROM
       V3
     WHERE
       C1<=200);

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

SQL_ID  fq3g6pr7ffj2f, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */   C1,   C2,   C3,
SUBSTR(C4,1,10) C4 FROM   T3 WHERE   C1 IN     (SELECT /*+  */
C1      FROM        V3      WHERE        C1<=200)

Plan hash value: 1309751330

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    200 |00:00:00.01 |      54 |    |          |          |
|*  1 |  HASH JOIN RIGHT SEMI        |           |      1 |     42 |    200 |00:00:00.01 |      54 |  1452K|  1452K| 1232K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |     42 |    200 |00:00:00.01 |      13 |    |          |          |
|*  3 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |       3 |    |          |          |
|   4 |   TABLE ACCESS BY INDEX ROWID| T3        |      1 |    200 |    200 |00:00:00.01 |      41 |    |          |          |
|*  5 |    INDEX RANGE SCAN          | IND_T3_C1 |      1 |    200 |    200 |00:00:00.01 |      17 |    |          |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1"="C1")
   2 - filter("C2"<=20)
   3 - access("C1"<=200)
   5 - access("C1"<=200) 

  1. By executing the above test scripts on Oracle Database 11.1, 10.2, 10.1, 9.2, or 9.0.1 do you see different results?  I am trying to understand why the person performing the search might have included 10g in the search keywords.  Is it possible that the hint appeared to work correctly in 9.2 simply by coincidence, and a query transformation in 10.1 or 10.2 exposed the fact that the hint was malformed?
  2. Is it possible that a query transformation can cause a NO_INDEX hint to be ignored?  If yes, please provide a test case that demonstrates a NO_INDEX hint being ignored due to a transformation.
  3. Are there any other examples where a NO_INDEX hint will appear to not work properly?  Could an index organized table cause problems for this hint?







Follow

Get every new post delivered to your Inbox.

Join 137 other followers