Oracle CONNECT BY PRIOR 遞歸查詢：從葉子節點反向尋找根節點

場景

表 `TRANS_INST` 存儲樹狀結構：

• `TRANS_ID`：子節點 ID
• `UP_TRANS_ID`：父節點 ID

需求：給定任一節點 (如 `480242`)，找出該節點為根的子樹中所有葉子節點。

核心 SQL

SELECT trans_id
FROM trans_inst
WHERE CONNECT_BY_ISLEAF = 1
START WITH trans_id = 480242
CONNECT BY PRIOR up_trans_id = trans_id;

關鍵字解析

正向 vs 反向遍歷對照

實用擴展

1. 顯示完整路徑 (SYS_CONNECT_BY_PATH)

SELECT trans_id,
       SYS_CONNECT_BY_PATH(trans_id, ' → ') AS path_from_root
FROM trans_inst
WHERE CONNECT_BY_ISLEAF = 1
START WITH trans_id = 480242
CONNECT BY PRIOR up_trans_id = trans_id;

2. 限制遞歸深度

SELECT trans_id
FROM trans_inst
WHERE CONNECT_BY_ISLEAF = 1
  AND LEVEL <= 5  -- 最多 5 層
START WITH trans_id = 480242
CONNECT BY PRIOR up_trans_id = trans_id;

3. 循環檢測 (NOCYCLE)

SELECT trans_id
FROM trans_inst
WHERE CONNECT_BY_ISLEAF = 1
START WITH trans_id = 480242
CONNECT BY NOCYCLE PRIOR up_trans_id = trans_id;
-- 若資料有環，NOCYCLE 避免無限循環，並可用 CONNECT_BY_ISCYCLE 檢測

4. 12c+ 標準遞歸 CTE 寫法 (更符合 ANSI SQL)

WITH RECURSIVE tree AS (
  SELECT trans_id, up_trans_id, 1 AS lvl
  FROM trans_inst
  WHERE trans_id = 480242
  UNION ALL
  SELECT t.trans_id, t.up_trans_id, tree.lvl + 1
  FROM trans_inst t
  JOIN tree ON t.up_trans_id = tree.trans_id
  WHERE tree.lvl < 100  -- 防無限遞歸
)
SELECT trans_id FROM tree
WHERE trans_id NOT IN (SELECT up_trans_id FROM trans_inst);  -- 葉子節點

效能提示

• 在 `TRANS_ID`、`UP_TRANS_ID` 建立索引。
• 大樹結構考慮物化路徑 或嵌套集合 模型替代遞歸查詢。
• 避免在高併發核心鏈路使用 `CONNECT BY`，改用應用層快取或預計算。