Oracle 重複資料查找三大經典方法：自關聯、GROUP BY、EXCEPTIONS 實戰對比

業務背景

建立唯一索引失敗 ORA-02299，提示存在重複鍵值。需先識別並清理重複列，再建立約束。

方法一：自關聯 + ROWID (經典、直觀)

SELECT a.*
FROM   table_name a
JOIN  (SELECT key_col, MIN(rowid) AS keep_rid
       FROM   table_name
       GROUP BY key_col
       HAVING COUNT(*) > 1) b
  ON   a.key_col = b.key_col
 AND   a.rowid > b.keep_rid;  -- 保留 MIN(rowid)，其餘為重複

• 優點：可直接看到完整重複列內容，便於人工審核。
• 缺點：大表自關聯成本高，需適當索引支援。

方法二：GROUP BY / HAVING (最輕量、純聚合)

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

• 優點：只掃描一次、不回表、極快，適合「只需知道有哪些鍵值重複」的場景。
• 缺點：無法直接取得完整列內容，需再 JOIN 回原表。

方法三：EXCEPTIONS INTO (建立約束時自動收集)

-- 1. 建立 EXCEPTIONS 表 (腳本 $ORACLE_HOME/rdbms/admin/utlexcpt.sql)
@?/rdbms/admin/utlexcpt.sql

-- 2. 嘗試啟用約束，違規列導入 EXCEPTIONS
ALTER TABLE table_name ADD CONSTRAINT uk_key UNIQUE (key_col)
EXCEPTIONS INTO exceptions;

-- 3. 查詢重複列
SELECT t.*
FROM   table_name t
JOIN   exceptions e ON t.rowid = e.row_id
WHERE  e.constraint = 'UK_KEY';

• 優點：一條 DDL 同時完成「驗證 + 記錄違規」，適合大表建約束前檢查。
• 缺點：需預先建立 EXCEPTIONS 表；約束啟用失敗會回滾，不會真正建立約束。

刪除重複列最佳實踐

-- 保留每組第一筆 (依 ROWID)
DELETE FROM table_name
WHERE rowid NOT IN (
  SELECT MIN(rowid) FROM table_name GROUP BY key_col
);

• 大表分批刪除 (每批 5 萬列) 避免 Undo 爆滿與長鎖定：

BEGIN
  LOOP
    DELETE FROM table_name
    WHERE rowid IN (
      SELECT rowid FROM (
        SELECT rowid, ROW_NUMBER() OVER (PARTITION BY key_col ORDER BY rowid) rn
        FROM table_name
      ) WHERE rn > 1
      AND ROWNUM <= 50000
    );
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;
/
-- 最後建立唯一索引/約束

總結選型指南