Oracle 去除重複數據完整方案：完全重複與部分字段重複的高效刪除策略

場景一：完全重複行 (所有列值相同)

-- 1. 創建臨時表保留 DISTINCT 結果
CREATE TABLE tmp_dedup AS SELECT DISTINCT * FROM source_table;

-- 2. 清空原表
TRUNCATE TABLE source_table;

-- 3. 數據回寫
INSERT INTO source_table SELECT * FROM tmp_dedup;

-- 4. 清理
DROP TABLE tmp_dedup;

優勢： 簡單、無需主鍵、並行 DDL 可加速大表操作。
注意： 需足夠臨時表空間，觸發器/外鍵需暫時禁用。

場景二：部分字段重複 (業務鍵重複，保留最新/最舊一行)

低效寫法 (直接刪除，大表慎用)

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

問題：子查詢重複掃描、大量隨機 I/O、Undo 爆發。

高效方案：臨時表 + ROWID 定位

-- 1. 識別需保留的 ROWID (按業務鍵分組取 MAX/MIN ROWID)
CREATE TABLE tmp_keep_rowid AS
SELECT col1, col2, MAX(ROWID) AS keep_rowid
FROM source_table
GROUP BY col1, col2;

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

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

原理： ROWID 物理定位唯一行，避免再次全表掃描；臨時表僅存業務鍵+ROWID，體積極小。

單語句寫法 (11g+ 支持 MERGE 刪除)

MERGE INTO source_table t
USING (
  SELECT col1, col2, MAX(ROWID) AS keep_rowid
  FROM source_table GROUP BY col1, col2
) s
ON (t.col1 = s.col1 AND t.col2 = s.col2 AND t.ROWID = s.keep_rowid)
WHEN NOT MATCHED THEN DELETE;  -- 11g 不支持 WHEN NOT MATCHED DELETE，需 12c+

12c+ 可用：DELETE FROM source_table WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM source_table GROUP BY col1, col2); (優化器可轉為 Anti-Join)。

性能對比參考 (1000 萬行測試)

生產環境檢查清單

• [ ] 業務鍵是否包含 NULL (NULL != NULL，GROUP BY 會分開組)
• [ ] 是否有外鍵引用 (需先 Disable/Defer Constraints)
• [ ] 是否有觸發器 (需先 Disable)
• [ ] 索引是否可用 (刪除後 Rebuild 或 Online Rebuild)
• [ ] 統計信息是否需重新收集 (DBMS_STATS)
• [ ] 歸檔模式下 Redo 量預估與磁盤空間