Demonstration of Oracle “Ignoring” an Index Hint

19 07 2010

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

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.


Actions

Information

10 responses

19 07 2010
coskan

Very nice compilation Charles definitely a reference bookmark to share with people when they ask why their hint is not used.

Another good reference to me is Troubleshooting Oracle Performance by Christian Antognini. Hints part of the book is available free to everybody for further reading on google books http://bit.ly/cKM2OE

19 07 2010
Charles Hooper

Coskan,

Thank you for the comments and the link. I forgot that the “Troubleshooting Oracle Performance” book had such a detailed section about using hints (I really need to finish the second read through of the book).

21 07 2010
Mohamed

I would add to your list of situations where hints are ignored the following line (particularly for the /*+ append */ hint)

(a) The /*+ append */ hint is silently ignored when the inserted table contains triggers and/or Foreign key

Best Regards

Mohamed Houri

21 07 2010
Charles Hooper

Mohamed,

I think what you mention is a good example of bullet point #5 that likely is not an obvious reason why Oracle’s optimizer would not be able to use the hint – when I wrote the article I was only considering the possibility of NULL values in a column that has an index. (Edit July 24, 2010: I just noticed that I wrote the word “need” rather than “not”. I probably should have stated that Mohamed’s comment is a very good example of bullet point #5.)

23 07 2010
Blogroll Report 16/07/2010 – 23/07/2010 « Coskan’s Approach to Oracle

[...] 11-Why does Oracle / CBO ignores your Hint? Charles Hooper-Demonstration of Oracle “Ignoring” an Index Hint [...]

23 07 2010
Log Buffer #196, A Carnival of the Vanities for DBAs | The Pythian Blog

[...] Charles Hooper blogs about an in-depth investigation on what can cause Oracle to ignore a hint. [...]

26 07 2010
Mark W. Farnham

One interesting thing Mike Brown of Colibrilimited pointed out to me is that some client server interfaces discard /* */ comments. Of course looking in the v$ tables will reveal if the hint never arrived to the optimizer. But in the context of a list of why the optimizer “ignored” a hint, never having gotten it is a useful addition. By the way, most interfaces that do discard /* */ comment blocks respect the ANSI(?) rule of passing through — format comments, which the Oracle optimizer thankfully respects as well. While I find block /* */ comments to be more readable, the — format is probably the way to go if you really need a hint to be passed through.

26 07 2010
Charles Hooper

Mark,

Thanks for another great example of why it may appear that the optimizer ignored a hint – the optimizer never saw the hint. That is something that I did not consider. It might be interesting to see if that is why the AND_EQUAL hint was apparently “ignored” in this recent OTN thread: http://forums.oracle.com/forums/thread.jspa?threadID=1104424 (this is in a 9.2.0.8 database, while the AND_EQUAL hint is deprecated in 10g).

Your comment helped me find another possible cause for a hint to be “ignored” – initialization parameter settings. If an initialization parameter is set to explicitly disable a feature, a hint likely will not allow the optimizer to consider that feature (unless the hint explicitly sets an initialization parameter value). For example, if we start with the T15 table from above (I restricted it to 1,000,000 rows rather than 10,000,000):

UPDATE
  T15
SET
  C3=0
WHERE
  C3 IS NULL;
 
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T15',CASCADE=>TRUE)
 
ALTER TABLE T15 MODIFY (C3 NUMBER NOT NULL);

If we ask Oracle Database to count the rows in the table, it should use one of the indexes:

SET AUTOTRACE TRACEONLY EXPLAIN
 
SELECT
  COUNT(*)
FROM
  T15;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 877827156
 
----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |   282   (6)| 00:00:02 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C3 |  1000K|   282   (6)| 00:00:02 |
----------------------------------------------------------------------------

We are able to hint a different execution plan without any trouble:

SELECT /*+ FULL(T15) */
  COUNT(*)
FROM
  T15;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 74626064
 
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5485   (2)| 00:00:23 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T15  |  1000K|  5485   (2)| 00:00:23 |
-------------------------------------------------------------------

Let’s set a hidden initialization parameter to its default value and retry the test SQL statement:

ALTER SESSION SET "_OPTIMIZER_IGNORE_HINTS"=FALSE;
 
SELECT /*+ FULL(T15) */
  COUNT(*)
FROM
  T15;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 74626064
 
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5485   (2)| 00:00:23 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T15  |  1000K|  5485   (2)| 00:00:23 |
-------------------------------------------------------------------

Now let’s try again with a modified value for the parameter:

ALTER SESSION SET "_OPTIMIZER_IGNORE_HINTS"=TRUE;
 
SELECT /*+ FULL(T15) */
  COUNT(*)
FROM
  T15;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 877827156

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |   282   (6)| 00:00:02 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C3 |  1000K|   282   (6)| 00:00:02 |
----------------------------------------------------------------------------

The optimizer ignored the index hint (because I told it to ignore the hint).

Another possibility of Oracle’s optimizer “ignoring” a hint is accomplished using a hacked stored outline, as in this example:

http://hoopercharles.wordpress.com/2009/12/18/tracking-performance-problems-inserting-a-hint-into-sql-in-a-compiled-program/

One might wonder what would happen if a hint was provided that stated to ignore hints (I believe that the recursive dynamic sampling SQL will include this hint). Will the hint to ignore hints be ignored?

ALTER SESSION SET "_OPTIMIZER_IGNORE_HINTS"=FALSE;
 
SELECT /*+ FULL(T15) */
  COUNT(*)
FROM
  T15;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 74626064
 
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  5485   (2)| 00:00:23 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T15  |  1000K|  5485   (2)| 00:00:23 |
-------------------------------------------------------------------

The hint was followed, as expected. Now trying again:

SELECT /*+ IGNORE_OPTIM_EMBEDDED_HINTS FULL(T15) */
  COUNT(*)
FROM
  T15;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 877827156

----------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |   282   (6)| 00:00:02 |
|   1 |  SORT AGGREGATE       |            |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IND_T15_C3 |  1000K|   282   (6)| 00:00:02 |
----------------------------------------------------------------------------

Oracle “ignored” my hint (this may fall under bullet point 3).

2 01 2011
Mohamed

Happy new year,

I just found another situation where a hint can be ignored; I don’t know also where to categorize it.

SQL> select * from v$version where rownum=1;

BANNER                                                                          
--------------------------------------------------------------------------------
Personal Oracle Database 11g Release 11.2.0.1.0 - Production                    

SQL> drop table t;

Table dropped.

SQL> drop table err$_t;

Table dropped.

SQL> CREATE TABLE t (ID NUMBER PRIMARY KEY, text VARCHAR2(10));

Table created.

SQL> INSERT INTO t
  2     SELECT     ROWNUM, 'Test DML'
  3           FROM DUAL
  4     CONNECT BY LEVEL <= 10;  

SQL> commit;

Commit complete.

SQL> exec DBMS_ERRLOG.create_error_log (dml_table_name => 't');

PL/SQL procedure successfully completed.

SQL> select count(1) from t;

  COUNT(1)                                                                      
----------                                                                      
        10                                                                      

SQL> select count(1) from err$_t;

  COUNT(1)                                                                      
----------                                                                      
         0                                                                      

SQL> INSERT      /*+ append */ INTO t
  2     SELECT ROWNUM, 'unique ap'
  3       FROM DUAL
  4  CONNECT BY LEVEL <= 13;
 
INSERT      /*+ append */ INTO t
*
ERROR at line 1:
ORA-00001: unique constraint (TESTUSER.SYS_C0028519) violated 
 
SQL> select count(1) from err$_t;

  COUNT(1)                                                                      
----------                                                                      
         0       

The DML Error logging doesn’t work with direct path load.

But let’s now use the new 11gR2 hint named ignore_row_on_dupkey_index as shown below:

SQL> INSERT      /*+ ignore_row_on_dupkey_index(t(id))
  2                           append
  3                    */
  4   INTO t
  5     SELECT ROWNUM, 'unique ig'
  6       FROM DUAL
  7   CONNECT BY LEVEL <= 13 
  8   LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;
 
SQL> select count(1) from t;

  COUNT(1)                                                                      
----------                                                                      
        13                                                                      

SQL> rollback;

Rollback complete.

SQL> INSERT      /*+ append
  2  			     ignore_row_on_dupkey_index(t(id))
  3                    */
  4   INTO t
  5     SELECT ROWNUM, 'unique ig'
  6       FROM DUAL
  7   CONNECT BY LEVEL <= 13
  8   LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;
 
3 rows created.

SQL> commit;

Commit complete.

The new hint ignore_row_on_dupkey_index makes the insert to silently ignore the hint append and to by passe the duplicate rows and to insert only new rows(3 rows)

Best Regards

Mohamed Houri

Mohamed

(Edited to fix the WordPress HTML problems when it encounters the LEVEL <= syntax)

2 01 2011
Charles Hooper

Mohamed,

Nice example using the unusual hint that is mentioned in this blog article:

http://richardfoote.wordpress.com/2010/12/20/oracle11g-ignore_row_on_dupkey_index-hint-micro-cuts/

I think that your example is a demonstration of the third bullet point: “The hint is incompatible with another hint”. I enabled a 10053 trace for the INSERT statement that used the IGNORE_ROW_ON_DUPKEY_INDEX hint, and found this at the end of the trace file:

Dumping Hints
=============
  atom_hint=(@=000000044B3920D8 err=0 resol=0 <strong>used=0</strong> token=814 org=1 lvl=1 txt=APPEND ())
  atom_hint=(@=00000000216C9020 err=0 resol=1 <strong>used=1</strong> token=1735 org=1 lvl=3 txt=IGNORE_ROW_ON_DUPKEY_INDEX ())

The above suggests that the APPEND hint was not used, and that was confirmed by the execution plan which was printed in the 10053 trace file:

============
Plan Table
============
--------------------------------------------------+-----------------------------------+
| Id  | Operation                       | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------------------+-----------------------------------+
| 0   | INSERT STATEMENT                |         |       |       |     2 |           |
| 1   |  LOAD TABLE CONVENTIONAL        |         |       |       |       |           |
| 2   |   COUNT                         |         |       |       |       |           |
| 3   |    CONNECT BY WITHOUT FILTERING |         |       |       |       |           |
| 4   |     FAST DUAL                   |         |     1 |       |     2 |  00:00:01 |
--------------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
 
Content of other_xml column
===========================
  db_version     : 11.2.0.1
  parse_schema   : TESTUSER
  plan_hash      : 1731520519
  plan_hash_2    : 2585177905
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      DB_VERSION('11.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "T"@"INS$1")
      NO_CONNECT_BY_FILTERING(@"SEL$1")
      CONNECT_BY_COMBINE_SW(@"SEL$1")
    END_OUTLINE_DATA
  */

I experimented a little with your test case.

CREATE TABLE t (ID NUMBER, text VARCHAR2(10));
 
INSERT INTO t
 SELECT     ROWNUM, 'Test DML'
   FROM DUAL
CONNECT BY LEVEL <= 10;  
 
commit;

So I now have a table without a primary key constraint. Let’s try the test SQL statement (the full test script followed by the output below the line /* The output */):

INSERT      /*+ append
		     ignore_row_on_dupkey_index(t)
               */
INTO t
  SELECT ROWNUM, 'unique ig'
    FROM DUAL
  CONNECT BY LEVEL <= 13
  LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;

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

/* The output */
                  *
ERROR at line 8:
ORA-38912: An index must be specified in the index hint

OK, that did not work since I do not have an index on the ID column, and the optimizer did not just use the APPEND hint and ignore the IGNORE_ROW_ON_DUPKEY_INDEX hint. Let’s try again:

CREATE INDEX IND_T_ID ON T(ID);

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

INSERT      /*+ append
		     ignore_row_on_dupkey_index(t IND_T_ID)
               */
INTO t
  SELECT ROWNUM, 'unique ig'
    FROM DUAL
  CONNECT BY LEVEL <= 13
  LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;

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

/* The output */
                  *
ERROR at line 8:
ORA-38913: Index specified in the index hint is invalid

That did not work either. Let’s try again, this time with a unique index:

DROP INDEX IND_T_ID;
 
CREATE UNIQUE INDEX IND_T_ID ON T(ID);

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

INSERT      /*+ append
		     ignore_row_on_dupkey_index(t IND_T_ID)
               */
INTO t
  SELECT ROWNUM, 'unique ig'
    FROM DUAL
  CONNECT BY LEVEL <= 13
  LOG ERRORS INTO err$_t REJECT LIMIT UNLIMITED;

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

/* The output */
3 rows created.

In short, it appears that the IGNORE_ROW_ON_DUPKEY_INDEX hint is one index that refuses to be ignored. :-)

Thank you for taking the time to put together the test case.

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 140 other followers

%d bloggers like this: