Insert with IGNORE_ROW_ON_DUPKEY_INDEX on Index Organized Table might result in lost data

1. We create table red_table with PK on both columns ID1, ID2 with 10000 rows
3. We create table delta_table with PK on both columns ID1, ID2 with only 500 rows like in red_table (first 500 rows)
3. We load additional 100 completely new/different rows into delta_table
4. So as of now we have 600 rows in delta_table and 10000 in red_table. The 100 rows are completely different (one can use A MINUS B to check that)
5. Try to load rows from delta_table into red_table using +IGNORE_ROW_ON_DUPKEY_INDEX hint that do NOT yet exists in red_table.
HEAP) 100 rows inserted, 2nd insert adds 0 rows (correct)
IOT) 60 rows inserted, WTF/BUG due to 40 not loaded rows!
6. “analyze table RED_TABLE validate structure cascade” returns no errors so we have no corruption (in both HEAP and IOT cases)
7. So as of now we have
HEAP) 10100 rows in red_table, and delta_table MINUS red_table returns 0 (no differences)
IOT) 10060 rows in red_table, and delta_table MINUS red_table returns 40 (missing data!)

--1
drop table red_table purge;
create table red_table (id1 number, id2 number, constraint RED_TABLE_PK primary key(id1, id2)) organization index;
insert into red_table (id1, id2) select object_id, object_id from all_objects where rownum <= 10000;
commit;

--2 & 3
drop table delta_table;
create table delta_table (id1, id2) as select * from red_table where rownum <= 500;
insert into delta_table (id1, id2) select 10000000+object_id, object_id from all_objects where rownum <= 100;
commit;

-- 4
select (select count(*) from delta_table) delta, (select count(*) from red_table) red from dual;
--
select count(*) from (
select * from delta_table
minus
select * from red_table
);

-- 5
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(RED_TABLE, RED_TABLE_PK) */ into red_table (id1, id2) select id1, id2 from delta_table;
commit;

-- 6
analyze table RED_TABLE validate structure cascade;

-- 7
select (select count(*) from delta_table) delta, (se1lect count(*) from red_table) from dual;

select count(*) from (
select * from delta_table
minus
select * from red_table
);

Reproduced on versions:
- 11.2.0.3.13
- 11.2.0.4.2
- reproduced also with primary key being only ID1 (so this is NOT related to the composite PK on ID1+ID2 on IOT)

Bugs possibly linked to this issue:

  • Bug 17397545 ORA-600 [kdtigetrow-2] / buffer cache errors from MERGE statement with LOG ERRORS INTO
  • Bug 11865420 Insert as Select with LOG ERRORS INTO slower than expected / corruption using IGNORE_ROW_ON_DUPKEY_INDEX hint – superseded
  • Bug 14512508 : PROBLEM WITH HINT “IGNORE_ROW_ON_DUPKEY_INDEX”
  • Bug 17748658 : BUG 11865420 IS MARKED AS FIXED IN 11.2.0.4 BUT ISN’T REALLY FIXED
  • Bug 13033715 : WRONG RESULTS WITH HINT IGNORE_ROW_ON_DUPKEY_INDEX

Summary: do not use IGNORE_ROW_ON_DUPKEY_INDEX hint . Another interesting story is documented here http://guyharrison.squarespace.com/blog/2010/1/1/the-11gr2-ignore_row_on_dupkey_index-hint.html where it is clear (due to performance reasons) it is best avoid IGNORE_ROW_ON_DUPKEY_INDEX hint.

Comments are closed.