萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 高效刪除重複資料實戰技巧

場景痛點

刪除重複資料是 DBA 與開發常見任務。傳統 DELETE 配合子查詢在大表上效能低落,本文介紹兩種高效方案:分析函數法與分區交換法。

方案一:ROW_NUMBER() 窗口函數刪除(適用中小型表)

利用 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 標記重複行,僅保留第一筆:

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

優點:單一 SQL 完成,邏輯清晰;實測:比傳統 GROUP BY ... HAVING COUNT(*) > 1 快數倍。

方案二:分區表大量資料採用「建臨時表 + 交換分區」

針對數 GB 級分區表,直接 DELETE 會產生大量 UNDO/REDO,導致耗時極長。推薦流程:

  1. 建立結構相同的臨時表(含索引、表空間設定一致)
  2. 用 INSERT /*+ APPEND */ ... NOLOGGING 將唯一資料寫入臨時表
  3. ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... 秒級完成切換
  4. 重建索引(Exchange 會導致 Local Index 失效)

實戰範例

-- 1. 插入去重後資料到臨時表(以子分區為例)
INSERT /*+ APPEND */ INTO ACCT_ITEM_MID
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;

-- 2. 交換分區
ALTER TABLE ACCT_ITEM EXCHANGE SUBPARTITION P_201109_SUB_P_XX WITH TABLE ACCT_ITEM_MID;

-- 3. 重建索引
ALTER INDEX ind_name REBUILD SUBPARTITION subpartition_name;

關鍵提醒

  • 臨時表的表空間、索引表空間必須符合原表規格
  • Exchange 操作後 Local Index 會標記 UNUSABLE,務必重建
  • 若表有 Global Index,Exchange 會自動維護,但建議事後 ANALYZE 更新統計資訊

總結

中小表用 ROW_NUMBER() 簡潔高效;超大分區表用 APPEND + NOLOGGING + EXCHANGE PARTITION 可將小時級操作壓縮至分鐘級,是生產環境首選方案。

copyright © 萬盛學電腦網 all rights reserved