Oracle效能調優實戰：EXISTS替換IN、表連接順序與BULK COLLECT陷阱

一、子查詢優化：EXISTS vs IN vs 表連接

• EXISTS優於IN：EXISTS採用半連接，找到一筆即停止；IN需掃描完整結果集，效率差異明顯
• 表連接優於EXISTS的前提：FROM子句中大表在前、小表在後，驅動表為小結果集時，Nested Loop Join效能最佳

二、BULK COLLECT LIMIT 迴圈陷阱與最佳實踐

使用SELECT ... BULK COLLECT INTO ... LIMIT ...或FETCH ... BULK COLLECT INTO ... LIMIT ...時，常搭配FOR/FORALL迴圈進行DML操作。

三種FOR迴圈寫法比較

-- 寫法1：FIRST..LAST (有風險)
FOR i IN tmp.FIRST .. tmp.LAST LOOP ... END LOOP;

-- 寫法2：1..COUNT (安全)
FOR i IN 1 .. tmp.COUNT LOOP ... END LOOP;

-- 寫法3：INDICES OF (安全，處理稀疏陣列)
FOR i IN INDICES OF tmp LOOP ... END LOOP;

致命陷阱：寫法1在無資料時會拋出ORA-06502

當BULK COLLECT未抓到資料，集合為空，tmp.FIRST回傳NULL，導致NUMERIC OR VALUE ERROR。巢狀迴圈中內層無法使用EXIT跳出外層，必須改用寫法2或3。

三、批量刪除存儲過程範本（綁定變數+FORALL）

PROCEDURE RemoveBatchRecords(p_batch_size IN NUMBER DEFAULT 1000) AS
  TYPE RowIdArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  v_rowids RowIdArray;
BEGIN
  LOOP
    SELECT rowid BULK COLLECT INTO v_rowids
    FROM target_table
    WHERE filter_condition
    AND ROWNUM <= p_batch_size;
    
    EXIT WHEN v_rowids.COUNT = 0;
    
    FORALL k IN 1 .. v_rowids.COUNT
      DELETE FROM target_table WHERE rowid = v_rowids(k);
    
    COMMIT;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

p_batch_size可依伺服器效能調整（建議500-5000）。

四、EXIT跳出多層迴圈技巧

標準EXIT僅跳出當前層。需跳出多層時，使用標籤：

<<OUTER_LOOP>>
FOR i IN 1..2 LOOP
  <<INNER_LOOP>>
  FOR j IN 1..4 LOOP
    EXIT OUTER_LOOP WHEN j = 3;  -- 直接跳出外層迴圈
  END LOOP INNER_LOOP;
END LOOP OUTER_LOOP;

此技巧在錯誤處理、批量處理中斷等場景極為實用。