Oracle 批量插入性能對比：FORALL vs 逐行 INSERT 實測數據與最佳實踐

前言：批量綁定的威力

Oracle 9i 引入 FORALL 批量綁定，將 PL/SQL 引擎與 SQL 引擎上下文切換從 N 次降為 1 次。本文實測 10 萬條記錄：**逐行 INSERT 93 秒 vs FORALL 0.44 秒，提速 200 倍**。附完整 Java/JDBC 批量綁定代碼與避坑指南。

一、 測試環境與結果

二、 數據庫端：集合類型與 FORALL 過程

-- 1. 定義 Schema 級集合類型 (供 JDBC ARRAY 映射)
CREATE OR REPLACE TYPE i_table IS TABLE OF NUMBER(10);
CREATE OR REPLACE TYPE v_table IS TABLE OF VARCHAR2(10);

-- 2. 批量插入存儲過程
CREATE OR REPLACE PROCEDURE pro_forall_insert(
  p_ids  IN i_table,
  p_vals IN v_table
) AS
BEGIN
  -- 核心：FORALL 批量綁定，單次上下文切換
  FORALL i IN 1..p_ids.COUNT
    INSERT INTO test_table (id, name) VALUES (p_ids(i), p_vals(i));
    
  -- 可選：異常處理 (10g+)
  -- FORALL i IN 1..p_ids.COUNT SAVE EXCEPTIONS
  --   INSERT ...
  -- EXCEPTION WHEN OTHERS THEN
  --   IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN ... 記錄錯誤索引 ...
END;
/

三、 Java/JDBC 端：Oracle ARRAY 綁定完整代碼

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;

public class BatchInsertDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:oracle:thin:@192.168.15.234:1521:ora10g";
        String user = "imcs", pwd = "imcs";
        
        try (Connection conn = DriverManager.getConnection(url, user, pwd)) {
            conn.setAutoCommit(false);
            
            // 1. 準備 10萬條數據
            int count = 100_000;
            Object[] ids = new Object[count];
            Object[] names = new Object[count];
            for (int i = 0; i < count; i++) {
                ids[i] = i + 1;
                names[i] = "name_" + i;
            }
            
            // 2. 創建 Oracle ARRAY 描述符 (需類型已在數據庫創建)
            ArrayDescriptor idDesc = ArrayDescriptor.createDescriptor("I_TABLE", conn);
            ArrayDescriptor valDesc = ArrayDescriptor.createDescriptor("V_TABLE", conn);
            
            // 3. 構建 ARRAY 對象
            ARRAY idArray = new ARRAY(idDesc, conn, ids);
            ARRAY valArray = new ARRAY(valDesc, conn, names);
            
            // 4. 調用存儲過程
            try (CallableStatement cs = conn.prepareCall("{call pro_forall_insert(?, ?)}")) {
                cs.setArray(1, idArray);
                cs.setArray(2, valArray);
                
                long start = System.currentTimeMillis();
                cs.execute();
                conn.commit();
                System.out.println("FORALL 批量插入耗時: " + (System.currentTimeMillis() - start) + " ms");
            }
        }
    }
}

四、 關鍵技術點解析

五、 替代方案對比

六、 生產環境檢查清單

1. 集合類型命名規範：T_<业务>_ID_TAB, T_<业务>_VAL_TAB
2. 批次大小參數化：BATCH_SIZE = 10000 可配置，避免硬編碼
3. 異常處理：SAVE EXCEPTIONS + 錯誤日志表記錄失敗索引與數據
4. 監控指標：SQL%ROWCOUNT 確認插入行數、V$SQL 執行計劃驗證批量綁定生效
5. 權限最小化：僅授予 EXECUTE ON pro_forall_insert 及集合類型 EXECUTE

七、 總結

FORALL 批量綁定是 Oracle 大批量寫入的黃金標準。通過 Schema 級集合類型 + Oracle ARRAY + Call Spec，Java 應用可無縫享受 200 倍性能提升。核心口訣：**少量用 addBatch，大量用 FORALL，極大量用外部表**。