11G 新特性： 新加的提示IGNORE_ROW_ON_DUPKEY_INDEX
Kevin Zou
2012-3-28
在做INSERT INTO TARGET...SELECT...FROM SOURCE 操作时，遇到原先TARGET 中已有的记录因违反唯一键而插入失败。 新加的提示IGNORE_ROW_ON_DUPKEY_INDEX 就可以完全避免这种问题。
例子：
SQL> create table test as select * from dba_objects where object_id < 1000;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
942
SQL> alter table test add primary key (object_id);
Table altered.
SQL> delete test where object_id <> 999;
941 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
1
SQL> insert into test select * from dba_objects where object_id < 1000;
insert into test select * from dba_objects where object_id < 1000
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C0065959) violated
SQL> select count(*) from test;
COUNT(*)
----------
1
SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(test(object_id)) */ into test select
* from dba_objects where object_id < 1000;
941 rows created.
SQL> select count(*) from test;
COUNT(*)
----------
942
如果不加上这个HINT，那就需要用PL/SQL编程来实现；
SQL> rollback;
Rollback complete.
SQL> insert into test select a.* from dba_objects a, test b where a.object_id
< 1000 and a.object_id <> b.object_id;
941 rows created.
-THE END-