Oracle11gr2新增提示CHANGE_DUPKEY_ERROR_INDEX

在11.2中，Oracle新增了一個HINT，用於改變鍵值重複時的報錯資訊。

正常情況下，如果主鍵衝突，則返回錯誤號為ORA-00001，而在11.2中，Oracle提供了改變錯誤號的方法，使用這個CHANGE_DUPKEY_ERROR_INDEX提示，指定的索引違反唯一約束後，返回的錯誤為ORA-38911。

SQL> CREATE TABLE T       
  2  (ID NUMBER PRIMARY KEY,
  3  NAME VARCHAR2(30));

表已建立。

SQL> INSERT INTO T VALUES (1, 'TEST');

已建立 1 行。

SQL> INSERT INTO T VALUES (1, 'ABC');
INSERT INTO T VALUES (1, 'ABC')
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (TEST.SYS_C0011145)

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011145) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011145) */ INTO T
*
第 1 行出現錯誤:
ORA-38911: 違反唯一約束條件 (TEST.SYS_C0011145)

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
*
第 1 行出現錯誤:
ORA-38911: 違反唯一約束條件 (TEST.SYS_C0011145)

提示CHANGE_DUPKEY_ERROR_INDEX有兩種用法，一種是指定索引的名稱，另一種是指明所有構成索引的列。

需要注意的是，這個HINT只對唯一索引生效，而對唯一約束無效：

SQL> ALTER TABLE T DROP PRIMARY KEY;

表已更改。

SQL> CREATE INDEX IND_T_NON_UNI ON T(ID);

索引已建立。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T(ID)) */ INTO T
                                                    *
第 1 行出現錯誤:
ORA-38913: 索引提示中指定的索引無效

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, IND_T_NON_UNI) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, IND_T_NON_UNI) */ INTO T
                                                               *
第 1 行出現錯誤:
ORA-38913: 索引提示中指定的索引無效

SQL> SELECT CONSTRAINT_NAME
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'T'
  4  AND CONSTRAINT_TYPE = 'P';

CONSTRAINT_NAME
------------------------------
SYS_C0011146

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
  2  VALUES (1, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
                                                              *
第 1 行出現錯誤:
ORA-38913: 索引提示中指定的索引無效

如果ORA-00001錯誤是唯一約束引發的，而不是唯一索引導致的，則執行SQL報錯ORA-38913。需要注意的是，這裡的錯誤和使用HINT不一樣，使用HINT是執行的時候將碰到的ORA-00001錯誤轉化為ORA-38911錯誤，而這裡的錯誤實際上是語法錯誤，SQL根本就沒有執行。

SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
  2  VALUES (2, 'ABC');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(T, SYS_C0011146) */ INTO T
                                                              *
第 1 行出現錯誤:
ORA-38913: 索引提示中指定的索引無效

SQL> INSERT INTO T
  2  VALUES (2, 'ABC');

已建立 1 行。

如果沒有HINT，則這個語句可以成功，但是由於指定了不唯一或不存在的索引，導致SQL語句直接報錯。

也行有人會置疑這個HINT的作用，事實上這個HINT和前不久介紹的另一個HINT：IGNORE_ROW_ON_DUPKEY_INDEX都是Oracle為了線上版本升級而設計的。因此這裡就不詳細描述HINT的作用了，在描述版本升級的時候會描述如何利用這個HINT的。

這裡要說明的不僅僅是這個HINT的用法，還有Oracle的可控性。連Oracle的返回錯誤號都可以透過HINT來修改，Oracle的靈活性可見一斑。