jump to navigation

Oracle11g IGNORE_ROW_ON_DUPKEY_INDEX Hint (Micro Cuts) December 20, 2010

Posted by Richard Foote in 11g, 11g New features, Oracle Indexes.
trackback

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 …

About these ads

Comments»

1. Tony - December 20, 2010

This is pretty interesting, but based on the limitations with the update statement I think I like the DML error logging feature better. Of course there is a table to “maintain” and you have to explicitly create that table, but it handles more situations and doesn’t require that you know information like the index name.

Richard Foote - December 21, 2010

Hi Tony

Yes, I know what you mean. It’s another way of skinning the cat, which in some cases might be a more convenient way, in other cases far less so.

Houri Mohamed - December 23, 2010

Yes but there exist a limit for the DML error logging with unique keys when using direct path load insert (/*+ append */).

This is why, if, the new hint when combined with the append hint, can allow me to use DML error logging it will be great.

This is what I will testing this afternoon

Regards

Mohamed Houri

2. Flado - December 20, 2010

Is it fair to say that the hint behaviour is independent of the constraint, only on the index?
In other words, if I only have a unique index and no constraint of any sort, would the hint still work? (Sorry, no 11g at hand to test myself)

Richard Foote - December 21, 2010

Hi Flado

Indeed yes it is fair to say so. It’s the index that’s important here, not the constraint although it’s in my blood to define uniqueness via a constraint that just through a unique index.

If the constraint wasn’t define, just the unique index, the hint would work as I described.

3. SYSDBA - December 21, 2010
Richard Foote - December 21, 2010

Thanks SYSDBA.

I don’t know from where, but I have the feeling I’ve heard of you before ;)

4. Ogan Ozdogan - December 21, 2010

That is an interesting new Oracle hint and i bet it will be used frequently to avoid the unique constraint violation errors.
The unique and non-unique index illustrations are also very handy.

Richard Foote - December 21, 2010

Hi Ogan

Thanks, glad it was handy for you :)

5. Uwe Hesse - December 22, 2010

Very interesting bit of research upon which I probably wouldn’t have stumbled so fast without your Blog. Always worth a click to watch it. Thank you for sharing, Richard!

Richard Foote - January 25, 2011

Hi Uwe

No worries :)

6. Noons - December 24, 2010

ummm, I can’t say I like this at all. First I heard of it a while ago, it didn’t sound very good at all and I must admit it still doesn’t.
Hints were merely optimizer related until now. If they start to affect how a statement behaves in terms of the data processing, we got a big can of worms coming up…

Richard Foote - January 25, 2011

Hi Noons

Long time, no hear :)

It’s just another way of making oracle do what you want it to do.

One way to look at it is that if you specifically state you want Oracle to ignore duplicates by applying the hint, but it can’t because (say) the wrong type of index is defined on the column, is it better to get the error when you effectively compile the statement or for Oracle to ignore the hint only to then get the error when one attempts to insert duplicate errors at run time.

One is going to get the error so it might be better to get the error sooner rather than later when it might be possible to do something about it.

If one knows how the hints work (and don’t work), then I don’t really have an issue with this new functionality.

7. coskan gundogar - January 3, 2011

Good news for the application developers is that _optimizer_ignore_hints parameter do not effect these semantic hints which means you can still use this parameter to get bypass optimizer hints with new releases without having an effect on this semantic hint.

SYS@ORACOS> @pd _optimizer_ignore_hints

NAME                                     VALUE                          DESCRIPTION
---------------------------------------- ------------------------------ -------------------------------------------------------
_optimizer_ignore_hints                  TRUE                           enables the embedded hints to be ignored

SYS@ORACOS> insert /*+ ignore_row_on_dupkey_index(radiohead,radiohead_pk_i) */ into hr.radiohead select rownum, 'OK COMPUTER' from dual connect by level <= 14;

2 rows created.

Richard Foote - January 25, 2011

Hi Coskan

Thanks, good to know :)

8. Mark Brady - February 4, 2011

As a performance move, I’ll sometime add the “code” column to the PK index on “ID” in order to avoid a table access. That’s a relatively safe move but now I’ll have to worry that someone’s code has a hint in it that will break. And it’s probably in the quarterly load… so my Real Application workload doesn’t contain that… I like the concept, I strongly dislike that this is index based and not constraint based.

9. Bitten by a Virtual Column, _OPTIMIZER_IGNORE_HINTS Doesn’t Ignore Hints? | Charles Hooper's Oracle Notes - March 10, 2013

[…] Database offers more than 200 hints to help control the query optimizer’s behavior (as well as other behavior).  In general, hints should be used sparingly.  For example, we are able to add a FULL hint to […]

10. Book Review: Troubleshooting Oracle Performance, Second Edition | Charles Hooper's Oracle Notes - July 16, 2014

[…] the hints are specified incorrectly.  Pg 365 (a similar comment is made at the top of page 371). (Reference) (Reference […]


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 1,913 other followers

%d bloggers like this: