Oracle INSERT ALL 多表插入：單次掃描多目標寫入的性能利器

語法結構

INSERT [ALL | FIRST] 
  INTO table1 [(col_list)] VALUES (val_list)
  INTO table2 [(col_list)] VALUES (val_list)
  ...
  [ELSE INTO else_table [(col_list)] VALUES (val_list)]
  SELECT ... FROM source_table WHERE ...;

• INSERT ALL：無條件執行所有 INTO 分支（默認）。
• INSERT FIRST：按順序評估 WHEN 條件，**首個滿足**的分支執行後停止（類似 CASE WHEN）。
• WHEN condition THEN：條件分支（可選），未滿足任何條件走 ELSE。

核心優勢：單次源表掃描，多目標並行寫入

傳統方式需多條 INSERT ... SELECT 多次全表掃描源表；INSERT ALL 只掃描一次源表，將行分發到多個目標表，I/O 減少 50%+，適用於：

• 數據倉庫 ETL：一張暫存表分發到多個維度表/事實表。
• 歸檔分流：熱數據留主表，冷數據寫歷史表。
• 多租戶隔離：按租戶 ID 分發到不同 Schema/表。

實戰示例

-- 從 SCOTT.EMP 分發：薪資>2000 入高薪表，其餘入普薪表，全部入全量表
INSERT ALL
  WHEN sal > 2000 THEN
    INTO emp_high (empno, ename, sal) VALUES (empno, ename, sal)
  ELSE
    INTO emp_low (empno, ename, sal) VALUES (empno, ename, sal)
  INTO emp_all (empno, ename, sal) VALUES (empno, ename, sal)  -- 無條件分支
SELECT empno, ename, sal FROM scott.emp;

進階：條件分支與 FIRST 模式

-- 互斥分流：每行僅進入一個目標表
INSERT FIRST
  WHEN deptno = 10 THEN INTO dept10_emp VALUES (empno, ename, sal)
  WHEN deptno = 20 THEN INTO dept20_emp VALUES (empno, ename, sal)
  WHEN deptno = 30 THEN INTO dept30_emp VALUES (empno, ename, sal)
  ELSE INTO dept_other_emp VALUES (empno, ename, sal)
SELECT empno, ename, sal, deptno FROM scott.emp;

性能對比實測（100 萬行源表）

限制與注意事項

1. 目標表**不能**有觸發器（會報 ORA-28014），需先禁用觸發器或改用 FORALL 批量綁定。
2. 目標表**不能**參與外鍵約束（父表/子表均不可），否則報 ORA-02298。
3. RETURNING 子句**不支持**。
4. 並行 DML (ALTER SESSION ENABLE PARALLEL DML) 可進一步加速，但需目標表啟用並行屬性。
5. 12c+ 支持 ERROR_LOGGING 子句記錄錯誤行：
   INSERT ALL ... LOG ERRORS INTO err_table REJECT LIMIT UNLIMITED SELECT ...

最佳實踐清單

• ETL 階段優先評估 INSERT ALL/FIRST 替代多條 INSERT。
• 大數據量場景配合 APPEND Hint：INSERT /*+ APPEND */ ALL ... 直寫高水位，減少 Redo/Undo。
• 分區表目標表時，源查詢帶上分區鍵，實現分區級直寫（Partition-Wise Insert）。
• 錯誤處理：12c+ 用 LOG ERRORS，舊版本拆分為多條 INSERT 配合 SAVE EXCEPTIONS / FORALL ... SAVE EXCEPTIONS。