Oracle 資料庫高效刪除重複資料三大實戰方案：從萬行到億級的效能躍遷

前言：重複資料是效能與邏輯的雙重毒瘤

重複資料不僅浪費儲存、干擾統計、導致唯一索引創建失敗，更會讓業務報表產生誤導性結論。Oracle 無原生「一鍵去重」功能，DBA 必須根據資料量、業務容錯度、維護窗口選擇最優策略。本文呈現三大分級方案，覆蓋從萬行到億級的全場景需求。

場景定義：兩類重複模式

• 部分欄位重複：業務鍵（如 emp_id+dept_id）重複，但非鍵欄位可能不同 → 保留最新/最舊一筆
• 整行完全重複：所有欄位值完全相同 → 保留任意一筆即可

方案一：部分欄位重複 — 保留最新記錄（ROWID 定位法）

❌ 反模式：單條 DELETE 子查詢（效能災難）

DELETE FROM emp a
WHERE (dept_id, emp_id) IN (
  SELECT dept_id, emp_id FROM emp
  GROUP BY dept_id, emp_id HAVING COUNT(*) > 1
);

問題：子查詢每執行一次全表掃描，DELETE 又觸發大量 UNDO/REDO，大表直接拖垮資料庫。

✅ 進階：臨時表 + ROWID 精準定位（標準高效寫法）

-- 1. 建立臨時表，鎖定「每組保留的 ROWID」（最大值=最新插入）
CREATE TABLE tmp_emp_keep AS
SELECT dept_id, emp_id, MAX(ROWID) AS keep_rid
FROM emp
GROUP BY dept_id, emp_id;

-- 2. 刪除「不在保留清單」的行
DELETE FROM emp a
WHERE ROWID NOT IN (SELECT keep_rid FROM tmp_emp_keep b
                    WHERE a.dept_id = b.dept_id AND a.emp_id = b.emp_id);

COMMIT;

優勢：
• 只掃描 1 次全表建臨時表，DELETE 走 ROWID 直接定位，無全表掃描
• 臨時表可建索引（dept_id, emp_id）加速 JOIN
• 可驗證臨時表筆數，確認業務邏輯無誤再執行刪除

🚀 極致優化：並行 DML + 批次提交（億級資料）

ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ PARALLEL(emp 8) */ FROM emp a
WHERE ROWID NOT IN (SELECT /*+ PARALLEL(b 8) */ keep_rid FROM tmp_emp_keep b
                    WHERE a.dept_id = b.dept_id AND a.emp_id = b.emp_id);

配合批次提交（每 10 萬行 COMMIT），控制 UNDO 表空間壓力與鎖持有時間。

方案二：整行完全重複 — DISTINCT 重建法

-- 1. 建立去重後的臨時表
CREATE TABLE tmp_emp_dedup AS SELECT DISTINCT * FROM emp;

-- 2. 截斷原表（瞬時釋放空間，不產生 UNDO）
TRUNCATE TABLE emp;

-- 3. 直路徑插入回寫（APPEND 提升寫入效能）
INSERT /*+ APPEND */ INTO emp SELECT * FROM tmp_emp_dedup;
COMMIT;

-- 4. 清理
DROP TABLE tmp_emp_dedup;

關鍵點：
• TRUNCATE 替代 DELETE，避免大量 UNDO/REDO，瞬間完成
• APPEND 直路徑載入，繞過緩衝區，直接寫入高水位線以上
• 需預留 2 倍表大小的暫存表空間

方案三：線上重定義 — 7×24 不停機去重（企業級方案）

適用於不能停機、表極大、有外鍵/觸發器/索引依賴的核心業務表。使用 DBMS_REDEFINITION 套件：

-- 1. 驗證可重定義
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('SCHEMA', 'EMP', DBMS_REDEFINITION.CONS_USE_PK);

-- 2. 建立中間表（含去重邏輯：可用 DISTINCT 或 ROWID 過濾）
CREATE TABLE emp_interim AS SELECT DISTINCT * FROM emp;

-- 3. 啟動重定義
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCHEMA', 'EMP', 'EMP_INTERIM', 
  'EMP_ID EMP_ID, DEPT_ID DEPT_ID, ...', DBMS_REDEFINITION.CONS_USE_PK);

-- 4. 複製索引/約束/觸發器/權限到中間表（DISABLE 狀態）

-- 5. 可選：同步增量變更（減少最終切換鎖時間）
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCHEMA', 'EMP', 'EMP_INTERIM');

-- 6. 完成切換（極短暫獨占鎖）
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA', 'EMP', 'EMP_INTERIM');

全程原表可正常 DML，業務零感知，是關鍵業務表去重的終極方案。

⚠️ 文中錯誤修正：註冊表刪除法屬於「卸載 Oracle 軟體」，非「刪除資料庫重複資料」

原文「三、怎樣快速刪除 Oracle 資料庫」一節描述的是刪除註冊表鍵值與程式檔案，屬於軟體卸載操作，與資料去重無關，已於本文移除，請勿混淆。

方案選型決策樹

總結

刪除重複資料不是寫一條 DELETE 那麼簡單。理解 ROWID 物理定位、TRUNCATE 元資料操作、APPEND 直路徑載入、DBMS_REDEFINITION 線上重定義等核心機制，並根據業務約束選型，才能在保障資料正確性的前提下，實現毫秒級到小時級的效能跨越。下次面對重複資料，請先問：「我的資料量、重複模式、停機容忍度是什麼？」——答案裡藏著最優方案。