11gr2 IGNORE_ROW_ON_DUPKEY_INDEX hint bug

brotherxiao — Sun, 27 Sep 2009 15:48:16 +0800

Oracle 11gR2 New feature中引入了IGNORE_ROW_ON_DUPKEY_INDEX hint以解决insert …select 碰到的唯一性冲突问题，测试了一把，没有成功。看来新功能还有带检验。

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Real Application Testing options

SQL> show user
USER is "SYS"

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select count(*) from t1;

COUNT(*)
----------
65690

SQL> insert into t1 select * from t1 where rownum<10000;

9999 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

COUNT(*)
----------
75689

SQL> create table t2 as select * from t1 where 1=0;

Table created.

SQL> alter table t2 add primary key(object_id);

Table altered.

SQL> insert into t2 select * from t1;
insert into t2 select * from t1
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C007007) violated

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ into t2 select * from t1;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ into t2 select * from t1
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C007007) violated

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2
select * from t1;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2 select * from t1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerltcInsertSelectRop_bad_state],
[], [], [], [], [], [], [], [], [], [], []

SQL> insert into t2 select * from dba_objects;

65692 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2
select * from t1;
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(t2,SYS_C007007) */ into t2
select * from t1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qerltcInsertSelectRop_bad_state],

[], [], [], [], [], [], [], [], [], [], []

Brotherxiao's Home

11gr2 IGNORE_ROW_ON_DUPKEY_INDEX hint bug

Oracle 11gR2 New feature中引入了IGNORE_ROW_ON_DUPKEY_INDEX hint以解决insert …select 碰到的唯一性冲突问题，测试了一把，没有成功。看来新功能还有带检验。