Oracle 11g 遞迴查詢搭配 EXISTS 執行計劃異常分析與優化：從隱含參數到 UNNEST Hint 解決方案

Oracle 11g 遞迴查詢 EXISTS 效能問題分析與優化

某遞迴查詢在 10g 執行迅速，升級 11.2.0.4 後執行超過 2 小時。經分析執行計劃，發現 EXISTS 子查詢未展開導致 CONNECT BY NO FILTERING 效能劣化。嘗試隱含參數、Hint 失效後，最終以 IN 替換 EXISTS 或加上 UNNEST Hint 解決。

問題 SQL

SELECT *
FROM (SELECT DISTINCT A.*
      FROM GG_MATERIAL_CLASSIFY A
      CONNECT BY PRIOR PARENT_CLASSIFY_ID = CLASSIFY_ID
      START WITH EXISTS (
        SELECT DISTINCT M.CLASSIFY_ID
        FROM GG_DISTRIBUTION D, GG_MATERIAL M
        WHERE D.MATERIAL_ID = M.MATERIAL_ID
          AND A.CLASSIFY_ID = M.CLASSIFY_ID
          AND D.ACTUAL_QTY > 0
          AND D.DATA_AREA LIKE '03%'
      )) B
WHERE B.PARENT_CLASSIFY_ID = '201'
ORDER BY B.CODE ASC;

原始執行計劃關鍵點

• Operation 4: CONNECT BY NO FILTERING WITH SW (UNIQUE)
• Predicate: FILTER( EXISTS (SELECT 0 ... WHERE ... AND M.CLASSIFY_ID=:B1 ...) ) → 子查詢未展開，導致逐行執行。

嘗試方案 1：隱含參數 (Session 級別)

ALTER SESSION SET "_optimizer_connect_by_elim_dups" = FALSE;
ALTER SESSION SET "_connect_by_use_union_all" = "OLD_PLAN_MODE";

執行時間縮至 4.39 秒，計劃轉為 CONNECT BY WITH FILTERING，但不建議生產環境使用隱含參數。

嘗試方案 2：CONNECT_BY_FILTERING Hint

SELECT /*+ CONNECT_BY_FILTERING */ ...

失效，執行仍超時 (需新 Session 測試)。

終極解決方案：改用 IN 或 UNNEST Hint

方法 A：EXISTS → IN

START WITH CLASSIFY_ID IN (
  SELECT DISTINCT M.CLASSIFY_ID
  FROM GG_DISTRIBUTION D, GG_MATERIAL M
  WHERE D.MATERIAL_ID = M.MATERIAL_ID
    AND D.ACTUAL_QTY > 0
    AND D.DATA_AREA LIKE '03%'
)

執行 1 秒，計劃出現 HASH JOIN SEMI 與 CONNECT BY WITHOUT FILTERING (UNIQUE)。

方法 B：保留 EXISTS + UNNEST Hint

START WITH EXISTS (
  SELECT /*+ UNNEST */ DISTINCT M.CLASSIFY_ID
  FROM ...
)

執行 1.18 秒，計劃同方法 A，Predicate 顯示 A.CLASSIFY_ID=ITEM_0 確認子查詢已展開。

結論與建議

1. Oracle 11g 優化器對 EXISTS 子查詢在 CONNECT BY 中展開策略改變，易導致效能退化。
2. 優先嘗試改寫為 IN (語意相同且穩定)。
3. 若需保留 EXISTS，加上 /*+ UNNEST */ 強制子查詢展開。
4. 避免使用隱含參數或不可靠 Hint。