Oracle 重複資料刪除完全指南：部分欄位重複與保留最新記錄的高效寫法

重複資料兩大類型

1. 部分欄位重複：關鍵業務鍵 (如 deptno, ename) 重複，其他欄位可能不同
2. 整列完全重複：所有欄位值均相同

重複資料刪除技術優勢：釋放空間、提升備份效率、加速恢復、利於容災。

場景一：刪除部分欄位重複資料 (保留任一筆)

低效寫法 (大數據量慎用)

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

子查詢每次執行掃描全表，大表極易拖垮資料庫。

高效寫法：暫存表中介

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

-- 2. 依暫存表刪除 (避免重複掃描)
DELETE FROM table_a a
WHERE (a.col1, a.col2) IN (SELECT col1, col2 FROM temp_dup_keys);

-- 3. 清理
DROP TABLE temp_dup_keys;

場景二：保留最新記錄 (依 ROWID 最大值)

Oracle 隱藏欄位 ROWID 編碼資料物理位置，越新寫入的 ROWID 通常越大 (同一物件)。

單句刪除 (效能較差)

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

高效版：暫存表保存「每組最大 ROWID」

-- 1. 建立暫存表：每組重複鍵保留最大 ROWID
CREATE TABLE temp_keep AS
SELECT col1, col2, MAX(ROWID) AS keep_rid
FROM table_a
GROUP BY col1, col2;

-- 2. 刪除非保留行
DELETE FROM table_a a
WHERE a.ROWID != (
  SELECT b.keep_rid FROM temp_keep b
  WHERE a.col1 = b.col1 AND a.col2 = b.col2
);

-- 3. 提交並清理
COMMIT;
DROP TABLE temp_keep;

場景三：完全重複列刪除 (保留一筆)

-- 1. 去重存入暫存表
CREATE TABLE temp_distinct AS SELECT DISTINCT * FROM table_a;

-- 2. 清空原表 (TRUNCATE 比 DELETE 快且不產生 UNDO)
TRUNCATE TABLE table_a;

-- 3. 寫回去重資料
INSERT INTO table_a SELECT * FROM temp_distinct;

-- 4. 清理
DROP TABLE temp_distinct;

或單指令 (適合中小表)：

INSERT INTO table_bak SELECT DISTINCT * FROM table_a;
TRUNCATE TABLE table_a;
INSERT INTO table_a SELECT * FROM table_bak;
DROP TABLE table_bak;

效能對比總結

進階技巧

• 分區表：在分區層級執行 ALTER TABLE ... TRUNCATE PARTITION 並行處理。
• DBMS_REDEFINITION：線上重定義表結構同時去重，零停機。
• 唯一索引 + IGNORE_ROW_ON_DUPKEY_INDEX (11g+)：插入時自動忽略重複鍵。
• 定期收集統計資訊：確保優化器選擇最佳執行計畫。

注意事項

1. 刪除前**務必備份**或建立暫存表驗證結果。
2. 大量刪除產生大量 UNDO/REDO，考慮分批提交 (每 5000~10000 列)。
3. 刪除後執行 ANALYZE TABLE ... COMPUTE STATISTICS 或 DBMS_STATS 更新統計。
4. 若有外鍵參考，需先處理子表資料或暫停約束。