刪除重複資料是 DBA 與開發常見任務。傳統 DELETE 配合子查詢在大表上效能低落,本文介紹兩種高效方案:分析函數法與分區交換法。
利用 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,導致耗時極長。推薦流程:
INSERT /*+ APPEND */ ... NOLOGGING 將唯一資料寫入臨時表ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... 秒級完成切換-- 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;
UNUSABLE,務必重建ANALYZE 更新統計資訊中小表用 ROW_NUMBER() 簡潔高效;超大分區表用 APPEND + NOLOGGING + EXCHANGE PARTITION 可將小時級操作壓縮至分鐘級,是生產環境首選方案。