Oracle BULK COLLECT 批量取資料效能最佳實踐

逐行 FETCH 會產生大量 Context Switch，BULK COLLECT 可一次將查詢結果批量載入集合，效能提升 10x-100x。核心模式：

標準範本

DECLARE
  TYPE t_emp_tab IS TABLE OF emp%ROWTYPE;
  v_emps t_emp_tab;
  CURSOR c_emp IS SELECT * FROM emp WHERE deptno = 10;
BEGIN
  OPEN c_emp;
  FETCH c_emp BULK COLLECT INTO v_emps;  -- 一次性載入
  CLOSE c_emp;

  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i).ename);
  END LOOP;
END;

進階：分批處理大資料集 (LIMIT 子句)

DECLARE
  TYPE t_id_tab IS TABLE OF NUMBER;
  v_ids t_id_tab;
  CURSOR c_big IS SELECT id FROM huge_table;
BEGIN
  OPEN c_big;
  LOOP
    FETCH c_big BULK COLLECT INTO v_ids LIMIT 10000;  -- 每次 1 萬筆
    EXIT WHEN v_ids.COUNT = 0;
    -- 批量處理邏輯
    FORALL i IN 1..v_ids.COUNT
      DELETE FROM child_table WHERE parent_id = v_ids(i);
    COMMIT;  -- 分批提交避免 UNDO 爆滿
  END LOOP;
  CLOSE c_big;
END;

關鍵優化組合

記憶體管理提醒

• 集合佔用 PGA 記憶體，LIMIT 必須配合 PGA_AGGREGATE_LIMIT 設定，建議單批 1,000-10,000 行。
• 大型物件型別集合慎用，改用游標迴圈或暫存表。

避坑指南

• BULK COLLECT 不支援 LONG/LOB 欄位，需改用 DBMS_LOB 分段讀取。
• FORALL 遇例外預設停止，加 SAVE EXCEPTIONS 並檢查 SQL%BULK_EXCEPTIONS 可繼續處理。
• 隱式游標 SELECT ... BULK COLLECT INTO 僅適用單行/少量資料，大量資料必須用顯式游標 + LIMIT。

掌握 BULK COLLECT ... LIMIT + FORALL 黃金組合，批量資料處理不再是瓶頸。