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/，如需轉載，請註明出處，否則將追究法律責任。

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

相關文章

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