IGNORE_ROW_ON_DUPKEY_INDEX Hint

This is something a colleague asked about the other day and it is a very interesting Oracle hint.

The IGNORE_ROW_ON_DUPKEY_INDEX hint is for single table INSERT statements and, when used, will cause the statement to ignore unique key violations for the specified index.

Create a test table

-- Create a table using mod 2
-- goal is to have no values ending in 5 (5, 15, 25, ...)

create table t1 as
select id, name
from   (
       select rownum id, rpad( 'x', 10, 'x' ) name
       from   dual
       connect by rownum <= 100
       )
where  mod( id, 2 ) = 0;

Attempt to insert values ending in 5 (5, 15, 25, …)

insert into t1
select id, name
from (
select rownum id, rpad( 'x', 10, 'x' ) name
from dual
connect by rownum < 100
)
where mod( id, 5 ) = 0;

The above code will fail with ORA-00001: unique constraint (T1.PK) violated

Syntax for IGNORE_ROW_ON_DUPKEY_INDEX

The unique index violation can be ignored by either referencing the index by name, or by referencing the columns that are in the unique index. Either of the two version below work the same.

insert /*+ ignore_row_on_dupkey_index( t1, (id) )*/ into t1

insert /*+ ignore_row_on_dupkey_index( t1, t1_pk )*/ into t1

Run the INSERT again with the hint

The code below works exactly as advertised and will insert 10 rows. There are 20 rows in the result set, but 10 of them will violate the unique constraint allowing the remaining 10 to be inserted into the table.

insert /*+ ignore_row_on_dupkey_index( t1, t1_pk )*/ into t1
select id, name
from   (
select rownum id, rpad( 'x', 10, 'x' ) name
from   dual
connect by rownum <= 100
)
where  mod( id, 5 ) = 0;
commit;

Follow-up

If you are running Oracle versions below 12.1.0.1 you will want to look at the following bugs to see if you are affected.
Bug 9004390 (Doc ID 9004390.8)
Bug 11865420 (Doc ID 11865420.8)

Post a Comment