ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 11G 新特性: 新加的提示IGNORE_ROW_ON_DUPKEY_INDEX

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

原创 Linux操作系统 作者:kewin 时间:2012-03-28 16:06:00 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-03-10

  • 博文量
    125
  • 访问量
    567373