Oracle 刪除表中重複資料三大經典方法：ROWID、臨時表、CTAS 深度對比

重複資料產生原因與影響

缺乏主鍵/唯一約束、ETL 重複載入、應用邏輯缺陷導致重複行。佔用空間、干擾統計資訊、導致查詢結果錯誤，需定期清理。

方法一：ROWID 保留法（適合小表、少量重複）

DELETE FROM tb_channel a
WHERE a.rowid IN (
  SELECT MAX(b.rowid)
  FROM tb_channel b
  WHERE a.policyno = b.policyno
    AND a.classcode = b.classcode
);

• 原理：同組重複資料保留最大 ROWID（最後插入），刪除其餘。
• **缺點：** 關聯子查詢執行計畫差，資料 > 10 萬行時**極慢**，Undo 產生量大，易撐爆 Undo 表空間。

方法二：臨時表換入法（適合大表、大量重複）

-- 1. 建立去重臨時表
CREATE TABLE temp_emp AS SELECT DISTINCT * FROM employee;

-- 2. 清空原表（TRUNCATE 釋放高水位，極快）
TRUNCATE TABLE employee;

-- 3. 插回去重資料
INSERT INTO employee SELECT * FROM temp_emp;

-- 4. 重建索引、約束、授權（TRUNCATE 會移除）
-- 5. 統計資訊收集
DROP TABLE temp_emp;

• **優點：** 批量操作（Direct Path Insert + TRUNCATE），**大表效能極佳**，Undo 極少。
• **注意：** 需離線維護窗口（TRUNCATE 鎖表）、外鍵關聯表需先處理、權限/索引/觸發器需重建。

方法三：CTAS 重建表法（Oracle 原生語法，最簡潔）

-- Oracle 不支援 SELECT INTO 新表，需用 CTAS
CREATE TABLE new_table AS SELECT DISTINCT * FROM old_table;

-- 刪除舊表
DROP TABLE old_table;

-- 重命名（需權限）
RENAME new_table TO old_table;
-- 或使用 DBMS_REDEFINITION 線上重定義（零停機）

• **優點：** 單一 DDL 完成去重+建表，**無中間表**、語法最簡潔。
• **缺點：** 同樣需重建索引/約束/授權/統計資訊、**物化視圖日誌失效**、外鍵需重建。

三方法核心對比

生產環境最佳實踐建議

1. 預防優於治療：**必須建立主鍵/唯一約束**，從源頭杜絕重複。
2. 大表首選 CTAS + DBMS_REDEFINITION 實現線上重定義，業務零感知。
3. 中小表維護窗口充足：臨時表換入法操作直觀、風險可控。
4. 清理前必備份：完整備份或至少導出重複資料樣本，防誤刪無法恢復。
5. 清理後必收集統計資訊： DBMS_STATS.GATHER_TABLE_STATS，否則優化器誤判導致效能倒退。