Oracle 效能調優實戰：EXISTS/IN 選擇、BULK COLLECT 陷阱與批量刪除最佳模式

子查詢改寫層級

• IN → EXISTS：半連接機制，通常明顯優於 IN（特別是外部表大、內部表小）。
• EXISTS → 表連接：必須將大表放在 FROM 子句前面（驅動表），小表在後，否則連接反而更慢。

BULK COLLECT ... LIMIT 循環陷阱

常見模式：

FETCH cur BULK COLLECT INTO coll LIMIT n;
FOR i IN 1..coll.COUNT LOOP ... END LOOP;

三種循環寫法差異

批量刪除最佳實踐：綁定變量 + FORALL + 分批 COMMIT

PROCEDURE purge_stale_data(p_batch_size IN NUMBER DEFAULT 1000) IS
  TYPE rid_arr IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  l_rids rid_arr;
BEGIN
  LOOP
    SELECT rowid BULK COLLECT INTO l_rids
    FROM   big_table
    WHERE  created_dt < SYSDATE - 365
    AND    ROWNUM <= p_batch_size;

    EXIT WHEN l_rids.COUNT = 0;

    FORALL i IN 1..l_rids.COUNT
      DELETE FROM big_table WHERE rowid = l_rids(i);

    COMMIT;  -- 分批提交，控制 UNDO/REDO 暴增
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

關鍵點：

1. ROWNUM <= batch_size 限制單次處理量，避免長鎖表、UNDO 爆滿。
2. FORALL 將 DML 打包成單次網路往返，效能較逐行 `LOOP` 提升 10~100 倍。
3. 批次大小依主機 CPU/IO 調校，典型 1,000~10,000。

EXIT 標籤跳出多層循環

<<outer_loop>>
FOR i IN 1..2 LOOP
  FOR j IN 1..4 LOOP
    DBMS_OUTPUT.PUT_LINE('i='||i||' j='||j);
    EXIT outer_loop WHEN j = 3;  -- 直接跳出雙層
  END LOOP;
END LOOP outer_loop;

輸出：i=1 j=1、i=1 j=2、i=1 j=3 即停止，外層不再執行 i=2。

總結

調優不只是改 SQL，更是掌握 PL/SQL 批量處理邊界條件、循環語義、提交頻率等工程細節。1..COUNT 寫法、FORALL 分批刪除、標籤 `EXIT` 是每位開發者必備的「肌肉記憶」。