Oracle 刪除重複資料三種高效方法效能實測

測試環境

表 DEMO 由 DBA_OBJECTS 重複複製，約 88 萬筆，非重複 27,323 筆，無索引。

方法一：傳統 ROWID 子查詢 (最慢)

DELETE FROM DEMO A
WHERE A.ROWID <> (SELECT MAX(ROWID) FROM DEMO B
                  WHERE B.OBJECT_ID = A.OBJECT_ID);
-- 耗時：數小時以上

方法二：分析函數 ROW_NUMBER (快)

DELETE FROM DEMO
WHERE ROWID IN (
  SELECT RID FROM (
    SELECT ROWID RID, ROW_NUMBER() OVER (PARTITION BY OBJECT_ID ORDER BY ROWID) RN
    FROM DEMO
  ) WHERE RN <> 1
);
-- 耗時：約 30 秒

方法三：CTAS 重建表 (最快、回滾量最小)

CREATE TABLE DEMO2 AS
SELECT OBJECT_ID, OWNER, ...
FROM (
  SELECT DEMO.*, ROW_NUMBER() OVER (PARTITION BY OBJECT_ID ORDER BY ROWID) RN
  FROM DEMO
) WHERE RN = 1;

TRUNCATE TABLE DEMO;
INSERT INTO DEMO SELECT * FROM DEMO2;
DROP TABLE DEMO2;
-- 總耗時：約 10 秒，適合大資料量，產生極少回滾

結論

• 小表：方法二足夠
• 大表/生產環境：方法三最佳 (速度最快、回滾最小、可控)
• 避免方法一全表掃描相關子查詢