Oracle11gr2新增提示IGNORE_ROW_ON_DUPKEY_INDEX

在11.2中，Oracle新增了一個HINT，使得INSERT INTO SELECT操作可以避免插入重複的記錄。

 

 

首先建立一個包含主鍵的表：

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

表已建立。

SQL> INSERT INTO T
  2  SELECT ROWNUM, OBJECT_NAME
  3  FROM DBA_OBJECTS
  4  WHERE ROWNUM <= 200;

已建立200行。

SQL> SELECT *
  2  FROM T
  3  WHERE ID = 100;

        ID NAME
---------- ------------------------------
       100 FIXED_OBJ$

下面清空表中資料，然後插入一條記錄：

SQL> TRUNCATE TABLE T;

表被截斷。

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

已建立 1 行。

SQL> COMMIT;

提交完成。

下面執行普通的INSERT INTO SELECT語句，會由於重複記錄的存在而報錯：

SQL> INSERT INTO T
  2  SELECT ROWNUM, OBJECT_NAME
  3  FROM DBA_OBJECTS
  4  WHERE ROWNUM <= 200;
INSERT INTO T
*
第 1 行出現錯誤:
ORA-00001: 違反唯一約束條件 (YANGTK.PK_T)

透過使用新增的提示，可以避免插入重複的記錄：

SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(T, PK_T) */
  2  INTO T
  3  SELECT ROWNUM, OBJECT_NAME
  4  FROM DBA_OBJECTS
  5  WHERE ROWNUM <= 200;

已建立199行。

除了指定索引名稱外，這個HINT還有另一種寫法，指定主鍵列：

SQL> ROLLBACK;

回退已完成。

SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(T(ID)) */
  2  INTO T
  3  SELECT ROWNUM, OBJECT_NAME
  4  FROM DBA_OBJECTS
  5  WHERE ROWNUM <= 200;

已建立199行。

SQL> COMMIT;

提交完成。

SQL> SELECT *
  2  FROM T
  3  WHERE ID = 100;

        ID NAME
---------- ------------------------------
       100 TEST

如果不關聯當前表的話，這個功能只能透過PL/SQL實現，而現在可以透過加上IGNORE_ROW_ON_DUPKEY_INDEX的一條INSERT語句實現。