Oracle 快速刪除重複資料的四種高效方法

情境

千萬筆資料表 Tbl (PK: col1, col2，已建索引) 全部重複一筆，生產環境需快速去重且不停機。

方法比較

1. 建立暫存表 (最慢，不建議)

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

大量資料搬移開銷極大。

2. 使用 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
);

3. 使用 MAX/MIN 函數 (適用多重複)

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
);

4. 使用 GROUP BY 提升效率 (推薦)

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

-- 針對重複筆數較少且有索引的情況
DELETE FROM tbl
WHERE (col1, col2) IN (
  SELECT col1, col2 FROM tbl GROUP BY col1, col2 HAVING COUNT(*) > 1
)
AND rowid NOT IN (
  SELECT MIN(rowid) FROM tbl GROUP BY col1, col2 HAVING COUNT(*) > 1
);

GROUP BY 減少顯性比較，配合索引效能最佳。