Oracle 重複資料刪除三大場景：部分欄位重複、整行重複、保留最新一筆

場景一：部分欄位重複（業務鍵重複）

表 T 中 (col1, col2) 為業務唯一鍵，需刪除重複組合，保留任一筆。

低效寫法（全表掃描兩次）

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

高效寫法：臨時表 + 索引

CREATE TABLE tmp_dup AS
SELECT col1, col2 FROM t
GROUP BY col1, col2 HAVING COUNT(*) > 1;

CREATE INDEX idx_tmp_dup ON tmp_dup(col1, col2);

DELETE FROM t a
WHERE (col1, col2) IN (SELECT col1, col2 FROM tmp_dup);

臨時表大幅縮小 IN 子查詢結果集，配合索引可從小時級降至秒級。

場景二：整行完全重複（含 ROWID）

利用 Oracle 隱藏 ROWID（每列唯一物理地址），保留 ROWID 最大（最新插入）者：

DELETE FROM t a
WHERE a.ROWID > (
  SELECT MIN(b.ROWID) FROM t b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2
);

MIN(ROWID) 代表最早插入的那筆，刪除其餘。若要保留最新，改用 MAX(ROWID) 並刪除 ROWID < MAX(...)。

場景三：保留最新一筆（業務時間欄位）

若有 create_time 或 update_time，可直接用時間判斷：

DELETE FROM t a
WHERE a.create_time < (
  SELECT MAX(b.create_time) FROM t b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2
);

若時間相同再以 ROWID 斷捨。

大數據量優化策略

1. 分批刪除：每次 DELETE ... WHERE ROWNUM <= 10000 迴圈，避免 UNDO 爆滿、鎖升級。
2. CTAS 重建表（最快）：

   CREATE TABLE t_new AS
   SELECT * FROM t
   WHERE ROWID IN (
     SELECT MAX(ROWID) FROM t GROUP BY col1, col2
   );
   DROP TABLE t;
   RENAME t_new TO t;
   -- 重建索引、約束、授權

3. 分區表：按時間分區，直接 TRUNCATE PARTITION 清除歷史重複區段。

驗證腳本

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

結果為 0 行表示清理完成。