Oracle 重複資料刪除完整指南：部分欄位與完全重複兩大場景

場景一：部分欄位重複（保留最新一筆）

1. 查詢重複鍵值

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

2. 低效直刪（不建議大表）

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

3. 高效暫存表法

-- 建立暫存表存放重複鍵值
CREATE TABLE temp_dup AS
SELECT col1, col2 FROM table_name
GROUP BY col1, col2 HAVING COUNT(*) > 1;

-- 刪除
DELETE FROM table_name a
WHERE (col1, col2) IN (SELECT col1, col2 FROM temp_dup);

4. 保留最新記錄（利用 ROWID）

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

5. 大表優化：暫存表存 MAX(ROWID)

CREATE TABLE temp_keep AS
SELECT col1, col2, MAX(rowid) AS keep_rid
FROM table_name
GROUP BY col1, col2;

DELETE FROM table_name a
WHERE a.rowid != (
  SELECT b.keep_rid FROM temp_keep b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2
);
COMMIT;

場景二：整行完全重複

-- 1. 取不重複資料存暫存表
CREATE TABLE temp_unique AS SELECT DISTINCT * FROM table_name;

-- 2. 刪除原表
DROP TABLE table_name;

-- 3. 還原資料
INSERT INTO table_name SELECT * FROM temp_unique;

-- 4. 清理
DROP TABLE temp_unique;

替代寫法：直接 INSERT 到備份表

INSERT INTO table_bak SELECT DISTINCT * FROM table_name;

效能與安全建議

• 大表務必使用暫存表避免重複掃描。
• 操作前完整備份，驗證無誤後提交。
• 若有外鍵約束，需暫時停用或級聯處理。
• 考慮加上 /*+ PARALLEL */ 提升大表刪除速度。