Oracle 三層游標循環效能優化：改用集合/暫存表集合操作

原始效能殺手

FOR x IN (SELECT * FROM bc2) LOOP      -- 10+ 萬
  FOR y IN (SELECT * FROM temp) LOOP   -- 20+ 萬
    FOR z IN (SELECT * FROM disTemp) LOOP -- 20+ 萬
      v_num := z.CODE;
    END LOOP;
  END LOOP;
END LOOP;
-- 複雜度 O(N*M*K) ≈ 10^15 次迴圈，執行數十小時

優化策略：集合化思維 (Set-based Operations)

1. 消除巢狀循環：用單一 SQL 完成 `JOIN`、`EXISTS`、`IN` 等集合運算。
2. 將中間結果物質化**：用 **Global Temporary Table (GTT)** 或 **PL/SQL Collection (Associative Array/Nested Table)** 暫存篩選後的鍵值，再批次處理。
3. BULK COLLECT + FORALL**：將游標資料批次讀入陣列，批次寫入，減少 Context Switch。

重寫範例：假設需將 disTemp.CODE 依條件更新到 bc2

-- 1. 建立暫存表 (GTT，提交保留資料)
CREATE GLOBAL TEMPORARY TABLE tmp_dis_codes (code NUMBER) ON COMMIT PRESERVE ROWS;

-- 2. 批次載入 disTemp 符合條件的 CODE (單一 SQL)
INSERT INTO tmp_dis_codes
SELECT DISTINCT code FROM disTemp WHERE ...條件...;

-- 3. 建立索引加速 JOIN
CREATE INDEX idx_tmp_code ON tmp_dis_codes(code);

-- 4. 單一 MERGE/UPSERT 完成更新 (無循環)
MERGE INTO bc2 b
USING (
  SELECT t.code, t.other_col
  FROM tmp_dis_codes t
  JOIN temp y ON y.key = t.code
  WHERE y.cond = 'X'
) s ON (b.id = s.code)
WHEN MATCHED THEN UPDATE SET b.col = s.other_col;

-- 5. 提交
COMMIT;

PL/SQL 批次處理模板 (若必須用程序邏輯)

DECLARE
  TYPE code_arr IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  v_codes code_arr;
BEGIN
  -- 批次讀取 (限制 10000 筆/批)
  SELECT code BULK COLLECT INTO v_codes FROM disTemp WHERE ...;

  FORALL i IN INDICES OF v_codes
    UPDATE bc2 SET col = 'Y' WHERE id = v_codes(i);

  COMMIT;
END;

核心原則

• **SQL 集合運算** (JOIN, EXISTS, IN, MERGE) 替代循環。
• **批次處理** (BULK COLLECT, FORALL) 替代逐筆 DML。
• **索引** 覆蓋 JOIN/WHERE 條件欄位。
• **暫存表/集合** 打破大表關聯爆炸。

遵循上述原則，原本數十小時的作業通常可壓縮至 **秒~分鐘級**。