Oracle 高效刪除重複資料完整攻略

重複資料分類與策略

• 部分欄位重複：關鍵業務鍵相同，其他欄位可能不同 → 保留最新/最舊一筆。
• 整列完全重複：所有欄位值皆相同 → 保留任一筆即可。

一、部分欄位重複：保留最新記錄

1. 定位重複群組

SELECT col1, col2, COUNT(*)
  FROM target_table
 GROUP BY col1, col2
HAVING COUNT(*) > 1;

2. 低效寫法（全表掃描多次）

DELETE FROM target_table a
 WHERE (col1, col2) IN (
   SELECT col1, col2 FROM target_table
    GROUP BY col1, col2 HAVING COUNT(*) > 1);

⚠️ 子查詢每次執行皆全表掃描，大數據量極慢。

3. 進階：暫存表 + ROWID 精準刪除

-- 建立暫存表：每群組保留 MAX(ROWID)（最新寫入）
CREATE TABLE tmp_dup AS
SELECT col1, col2, MAX(rowid) AS keep_rowid
  FROM target_table
 GROUP BY col1, col2
HAVING COUNT(*) > 1;

-- 刪除非保留行
DELETE FROM target_table a
 WHERE a.rowid != (
   SELECT b.keep_rowid FROM tmp_dup b
    WHERE a.col1 = b.col1 AND a.col2 = b.col2);
COMMIT;

✅ 單次掃描建暫存表，刪除時索引式查找 ROWID，效能提升數量級。

4. 直接刪除語法（小表可用）

DELETE FROM target_table a
 WHERE a.rowid != (
   SELECT MAX(b.rowid) FROM target_table b
    WHERE a.col1 = b.col1 AND a.col2 = b.col2);

二、整列完全重複

暫存表去重法（最通用）

CREATE TABLE tmp_dedup AS SELECT DISTINCT * FROM target_table;
TRUNCATE TABLE target_table;          -- 注意：非 DROP，保留結構/權限/索引
INSERT INTO target_table SELECT * FROM tmp_dedup;
DROP TABLE tmp_dedup;

⚠️ 大表建議 INSERT /*+ APPEND */ 直寫 + NOLOGGING 減少 Redo。

備份表法

INSERT INTO target_table_bak SELECT DISTINCT * FROM target_table;
-- 驗證後 TRUNCATE 原表 → INSERT BACK

三、效能關鍵點總結

1. ROWID 是高效去重關鍵：物理定位唯一，避免業務鍵比較開銷。
2. 暫存表隔離判斷邏輯：將「找出重複」與「執行刪除」分離，減少重複掃描。
3. 索引輔助：去重欄位建立複合索引加速分組查詢。
4. 分批提交：超大量刪除時，分批 DELETE ... WHERE ROWNUM <= 10000 循環，避免 Undo 爆滿與鎖爭用。
5. 並行 DML：ALTER SESSION ENABLE PARALLEL DML + DELETE /*+ PARALLEL(t, 8) */ 加速。

四、避坑提醒

• TRUNCATE 非 DROP，否則需重建索引/權限/約束。
• 刪除前務必備份或驗證暫存表資料正確性。
• 含外鍵引用的表需先處理子表資料或暫停約束。