Oracle11g IGNORE_ROW_ON_DUPKEY_INDEX Hint (Micro Cuts)

An interesting new hint was introduced in Oracle11g which provides an alternative approach when inserting data where duplicate values might be an issue.
 
To illustrate, I’m going to create a little table with just the 10 rows with a unique ID column containing values 1 – 10 policed by a Unique index:

 

SQL> create table radiohead (id number constraint radiohead_pk_i primary key using index (create unique index radiohead_pk_i on radiohead(id)), name varchar2(20));
 
Table created.
 
SQL> select index_name, uniqueness, table_name from dba_indexes where index_name='RADIOHEAD_PK_I';
 
INDEX_NAME                     UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
RADIOHEAD_PK_I                 UNIQUE    RADIOHEAD
 
SQL> insert into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 10;
 
10 rows created.
 
SQL> commit;
 
Commit complete.

 
 

If we now attempt to add 12 more rows, but including the values 1 – 10:

 
SQL> insert into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 12;
insert into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 12
*
ERROR at line 1:
ORA-00001: unique constraint (BOWIE.RADIOHEAD_PK_I) violated

  
We obviously get a unique constraint violation error.
 
However, Oracle11g allows us to use the IGNORE_ROW_ON_DUPKEY_INDEX hint, which will silently deal with the unique constraint violation errors by simply ignoring and not inserting any row in which the unique values already exist in the table.
 
The hint comes in 2 formats, the first allows us to specify the unique index which contains the unique values to be ignored:
 

 
SQL> insert /*+ ignore_row_on_dupkey_index(radiohead,radiohead_pk_i) */ into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 12;
 
2 rows created.
 
SQL> commit;
 
Commit complete.

 
 

  
Note the 10 duplicate values (1 – 10) have been ignored and have not been inserted into the table but the values 11 and 12 which didn’t previously exist have successfully been inserted into the table.
 
Here we attempt to insert values 1 – 13 into the table, although now values 1 – 12 currently already exist. This time, we’ll use the second format of the hint which allows us to stipulate the column which contains unique values which are to be ignored if they already exist:
 

 
SQL> insert /*+ ignore_row_on_dupkey_index(radiohead(id)) */ into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 13;
 
1 row created.
 
SQL> commit;
 
Commit complete.
 

SQL> select * from radiohead;
 
        ID NAME
---------- --------------------
         1 OK COMPUTER
         2 OK COMPUTER
         3 OK COMPUTER
         4 OK COMPUTER
         5 OK COMPUTER
         6 OK COMPUTER
         7 OK COMPUTER
         8 OK COMPUTER
         9 OK COMPUTER
        10 OK COMPUTER
        11 OK COMPUTER
        12 OK COMPUTER
        13 OK COMPUTER
 
13 rows selected.

 
 

Note in this case, the values 1 – 12 have all been silently ignored with just the value 13 inserted this time into the table.
 
You can’t however use this hint within an update statement …

 
SQL> update /*+ ignore_row_on_dupkey_index(radiohead,radiohead_pk_i) */ radiohead set id = 13 where id = 3;
update /*+ ignore_row_on_dupkey_index(radiohead,radiohead_pk_i) */ radiohead set id = 13 where id = 3
*
ERROR at line 1:
ORA-38917: IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation

 
 

Interesting difference in the behaviour of this hint. Usually “invalid” hints are just ignored and treated as comments but here if an illegal operation is attempted with the use of this “hint”, an error is invoked.
 
Going to now set up the same demo again, but this time police the Primary Key constraint via a Non-Unique index:

 
SQL> drop table radiohead;
 
Table dropped.
 
SQL> create table radiohead (id number constraint radiohead_pk_i primary key using index (create index radiohead_pk_i on radiohead(id)), name varchar2(20));
 
Table created.
 
SQL> insert into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 10;
 
10 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select index_name, uniqueness, table_name from dba_indexes where index_name='RADIOHEAD_PK_I';
 
INDEX_NAME                     UNIQUENES TABLE_NAME
------------------------------ --------- ------------------------------
RADIOHEAD_PK_I                 NONUNIQUE RADIOHEAD
 

SQL> insert /*+ ignore_row_on_dupkey_index(radiohead,radiohead_pk_i) */ into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 12;
insert /*+ ignore_row_on_dupkey_index(radiohead,radiohead_pk_i) */ into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 12
                                                                        *
ERROR at line 1:
ORA-38913: Index specified in the index hint is invalid
 

SQL> insert /*+ ignore_row_on_dupkey_index(radiohead(id)) */ into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 13;
insert /*+ ignore_row_on_dupkey_index(radiohead(id)) */ into radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 13
                                                             *
ERROR at line 1:
ORA-38913: Index specified in the index hint is invalid

 
 

Note again an error is invoked here as well as this hint can only be applied via a constraint policed via a Unique index. A Non-Unique index, even with a Unique or PK constraint in place is not sufficient and will generate the above error.
 
Yet another reason to use Unique indexes to police constraints whenever possible and practical …