Oracle hint ignore_row_on_dupkey_index - part 1

At this year’s DOAG Conference, I had a session called “Think simple and space yourself a facepalm”. In one of examples, we were discussing usage of MERGE in particular situation. We came with a good solution using set operation (MINUS). After that I suggested another idea which would simplify given SQL even more. It involved usage of hint ignore_row_on_dupkey_index.

For those of you who are not familiar with this hint, you are basically telling Oracle:” Hey Oracle! I’ll fire this INSERT and I want you to ignore any rows, which will fail on ORA-0001”. So in principle, if you have a unique index to check against, you don’t need to check whether particular row adheres to your unique constraint before you try to insert it.

Example of usage looks like this (table1_pk is unique index):

insert /*+ ignore_row_on_dupkey_index(table1, table1_pk) */ into table1 select * from table2;

Now there are certain properties you have to be aware of, when using this hint.

1. If you have on target table BEFORE INSERT FOR EACH ROW trigger, it will (of course) fire for ALL rows. So for example if you are logging DML operations that way, you’ll log inserts of rows, which will not appear in table
2. At the end of the session I’ve got a question if sql%rowcount counts rows processed or only those rows, which are inserted in table. I haven’t really tested that, so now I did. It counts only rows which are inserted, so it works fine.

Now we have got to the point why I’m writing this blog post in first place. There is one last thing I’ve forgot to mention on that slide – It’s elegant but very slow.

Let me show you very simple example.

I have two tables:

• table1 with 100 000 rows 
• table2 with 199 001 rows from which 99 001 have same primary key value like rows in table1

Now let’s run a simple test:

SQL> set timing on;
SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.08

SQL> insert into table1 select * from table2 t2 where not exists (select null from table1 t1 where t1.id = t2.id);
100000 rows created.
Elapsed: 00:00:01.85

SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.18

SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.82

SQL> insert /*+ ignore_row_on_dupkey_index(table1, table1_pk) */ into table1 select * from table2;
100000 rows created.
Elapsed: 00:01:27.96

SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.08

You heard me saying that “time is just a hint” that we should always compare exact measures, like logical reads or memory. But this is simply too obvious. Anyway, just to be sure, let’s trace it:

insert into table1 select * from table2 t2 where not exists<br />   (select null from table1 t1 where t1.id = t2.id) call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.03       0.03        986        139          1           0 Execute      1      2.40       2.40        733     128970       4399      100000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2      2.44       2.44       1719     129109       4400      100000 insert /*+ rule ignore_row_on_dupkey_index(table1, table1_pk) */ into table1 select * from table2 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1     44.76      87.52          1      99881    1056764      100000 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        2     44.76      87.52          1      99881    1056764      100000

1 056 764 current reads? Something smells really bad here. Let’s find out in next part, what it is ...

Oracle hint ignore_row_on_dupkey_index - part 2