Oracle 批量匯入資料的高效能策略 (無框架環境)

問題背景

在無 ORM 框架 (如 Hibernate/MyBatis) 的純 JDBC 環境下，使用 for 迴圈逐筆 INSERT 效能極差，每筆皆有一次網路往返與解析開銷。需大幅提升批量寫入效能。

核心優化方向

1. 減少網路往返：使用批次處理。
2. 減少 SQL 解析：使用 PreparedStatement 並重複執行。
3. 減少 Redo/Undo 產生：適當調整提交頻率、使用 NOLOGGING (需權限)、APPEND Hint。
4. 並行化：多執行緒分區寫入 (Parallel DML)。

關鍵技術實作

1. JDBC 批次更新 — 基礎必做

String sql = "INSERT INTO target_table (id, name, value) VALUES (seq.nextval, ?, ?)";
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {
    
    conn.setAutoCommit(false); // 關鍵：手動控制交易
    
    for (int i = 0; i < dataList.size(); i++) {
        Data d = dataList.get(i);
        ps.setString(1, d.getName());
        ps.setDouble(2, d.getValue());
        ps.addBatch(); // 加入批次
        
        // 每 1000 筆執行一次，避免記憶體暴漲
        if (i % 1000 == 0) {
            ps.executeBatch();
            conn.commit();
        }
    }
    ps.executeBatch(); // 執行剩餘
    conn.commit();
}

效果：相比逐筆 commit，速度通常提升 10x - 100x。

2. Oracle 特有優化：/*+ APPEND */ Hint

String sql = "INSERT /*+ APPEND */ INTO target_table (...) VALUES (...)";

• 直接路徑載入，繞過 Buffer Cache，直接寫入資料檔 (High Water Mark 以上)。
• 大幅減少 Redo Log 產生 (若表設為 NOLOGGING)。
• 注意：鎖定整張表 (排他鎖)，並發環境慎用；寫入後需備份才能恢復。

3. PL/SQL 批次綁定 — 極致效能

將資料組裝為陣列，一次傳給資料庫處理，消除 Java 與 DB 間的多次互動。

-- Oracle 端建立型別與程序
CREATE OR REPLACE TYPE t_data_arr IS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE PROCEDURE bulk_insert(p_data IN t_data_arr) IS
BEGIN
    FORALL i IN 1..p_data.COUNT
        INSERT INTO target_table (col1) VALUES (p_data(i));
    COMMIT;
END;

Java 端使用 OracleTypes.ARRAY 傳遞集合。此為大量資料 (百萬筆以上) 的最佳選擇。

4. 外部表 / SQL*Loader — 離線超大量資料

• 將資料匯出為 CSV/定寬檔案。
• 使用 SQL*Loader (sqlldr) 或建立 外部表 直接查詢檔案並 INSERT /*+ APPEND */ INTO ... SELECT * FROM ext_table。
• 效能最高，適合 TB 級遷移、ETL 流程。

參考資源

詳細基準測試與程式碼範例請參考：Oracle 批量插入效能優化實戰

總結建議

務必在測試環境壓測，觀察 Redo Log 產生量、Undo 表空間、鎖衝突，找出最佳批次大小與提交頻率。