Oracle 表重複資料查找三大經典技法：ROWID、GROUP BY、EXCEPTIONS 完整解析

當嘗試建立唯一索引時遇到 ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found，表示表中存在重複鍵值。必須先找出並清理重複列，才能成功建立索引。本文介紹三種查找重複資料的經典方法，適用於 Oracle 8i 至最新版本。

方法一：自關聯查詢（利用 ROWID）

Oracle 每列擁有唯一 ROWID，透過自關聯比較業務鍵值，保留 MAX(ROWID) 或 MIN(ROWID) 即可識別重複列：

SELECT a.*
FROM   code_ref a
JOIN   (SELECT key_col, MAX(rowid) AS keep_rid
        FROM   code_ref
        GROUP BY key_col
        HAVING COUNT(*) > 1) b
ON     a.key_col = b.key_col
AND    a.rowid <> b.keep_rid;

結果即為需刪除的重複列。優點：直觀、不需額外物件；缺點：大表自關聯可能較慢。

方法二：GROUP BY / HAVING 聚合查詢

最常用、效能最佳的方式：

SELECT key_col, COUNT(*) AS dup_cnt
FROM   code_ref
GROUP BY key_col
HAVING COUNT(*) > 1;

若需刪除重複列，可搭配 ROW_NUMBER() 分析函數（Oracle 9i+）：

DELETE FROM code_ref
WHERE  rowid IN (
  SELECT rowid
  FROM (
    SELECT rowid,
           ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY rowid) AS rn
    FROM   code_ref
  )
  WHERE rn > 1
);

方法三：EXCEPTIONS INTO 子句（傳統索引驗證法）

適用於已有大量資料、需一次性驗證並記錄違規列的場景：

1. 執行 @?/rdbms/admin/utlexcpt.sql 建立 EXCEPTIONS 表（記錄 ROW_ID, OWNER, TABLE_NAME, CONSTRAINT）。
2. 嘗試啟用約束並導向例外表：

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

3. 查詢違規列：

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

注意：utlexcpt.sql 路徑 Windows 為 %ORACLE_HOME%\rdbms\admin，Linux/Unix 為 $ORACLE_HOME/rdbms/admin。

實務建議

• 小/中型表：優先用 GROUP BY + ROW_NUMBER() 刪除，語法簡潔、效能佳。
• 超大表（億級以上）：考慮 CTAS 建新表 保留去重資料，再 DROP 舊表改名，避免大量 DELETE 產生 UNDO/REDO 壓力。
• 建立唯一索引前，務必先清理重複，否則 ORA-01452 會阻擋索引建立。

掌握這三招，面對「無法建立唯一索引」的報錯即可從容應對，快速定位並清理髒資料，確保資料品質與索引順利上線。