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 …
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.
LikeLike
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.
LikeLike
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
LikeLike
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)
LikeLike
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.
LikeLike
Here is the link to the Oracle Doc: http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF30052
and here’s a good link about these “hints”: http://rwijk.blogspot.com/2009/10/three-new-hints.html
LikeLike
Thanks SYSDBA.
I don’t know from where, but I have the feeling I’ve heard of you before 😉
LikeLike
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.
LikeLike
Hi Ogan
Thanks, glad it was handy for you 🙂
LikeLike
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!
LikeLike
Hi Uwe
No worries 🙂
LikeLike
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…
LikeLike
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.
LikeLike
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.
LikeLike
Hi Coskan
Thanks, good to know 🙂
LikeLike
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.
LikeLike
[…] 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 […]
LikeLike
[…] the hints are specified incorrectly. Pg 365 (a similar comment is made at the top of page 371). (Reference) (Reference […]
LikeLike
[…] See this example by Richard Foote about the hint: https://richardfoote.wordpress.com/2010/12/20/oracle11g-ignore_row_on_dupkey_index-hint-micro-cuts/ […]
LikeLike