Oracle 刪除重複資料：ROWID 精準去重最佳實務

為何使用 ROWID？

Oracle 每一列資料皆具唯一 ROWID，編碼了「資料檔號、區塊號、列號」。即使所有欄位值完全相同，ROWID 也不同。利用此特性，可精準保留一筆、刪除其餘重複列，避免誤刪。

標準去重語法

DELETE FROM target_table t
WHERE t.ROWID > (
  SELECT MIN(x.ROWID)
  FROM target_table x
  WHERE x.key_col1 = t.key_col1
    AND x.key_col2 = t.key_col2
    -- 依業務鍵定義重複條件
);

實戰範例

假設 bb 表有 id, name 兩欄，以 name 判斷重複：

-- 原始資料
ID NAME
--- ----
 3  sq
 4  sqa
 5  sqa
 6  sqa
 7  sqa
 2  as
 1  s

-- 執行去重
DELETE FROM bb b
WHERE b.ROWID > (
  SELECT MIN(x.ROWID)
  FROM bb x
  WHERE x.name = b.name
);

-- 結果：保留 id=4 (sqa)、id=3 (sq)、id=2 (as)、id=1 (s)

大量資料優化策略

1. 建立暫存表：CREATE TABLE bb_dedup AS SELECT MIN(ROWID) AS rid, name FROM bb GROUP BY name; 然後 DELETE FROM bb WHERE ROWID NOT IN (SELECT rid FROM bb_dedup); 減少子查詢重複掃描。
2. 分批提交：PL/SQL 迴圈每 5,000 筆 COMMIT，避免 UNDO 爆滿與長鎖定。
3. 並行 DML：ALTER SESSION ENABLE PARALLEL DML; DELETE /*+ PARALLEL(bb 4) */ ... (Enterprise Edition)。
4. CTAS 重建：若重複率極高 (>30%)，CREATE TABLE bb_new AS SELECT DISTINCT * FROM bb; 重建索引/約束/權限後 RENAME 替換，速度最快。

常見陷阱與注意事項

• 子查詢相關欄位務必包含所有業務鍵欄位，避免誤判。
• ROWID 在 ALTER TABLE MOVE、SHRINK SPACE、分區遷移後會改變，去重前勿執行此類操作。
• 若表有 TRIGGER、FK 參考，刪除可能觸發級聯或報錯，需暫時停用或調整順序。
• 執行前先 SELECT COUNT(*) FROM bb GROUP BY name HAVING COUNT(*) > 1; 確認重複筆數與預期一致。