11G 新特性： 新加的提示IGNORE_ROW_ON_DUPKEY_INDEX

kewin發表於2012-03-28
Index
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-

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/40239/viewspace-719747/，如需轉載，請註明出處，否則將追究法律責任。

相關文章