Oracle 重複數據刪除完全指南：從基礎去重到保留最新記錄的高效方案

重複數據的兩種典型場景

1. 部分字段重複：業務鍵（如身份證號、訂單號）重複，但非鍵字段可能不同。
2. 整行完全重複：所有列值均相同，通常由缺乏主鍵/唯一約束的表在並發插入時產生。

重複數據不僅浪費存儲，還會導致統計信息失真、連接膨脹、報表錯誤。刪除策略需兼顧正確性與性能。

場景一：刪除部分字段重複，保留最新記錄（推薦方案）

步驟 1：識別重複鍵值

SELECT id_card, COUNT(*)
FROM customer
GROUP BY id_card
HAVING COUNT(*) > 1;

步驟 2：創建臨時表存儲「需保留」的 ROWID（高效核心）

CREATE TABLE tmp_keep_rowid AS
SELECT MAX(rowid) AS keep_rowid, id_card
FROM customer
GROUP BY id_card
HAVING COUNT(*) > 1;

• 利用 Oracle 隱藏列 ROWID（物理地址，唯一且隱含插入順序），MAX(rowid) 近似代表「最新插入」。
• 若有業務時間戳列（如 create_time），更準確：
  SELECT MAX(rowid) KEEP (DENSE_RANK LAST ORDER BY create_time) ...

步驟 3：高效刪除多餘記錄

DELETE FROM customer c
WHERE c.rowid NOT IN (SELECT keep_rowid FROM tmp_keep_rowid)
  AND c.id_card IN (SELECT id_card FROM tmp_keep_rowid);

• 子查詢僅掃描小臨時表，避免大表自連接或重複聚合。
• 可添加 /*+ PARALLEL(c, 4) */ 並行加速（Enterprise Edition）。

步驟 4：清理臨時表

DROP TABLE tmp_keep_rowid;

場景二：刪除完全重複行（保留一行）

方法 A：DISTINCT 重建表（適合中小表、維護窗口充足）

CREATE TABLE tmp_dedup AS SELECT DISTINCT * FROM source_table;
TRUNCATE TABLE source_table;
INSERT INTO source_table SELECT * FROM tmp_dedup;
DROP TABLE tmp_dedup;

• TRUNCATE 瞬間釋放空間、重置 HWM，比 DELETE 快得多且不產生 UNDO/REDO。
• 需重建索引、約束、權限、同義詞。

方法 B：ROWID 直接刪除（適合大表、無維護窗口）

DELETE FROM source_table a
WHERE a.rowid > (
  SELECT MIN(b.rowid)
  FROM source_table b
  WHERE b.col1 = a.col1
    AND b.col2 = a.col2
    -- 所有列比較
);

• 保留每組重複中的最小 ROWID（最早插入）。
• 大表建議分批刪除（如每次 1 萬行），中間 COMMIT，避免 UNDO 爆滿與長鎖定。

性能優化關鍵點

進階：在線重定義（DBMS_REDEFINITION）零停機去重

-- 1. 創建中間表（含去重邏輯）
CREATE TABLE customer_interim AS
SELECT * FROM customer WHERE 1=2;  -- 結構
ALTER TABLE customer_interim ADD CONSTRAINT pk_cust PRIMARY KEY (id_card);

-- 2. 啟動重定義
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'CUSTOMER', 'CUSTOMER_INTERIM');

-- 3. 同步數據（可並行）
EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'CUSTOMER', 'CUSTOMER_INTERIM');

-- 4. 完成切換（瞬間鎖表，極短）
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'CUSTOMER', 'CUSTOMER_INTERIM');

適用於 7×24 業務零停機需求，Enterprise Edition 專屬。

預防重複數據的根治措施

1. 業務鍵加唯一約束：ALTER TABLE customer ADD CONSTRAINT uk_id_card UNIQUE (id_card);
2. 主鍵設計：所有業務表必須有主鍵（序列/Identity 列）。
3. ETL/導入去重：數據入庫前在暫存層 DISTINCT 或 ROW_NUMBER() OVER (PARTITION BY key ORDER BY load_time DESC) = 1。
4. 應用層冪等設計：業務唯一標識 + MERGE / INSERT ... ON CONFLICT（19c+）。

結語

刪除重複數據不是一次性動作，而是「識別 → 高效刪除 → 根治預防」的閉環。核心技巧在於：用 ROWID 替代全列比較、用臨時表替代重複計算、用 TRUNCATE/重定義替代大量 DELETE。根據表大小、維護窗口、業務可用性要求選擇最適策略，方能事半功倍。