Oracle TRUNCATE、DELETE、DROP 三大刪除操作深度對比

核心差異一覽表

場景選型指南

✅ 使用 DELETE

• 需刪除**部分行**（帶 WHERE）。
• 需觸發觸發器（審計、同步邏輯）。
• 在事務中需回滾能力。
• 表較小或有索引維護成本可接受。

✅ 使用 TRUNCATE

• 清空**整表數據**，無事務回滾需求。
• 重置 HWM，釋放空間（或 `REUSE STORAGE` 保留空間供下次載入）。
• 大表清空、數據重載、分區截斷（`TRUNCATE PARTITION`）。
• 注意：被外鍵引用的父表不可直接 TRUNCATE，需先 `DISABLE` 約束。

✅ 使用 DROP

• 永久移除表結構、索引、約束、權限、觸發器等所有依賴對象。
• 表結構變更重建、測試環境清理、臨時表生命周期結束。
• 慎用：無回滾，無閃回表（除非啟用 Recyclebin 且 `PURGE` 未指定）。

進階細節

1. TRUNCATE 與 HWM

全表掃描讀取到 HWM 以下塊。大量刪除後 HWM 不下降，導致後續全掃性能差。`TRUNCATE` 或 `ALTER TABLE ... MOVE` + `SHRINK SPACE` 可重置 HWM。

2. DELETE 大表性能優化

-- 分批提交，避免 UNDO 爆滿、鎖升級
LOOP
  DELETE FROM big_table WHERE create_dt < :cutoff AND ROWNUM <= 10000;
  EXIT WHEN SQL%ROWCOUNT = 0;
  COMMIT;
END LOOP;

3. DROP 後的閃回恢復（10g+）

-- 未 PURGE 時可恢復
FLASHBACK TABLE dropped_table TO BEFORE DROP;
-- 查看回收站
SELECT * FROM RECYCLEBIN;

安全警示

⚠️ **生產環境嚴禁無備份直接 DROP/TRUNCATE**。建議：

1. 建立「變更單」流程，審批後執行。
2. 執行前導出元數據（`DBMS_METADATA.GET_DDL`）與關鍵數據樣本。
3. 啟用 Recyclebin（`RECYCLEBIN=ON`）作為最後防線。

理解三者在存儲、事務、觸發器、HWM 等維度的本質差異，是數據庫開發與運維的必修課。