Oracle 千萬筆資料高效刪除重複值實戰指南

場景背景

生產環境中一張千萬筆資料的表格因匯入失誤導致全表重複，且系統不可停機、不可全量刪除重建，必須在線快速清除重複列。

方案比較與優劣分析

假設表 TBL，主鍵為 (COL1, COL2) 並已建立索引。

方案一：建立暫存表（不推薦用於大表）

CREATE TABLE tbl_tmp AS SELECT DISTINCT * FROM tbl;
TRUNCATE TABLE tbl;
INSERT INTO tbl SELECT * FROM tbl_tmp;

缺點：全表掃描、建表、截斷、再插入，產生大量 REDO/UNDO，耗時極長，生產環境不可接受。

方案二：利用 ROWID 直接刪除（適用於「每筆僅一重複」）

DELETE FROM tbl
WHERE rowid IN (
    SELECT a.rowid
    FROM tbl a, tbl b
    WHERE a.rowid > b.rowid
      AND a.col1 = b.col1
      AND a.col2 = b.col2);

原理：ROWID 全資料庫唯一，保留較小的 ROWID。限制：若重複筆數超過 2 筆（N>2），需多次執行或改用方案三。

方案三：MAX/MIN 函數配合 ROWID（通用高效寫法）

-- 保留最大 ROWID
DELETE FROM tbl a
WHERE rowid NOT IN (
    SELECT MAX(b.rowid)
    FROM tbl b
    WHERE a.col1 = b.col1
      AND a.col2 = b.col2);

-- 或使用關聯子查詢
DELETE FROM tbl a
WHERE rowid < (
    SELECT MAX(b.rowid)
    FROM tbl b
    WHERE a.col1 = b.col1
      AND a.col2 = b.col2);

優點：單次掃描即可處理任意 N 重複，配合主鍵索引效能極佳。

方案四：GROUP BY 聚合進一步優化

DELETE FROM tbl
WHERE rowid NOT IN (
    SELECT MAX(rowid)
    FROM tbl
    GROUP BY col1, col2);

或結合 HAVING COUNT(*) > 1 只針對有重複的群組操作，減少不必要的索引查找。

最佳實踐建議

1. 優先選擇方案三或四，單一 SQL 完成，利用索引範圍掃描，對生產系統衝擊最小。
2. 執行前先用 SELECT COUNT(*) FROM tbl GROUP BY col1, col2 HAVING COUNT(*) > 1 確認重複分布。
3. 若表極大（億級），考慮分批次刪除（加 WHERE ROWNUM <= 10000 循環），避免長交易阻塞。
4. 刪除後執行 ANALYZE TABLE tbl COMPUTE STATISTICS 更新統計資訊。

總結

大表去重核心原則：避免全表建表、利用 ROWID 定位、配合索引單次掃描。方案三/四為生產環境首選。