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

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

Kevin Zou

2012-4-10

在Oracle 11.2.0.1 版本中心加入的3个提示，CHANGE_DUPKEY_ERROR_INDEX, IGNORE_ROW_ON_DUPKEY_INDEX 和 RETRY_ON_ROW_CHANGE 。

IGNORE_ROW_ON_DUPKEY_INDEX 已经做了介绍，这篇文章主要是说明剩下 CHANGE_DUPKEY_ERROR_INDEX。

CHANGE_DUPKEY_ERROR_INDEX 

一般情况下，如果插入时遇到唯一键或者主键冲突，SQL会返回ORA-00001的错误信息。

SQL> create table test (id int primary  key, name varchar2(10));

Table created.

SQL> insert into test values (1,'TEST');

1 row created.

SQL> insert into test values (1,'TEST2');

insert into test values (1,'TEST2')

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.SYS_C0065960) violated

SQL> alter table test add constraint test_uk unique(name);

Table altered.

SQL> insert into test values(2,'TEST');

insert into test values(2,'TEST')

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.TEST_UK) violated

加上CHANGE_DUPKEY_ERROR_INDEX ，会让错误提示号从ORA-00001改为ORA-38911。至于是提示哪个唯一键冲突，就需要看在表名称后的字段名称。

SQL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(id)) */ into test values(1,'TEST2

');

insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(id)) */ into test values(1,'TEST2')

*

ERROR at line 1:

ORA-38911: unique constraint (SYS.SYS_C0065960) violated

SQL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(name)) */ into test values(2,'TES

T');

insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(name)) */ into test values(2,'TEST')

*

ERROR at line 1:

ORA-38911: unique constraint (SYS.TEST_UK) violated

如果在HINT中表名称的字段和发生冲突的字段不一致，那ORACLE还是提示ORA-00001的错误。

这个SQL明显是ID字段发生冲突，而HINT中的字段为 NAME，不一致。

SQL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(name)) */ into test values(1,'TES

T2');

insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(name)) */ into test values(1,'TEST2')

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.SYS_C0065960) violated

理由同上。

SQL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(id)) */ into test values(2,'TEST'

);

insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(id)) */ into test values(2,'TEST')

*

ERROR at line 1:

ORA-00001: unique constraint (SYS.TEST_UK) violated

这个提示只能应用在INSERT/UPDATE语句中。

有两种语法：

1） 第一种就是 /*+ CHANGE_DUPKEY_ERROR_INDEX（table_name, index_name) */

2） 第二种就是  /*+ CHANGE_DUPKEY_ERROR_INDEX（table_name(column_name)) */

如果指定索引，那该索引必须存在而且是唯一索引；如果指定了字段，那引用了该字段的唯一索引必须存在。

如果索引不是唯一索引，那会报ORA-38913错误。

SQL> select index_name,index_type,UNIQUENESS from dba_indexes where table_name='

TEST';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

------------------------------ --------------------------- ---------

SYS_C0065960                   NORMAL                      UNIQUE

TEST_UK                        NORMAL                      UNIQUE

SQL> alter table test drop constraint test_uk;

Table altered.

SQL> create index ind_test_name on test(name);

Index created.

SQL> alter table test add constraint test_uk unique (name);

Table altered.

SQL> select index_name,index_type,UNIQUENESS from dba_indexes where table_name='

TEST';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES

------------------------------ --------------------------- ---------

SYS_C0065960                   NORMAL                      UNIQUE

IND_TEST_NAME                  NORMAL                      NONUNIQUE

这时字段NAME所在的索引已经由UNIQUE变为NONUNIQUE，再次运行相同的SQL会报ORA-38913的错误：

SQL> insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(name)) */ into test values(2,'TES

T');

insert /*+ CHANGE_DUPKEY_ERROR_INDEX(test(name)) */ into test values(2,'TEST')

                                                         *

ERROR at line 1:

ORA-38913: Index specified in the index hint is invalid

这个HINT在一般的场景都是个鸡肋HINT，如果仅仅为改变错误提示号而去用这个HINT，那没有明显的收益啊。

老杨说到CHANGE_DUPKEY_ERROR_INDEX和GNORE_ROW_ON_DUPKEY_INDEX都是Oracle为了在线版本升级而设计的。估计只有在这个场景才发挥作用。

-THE END-

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40239/viewspace-720800/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40239/viewspace-720800/