Oracle 資料表重複記錄偵測與清理三大經典方法：ROWID 自關聯、GROUP BY HAVING、EXCEPTIONS INTO 實戰對比

問題背景

嘗試建立唯一索引時遭遇 ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found，必須先識別並清理重複資料。

方法一：ROWID 自關聯查詢 (效能最佳)

SELECT a.rowid, a.*
FROM code_ref a, code_ref b
WHERE a.unique_key = b.unique_key
  AND a.rowid > b.rowid;

• 利用 ROWID 唯一性，MAX(ROWID) 或 MIN(ROWID) 保留單一記錄。
• 適合大表，可直接轉為刪除語句：

  
  DELETE FROM code_ref
  WHERE rowid IN (
    SELECT a.rowid
    FROM code_ref a, code_ref b
    WHERE a.unique_key = b.unique_key
      AND a.rowid > b.rowid
  );
  

方法二：GROUP BY / HAVING 聚合檢測 (直觀易懂)

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

• 快速定位重複鍵值及重複次數。
• 需再結合 ROWID 或主鍵進行實際刪除。
• 適合分析階段、小中型表。

方法三：EXCEPTIONS INTO 子句 (約束驗證模式)

1. 建立例外表：

   
   @?/rdbms/admin/utlexcpt.sql
   -- NT: %ORACLE_HOME%\Ora81\rdbms\admin\utlexcpt.sql
   -- UNIX: $ORACLE_HOME/rdbms/admin/utlexcpt.sql
   

2. 嘗試啟用約束導向例外表：

   
   ALTER TABLE code_ref ADD CONSTRAINT uk_code_ref UNIQUE (unique_key)
   EXCEPTIONS INTO exceptions;
   

3. 查詢例外表關聯原表獲取重複行：

   
   SELECT c.*
   FROM code_ref c, exceptions e
   WHERE c.rowid = e.row_id
     AND e.table_name = 'CODE_REF'
     AND e.constraint = 'UK_CODE_REF';
   

• 優點：原生約束機制，適用於啟用現有約束場景。
• 缺點：需額外建表、兩階段操作、效能較前兩法低。

三方法對比總結

實務建議

• 生產環境大表優先使用 ROWID 自關聯 + DELETE 單一語句完成。
• 分析階段先用 GROUP BY HAVING 了解重複分布。
• 若需保留約束定義過程中的違規記錄供審計，採用 EXCEPTIONS INTO。
• 清理前務必備份，確認保留邏輯 (最早/最新/特定條件)。