Oracle 重複資料查找與清除三大法：ROWID、GROUP BY、EXCEPTIONS

建立唯一索引失敗：ORA-01452 經典場景

ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

嘗試對 code_ref 表建立唯一索引時發現重複鍵值。必須先識別並清除重複資料才能建立索引。本文介紹三種定位重複列的方法。

方法一：自關聯 + ROWID（經典高效）

Oracle 每列擁有唯一 ROWID，利用 MAX(ROWID) 或 MIN(ROWID) 保留一筆、刪除其餘：

DELETE FROM code_ref a
WHERE ROWID < (SELECT MAX(ROWID)
               FROM code_ref b
               WHERE b.key_col1 = a.key_col1
                 AND b.key_col2 = a.key_col2
                 ...);

優點：直觀、效能好、適用大多數場景。

方法二：GROUP BY / HAVING（集合思維）

SELECT key_col1, key_col2, COUNT(*)
FROM code_ref
GROUP BY key_col1, key_col2
HAVING COUNT(*) > 1;

先查出重複鍵值組合，再針對性刪除。適合先分析重複分布再決定清理策略。

方法三：EXCEPTIONS INTO 子句（約束驗證視角）

1. 執行 @$ORACLE_HOME/rdbms/admin/utlexcpt.sql 建立 EXCEPTIONS 表（NT: ORACLE_HOME\Ora81\rdbms\admin，Unix: $ORACLE_HOME/rdbms/admin）
2. 嘗試啟用約束並導向例外表：

   ALTER TABLE code_ref ADD CONSTRAINT uk_code_ref UNIQUE (key_col1, key_col2)
   EXCEPTIONS INTO EXCEPTIONS;

3. 若有違規，報錯 ORA-02299，此時 EXCEPTIONS 表記錄違規列的 ROW_ID
4. 關聯查詢還原重複資料：

   SELECT c.* FROM code_ref c, EXCEPTIONS e
   WHERE c.ROWID = e.ROW_ID
     AND e.CONSTRAINT = 'UK_CODE_REF';

方法比較與選擇建議

大表（>50萬列）清理策略

• 分批次刪除（如每次 1 萬列）避免長交易與 Undo 爆滿
• 建立臨時表存放保留資料，TRUNCATE 原表再 INSERT /*+ APPEND */ 回寫
• 利用 DBMS_REDEFINITION 線上重定義表結構同時清理
• 離峰時段執行，監控 Undo Tablespace 使用率

重複資料是資料品質的毒瘤，選對方法、分批處理、驗證結果，讓唯一約束順利建立，為資料完整性築牢防線。