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?

« »


Actions

Information

  • Date : July 11, 2011
  • Categories : SQL

6 responses

11 07 2011
Gokhan Atil (08:56:00) :

There’s a MOS document about nonworking NO_INDEX hint:

Ineffective SQL Hints NO_INDEX [ID 399942.1]

It says “The NO_INDEX hint does not applying to UNIQUE indexes. The NO_INDEX hint applies to function-based, B*-tree, bitmap, cluster, or domain indexes.”

I have tested it on 9.2.0.8, 10.2.0.3 and 11.2.0.1 and I see that “no_index hint” WORKS as intended even on unique indexes.

Reply
11 07 2011
Charles Hooper (10:04:47) :

Gokhan,

That MOS (Metalink) article is an interesting find – thank you for locating it.

I see that the article was last modified November 11, 2010. The article lists as a reference a bug that applies to Oracle Database 7.1.6.2. I think that the MOS article is in need of quite a bit of editing. The take aways from that article:
* Can a unique index not be a b*tree index?
* I should set DB_FILE_MULTIBLOCK_READ_COUNT to a value greater than 16 so that my NO_INDEX hint works?

Here is a quick test on 11.2.0.2 to demonstrate that the MOS article does not apply to 11.2.0.2 (that is one of the few release versions you did not test). First, a basic query using the sample table created in the article:

SET LINESIZE 140
SET PAGESIZE 1000
 
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 non-unique index was selected by default.

Now drop that non-unique index, create a unique index on the same column, and try the SQL statement again:

DROP INDEX IND_T3_C1;
CREATE UNIQUE INDEX IND_T3_C1_UNIQUE ON T3(C1);
 
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T3',CASCADE=>TRUE,ESTIMATE_PERCENT=>100)
 
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: 66750336
 
----------------------------------------------------------------------------------------------------------
| 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_UNIQUE |      1 |     20 |     20 |00:00:00.01 |       5 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<=20)

The optimizer selected to use the unique index, as expected.

Trying again with the NO_INDEX hint:

SELECT /*+ NO_INDEX(T3 IND_T3_C1_UNIQUE) 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  gvsjzs6x62f1d, child number 0
-------------------------------------
SELECT /*+ NO_INDEX(T3 IND_T3_C1_UNIQUE) 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)

The NO_INDEX hint worked without issue – a full table scan was performed.

—

Another interesting MOS article is this one Doc ID 1051265.1, Bug 5882821 – “Wrong Results from a RANGE-LIST PARTITIONED TABLE”. Here the NO_INDEX hint was used to produce the correct query results, while the unhinted version provided incorrect results.

Reply
8 02 2012
Hanan (05:42:49) :

Hi,

Following is an example of NO_INDEX is not working in 10g: Same SQL in 11.2.0.2 w/o any hints doesn’t use the Index and working much mush faster:

select count(*) from
(select /*+ NO_INDEX(c CDR_IND_03*/
s.main_resource, s.OPERATOR_CODE , s.ORIGINATED_PREPAID_IND
from   lb_5.subscriber s , lb_5.cdr c
where
c.recieve_time between TO_DATE('01/01/2012','DD/MM/YYYY') and TO_DATE('09/02/2012','DD/MM/YYYY')
and s.operator_code='Partner'
and s.ORIGINATED_PREPAID_IND='0'
and s.main_resource=c.Anumber
and substr(lb_5.get_avl(avl,'DialedDigits'),1,3) = '117'
group by s.main_resource, s.OPERATOR_CODE , s.ORIGINATED_PREPAID_IND)

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1033640149

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop 
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |            |     1 |       |       |   923   (1)| 00:00:12 |       |       
|   1 |  SORT AGGREGATE                         |            |     1 |       |       |            |          |       |       
|   2 |   VIEW                                  |            |  3377 |       |       |   923   (1)| 00:00:12 |       |       
|   3 |    HASH GROUP BY                        |            |  3377 |  2186K|  4520K|   923   (1)| 00:00:12 |       |       
|*  4 |     HASH JOIN                           |            |  3377 |  2186K|       |   447   (2)| 00:00:06 |       |       
|*  5 |      TABLE ACCESS FULL                  | SUBSCRIBER | 27819 |   516K|       |   446   (1)| 00:00:06 |       |       
|   6 |      PARTITION RANGE ITERATOR           |            | 15575 |  9795K|       |     0   (0)| 00:00:01 |     9 |    18 
|   7 |       PARTITION LIST ALL                |            | 15575 |  9795K|       |     0   (0)| 00:00:01 |     1 |    17 
|*  8 |        TABLE ACCESS BY LOCAL INDEX ROWID| CDR        | 15575 |  9795K|       |     0   (0)| 00:00:01 |   137 |   306 
|*  9 |         INDEX SKIP SCAN                 | CDR_IND_03 | 52700 |       |       |     0   (0)| 00:00:01 |   137 |   306 
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("S"."MAIN_RESOURCE"="C"."ANUMBER")
   5 - filter("S"."MAIN_RESOURCE" IS NOT NULL AND "S"."ORIGINATED_PREPAID_IND"=0 AND "S"."OPERATOR_CODE"='Partner')

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
   8 - filter(SUBSTR("LB_5"."GET_AVL"("AVL",'DialedDigits'),1,3)='117')
   9 - access("C"."RECIEVE_TIME">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "C"."RECIEVE_TIME"<=TO_DATE(' 2012-02-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
       filter("C"."RECIEVE_TIME"<=TO_DATE(' 2012-02-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

26 rows selected.
Reply
8 02 2012
Charles Hooper (06:08:03) :

Hanan,

Your NO_INDEX hint appears to be incompletely formed. Please try the following to see if the execution plan changes:

select count(*) from
(select /*+ NO_INDEX(c CDR_IND_03) */
s.main_resource, s.OPERATOR_CODE , s.ORIGINATED_PREPAID_IND
from   lb_5.subscriber s , lb_5.cdr c
where
c.recieve_time between TO_DATE('01/01/2012','DD/MM/YYYY') and TO_DATE('09/02/2012','DD/MM/YYYY')
and s.operator_code='Partner'
and s.ORIGINATED_PREPAID_IND='0'
and s.main_resource=c.Anumber
and substr(lb_5.get_avl(avl,'DialedDigits'),1,3) = '117'
group by s.main_resource, s.OPERATOR_CODE , s.ORIGINATED_PREPAID_IND)
Reply
8 02 2012
Hanan (06:56:52) :

10x for your quick answer and your good “compile” .

Reply
20 02 2013
Akhil Mahajan (07:33:20) :

I was so silly. Just using the table alias in no_index hint helped me. For past 7-8 days, I kept thinking that no_index hint just cannot work.

Reply

Leave a Reply

Fill in your details below or click an icon to log in:

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

Cancel

Connecting to %s


Follow

Get every new post delivered to your Inbox.

Join 100 other followers

%d bloggers like this: