Oracle CONNECT BY 樹狀查詢效能陷阱：優化器 Cardinality 估算缺陷與實戰規避指南

問題背景：優化器對 CONNECT BY 基數估算嚴重失準

真實生產系統 BSS_ORG 表 (5,739 筆)，測試環境 Oracle 9i/10g/11g 一致：

• 無索引時：優化器估算 5,739 行 (全表筆數)，實際返回 4 行
• 有索引時：優化器估算 6 行 (總筆數 / DISTINCT 值)，實際返回 4 行
• 估算值與實際值差異高達 1000 倍，導致執行計劃災難性偏差

測試案例與執行計劃對比

表結構與統計資訊

BSS_ORG_ID          NOT NULL NUMBER(9)     -- PK, 5739 distinct
NAME                NOT NULL VARCHAR2(64)   -- 5034 distinct
BSS_PARENT_ORG_ID            NUMBER(9)     -- 905 distinct, 形成樹狀結構
BSS_ORG_LEVEL_ID    NOT NULL NUMBER(3)     -- 6 distinct
STATE               NOT NULL VARCHAR2(3)   -- 2 distinct

查詢 1：根節點查全樹 (返回 5739 行)

SELECT * FROM bss_org t
START WITH bss_org_id = 832044754
CONNECT BY bss_parent_org_id = PRIOR bss_org_id;

查詢 2：葉節點查單路徑 (返回 4 行)

SELECT * FROM bss_org t
START WITH bss_org_id = 832044754  -- 同一根節點
CONNECT BY bss_parent_org_id = PRIOR bss_org_id;

現象：同一 SQL 結構，不同 START WITH 值，實際返回行數差異巨大，但優化器給出相同估算 (5739 或 6)，完全無視 START WITH 過濾效果。

根因分析

• 優化器不分析 START WITH 條件的過濾度
• 採用簡化啟發式：全表筆數 或 總筆數 / 父鍵 DISTINCT 值
• 導致後續 JOIN 順序、JOIN 方法 (HASH vs NESTED LOOP) 選擇錯誤
• 在複雜 SQL 中作為子查詢時，錯誤基數傳播放大效能災難

實戰案例：索引建立前後的計劃變化

無索引 (9i) — 全表掃描 + HASH JOIN

| Id | Operation                      | Name     | Rows  |
|  1 | CONNECT BY WITH FILTERING      |          |       |
|  2 | TABLE ACCESS BY INDEX ROWID    | BSS_ORG  |       |
|  3 | INDEX UNIQUE SCAN              | PK_BSS_ORG| 1     |
|  4 | HASH JOIN                      |          |       |
|  5 | CONNECT BY PUMP                |          |       |
|  6 | TABLE ACCESS FULL              | BSS_ORG  | 5739  |
|  7 | TABLE ACCESS FULL              | BSS_ORG  | 5739  |

估算 5739 行 → 選擇 HASH JOIN → 效能極差

建立 BSS_PARENT_ORG_ID 索引後

• 9i：計劃不變，估算仍 5739
• 11g：計劃變為 CONNECT BY NO FILTERING，估算變 6 行 → 效能大幅提升

規避與優化策略

1. 強制索引存取 (舊版本)

SELECT /*+ INDEX(t idx_bss_parent_org_id) */ * FROM bss_org t
START WITH bss_org_id = :root_id
CONNECT BY bss_parent_org_id = PRIOR bss_org_id;

2. 使用 CTE 物化中間結果

WITH tree_path AS (
  SELECT /*+ MATERIALIZE */ * FROM bss_org
  START WITH bss_org_id = :root_id
  CONNECT BY bss_parent_org_id = PRIOR bss_org_id
)
SELECT * FROM tree_path WHERE ...;  -- 後續查詢使用物化結果

3. 改用遞歸 CTE (11gR2+) — 優化器支援更好

WITH RECURSIVE org_tree (org_id, parent_id, lvl) AS (
  SELECT bss_org_id, bss_parent_org_id, 1 FROM bss_org WHERE bss_org_id = :root_id
  UNION ALL
  SELECT c.bss_org_id, c.bss_parent_org_id, p.lvl + 1
  FROM bss_org c JOIN org_tree p ON c.bss_parent_org_id = p.org_id
)
SELECT * FROM org_tree;

4. 收集直方圖統計資訊

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'BSS_ORG', 
  METHOD_OPT => 'FOR COLUMNS SIZE 254 BSS_PARENT_ORG_ID');

5. 必要時使用 CARDINALITY / OPT_ESTIMATE Hint

SELECT /*+ CARDINALITY(t 10) */ * FROM bss_org t
START WITH bss_org_id = :root_id
CONNECT BY bss_parent_org_id = PRIOR bss_org_id;

總結建議

1. 樹狀查詢必須在父鍵欄位建立索引
2. 11g+ 優先使用遞歸 CTE 替代 CONNECT BY
3. 複雜 SQL 中隔離樹狀查詢為 CTE，避免基數錯誤傳播
4. 定期收集關鍵欄位直方圖統計
5. 效能異常時檢查執行計劃 E-Rows vs A-Rows 差異