Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX

insert提示IGNORE_ROW_ON_DUPKEY_INDEX

在 insert into tablea ...select * from tableb中，如果存在唯一約束，會導致整個insert操作失敗。使用IGNORE_ROW_ON_DUPKEY_INDEX提示，會忽略唯一約束衝突，回滾當前行，繼續完成其他行的插入。

1，注意：
（1）如下三個提示CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX, RETRY_ON_ROW_CHANGE與其他提示不同，特別之處在於存在“語義效果（semantic effect）”。
“semantic effect”指在違反以下規則時該提示會導致錯誤資訊:
*如果指定了索引名稱，該索引必須存在且唯一；否則會導致ORA-38913錯誤；
*如果指定索引，必須指定一個索引。如果未指定索引，會導致ORA-38912錯誤；如果指定了多個索引，會導致ORA-38915錯誤。
*不能同時指定CHANGE_DUPKEY_ERROR_INDEX和IGNORE_ROW_ON_DUPKEY_INDEX提示；否則會導致ORA-38915錯誤。

（2）與其他提示相同，存在語法錯誤的提示將被忽略.

（3）該提示僅適用於單個表的insert操作。

2，測試：
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as
 
SQL>
SQL> drop table test;
 
Table dropped
SQL> create table test(x int,y int);
 
Table created
SQL> insert into test values(1,1);
 
1 row inserted
SQL> insert into test values(2,2);
 
1 row inserted
SQL> insert into test values(3,3);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> create unique index uidx_test_x on test(x);
 
Index created
SQL> drop table test2;
 
Table dropped
SQL> create table test2
  2  as
  3  select * from test;
 
Table created
SQL> insert into test2 values(4,4);
 
1 row inserted
SQL> insert into test2 values(5,5);
 
1 row inserted
SQL> commit;
 
Commit complete
SQL> insert into test
  2  select * from test2;
 
insert into test
select * from test2
 
ORA-00001: 違反唯一約束條件 (MH.UIDX_TEST_X)
SQL> rollback;
 
Rollback complete
SQL> insert into test
  2  select * from test2;
 
insert into test
select * from test2
 
ORA-00001: 違反唯一約束條件 (MH.UIDX_TEST_X)
SQL> commit;
 
Commit complete
SQL> insert /*+  IGNORE_ROW_ON_DUPKEY_INDEX(test(x)) */into test
  2  select * from test2;
 
2 rows inserted
SQL> commit;
 
Commit complete
 
SQL> select * from test;
 
                                      X                                       Y
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      2                                       2
                                      3                                       3
                                      4                                       4
                                      5                                       5
 
SQL>

SQL>
SQL> insert /*+  IGNORE_ROW_ON_DUPKEY_INDEX(test,uidx_test_x) */into test
  2  select * from test2;
 
0 rows inserted
SQL> commit;
 
Commit complete