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

kewin發表於2012-04-10
ErrorIndex
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章