Oracle 大表刪除重複資料高效策略：ROW_NUMBER 分析函數與分區交換實戰

刪除重複資料是資料清洗常見需求。針對不同資料量級，本文提供兩種高效方案：中小表用 ROW_NUMBER() 窗口函數、超大分區表用 APPEND + NOLOGGING + EXCHANGE PARTITION。

方案一：ROW_NUMBER() 保留最新/最舊一筆（適用 < 100 萬筆）

DELETE FROM prod_grid_inc
WHERE ROWID IN (
  SELECT b.row_id2
  FROM (
    SELECT ROWID AS row_id2,
           a.*,
           ROW_NUMBER() OVER (PARTITION BY a.prod_id ORDER BY a.insert_date DESC) AS row_num
    FROM prod_grid_inc a
  ) b
  WHERE b.row_num >= 2
);

• PARTITION BY 定義重複鍵（此處為 prod_id）。
• ORDER BY 決定保留哪一筆（DESC 保留最新）。
• 直接刪除 ROWID，避免全表掃描 JOIN。

方案二：超大分區表（數 GB~TB）採用建新表 + 交換分區

原理：以 INSERT /*+ APPEND */ 直寫新分區，再以元資料操作 EXCHANGE PARTITION 秒級切換，避免大量 REDO/UNDO 產生。

步驟

1. 建立結構相同的中介表（同表空間、同索引定義）。
2. 以 APPEND + NOLOGGING 寫入去重後資料。
3. EXCHANGE PARTITION 切換。
4. 重建索引（交換後區域索引會標記 UNUSABLE）。

實戰腳本

-- 1. 建立中介表（同分區鍵、同索引）
CREATE TABLE acct_item_mid
PARTITION BY RANGE (acct_date) SUBPARTITION BY HASH (cust_id) SUBPARTITIONS 8
( PARTITION p_201109 VALUES LESS THAN (DATE '2011-10-01') )
AS SELECT * FROM acct_item WHERE 1=0;

-- 2. 寫入去重資料（保留每 cust_id 最大 ROWID）
INSERT /*+ APPEND */ INTO acct_item_mid SUBPARTITION (P_201109_SUB_P_XX)
SELECT * FROM acct_item SUBPARTITION (P_201109_SUB_P_XX) a
WHERE ROWID = (
  SELECT MAX(ROWID)
  FROM acct_item SUBPARTITION (P_201109_SUB_P_XX) a1
  WHERE a.cust_id = a1.cust_id
);
COMMIT;

-- 3. 交換分區（秒級完成）
ALTER TABLE acct_item EXCHANGE SUBPARTITION P_201109_SUB_P_XX
WITH TABLE acct_item_mid
INCLUDING INDEXES
WITHOUT VALIDATION;

-- 4. 重建索引（區域索引）
ALTER INDEX idx_acct_item_cust_id REBUILD SUBPARTITION P_201109_SUB_P_XX;
-- 全域索引需 REBUILD PARTITION 或整體 REBUILD

關鍵參數說明

方案選型決策樹

資料量 < 100 萬筆？ → ROW_NUMBER() DELETE
資料量 ≥ 100 萬 且 非分區表？ → CTAS 建新表 + RENAME + 索引重建
資料量 ≥ 100 萬 且 分區表？ → APPEND + EXCHANGE PARTITION（最快）

注意事項

• 交換分區後全域索引失效，必須 ALTER INDEX ... REBUILD。
• APPEND 會導致表鎖，建議維護窗口執行。
• 若啟用 FORCE LOGGING 或 Data Guard，NOLOGGING 失效，改用 LOGGING 仍可受益於 APPEND。
• 交換前務必確認中介表結構、約束、索引與目標分區完全一致。