Oracle 遞迴查詢 CONNECT BY 處理父子層級資料完整教學

前言

Oracle 獨有的 CONNECT BY 語法能以單一 SQL 遞迴遍歷樹狀結構 (組織架構、物料清單 BOM、目錄分類)，比應用層遞迴查詢高效數十倍。本文詳細解析語法、偽欄位、排序、循環偵測與效能技巧。

核心語法

SELECT column_list
FROM   table_name
WHERE  filter_conditions
START WITH root_condition           -- 根節點條件
CONNECT BY PRIOR child_col = parent_col  -- 遞迴關聯 (PRIOR 指向父行)
[ORDER SIBLINGS BY sort_col];       -- 同層排序

關鍵字解析

• START WITH：定義樹的**根節點** (可多行)，若省略則全表每行皆視為根 (產生森林)。
• CONNECT BY PRIOR：指定**父→子**關聯方向。
  • PRIOR child = parent：從父找子 (向下遍歷，最常用)。
  • PRIOR parent = child：從子找父 (向上遍歷，如查詢某節點所有祖先)。
• ORDER SIBLINGS BY：僅對**同層兄弟節點**排序，不改變父子層級順序。

實戰範例：任務分解樹

資料結構

TASK_ID  TASK_NAME  PARENT_TASK_ID
000001   T1         NULL
000002   T11        000001
000003   T111       000002
000004   T1111      000003
000005   T12        000001
000006   T121       000005
000007   T1211      000006

向下遍歷 (根到葉)

SELECT task_id, task_name, parent_task_id,
       LEVEL AS lvl,
       LPAD(' ', 2*(LEVEL-1)) || task_name AS tree_view
FROM   t_task
START WITH task_id = '000001'
CONNECT BY PRIOR task_id = parent_task_id
ORDER SIBLINGS BY task_id;

輸出：

TASK_ID TASK_NAME PARENT_TASK_ID LVL TREE_VIEW
000001  T1        (null)          1   T1
000002  T11       000001          2     T11
000003  T111      000002          3       T111
000004  T1111     000003          4         T1111
000005  T12       000001          2     T12
000006  T121      000005          3       T121
000007  T1211     000006          4         T1211

實用偽欄位與函數

循環偵測與防禦

-- 11g+ NOCYCLE 關鍵字：偵測到循環自動停止該分支，不報錯
SELECT ... 
FROM   t_task
START WITH task_id = '000001'
CONNECT BY NOCYCLE PRIOR task_id = parent_task_id;

搭配 CONNECT_BY_ISCYCLE 識別有問題的資料行。

效能優化關鍵

1. 父/子欄位建立索引：CREATE INDEX idx_task_parent ON t_task(parent_task_id); 遞迴查找極度依賴索引。
2. START WITH 條件具體化：避免全表掃描尋找根節點，必要時加 Hint /*+ INDEX(t idx_task_parent) */。
3. 限制回傳欄位：僅 SELECT 必需欄位，減少投影開銷。
4. 大樹分頁**：Oracle 12c+ 可用 OFFSET ... FETCH 配合 CONNECT BY (需子查詢包裝)。

替代方案：遞迴 CTE (WITH RECURSIVE)

ANSI SQL 標準寫法，可移植至 PostgreSQL、SQL Server、MySQL 8.0+：

WITH RECURSIVE task_tree AS (
  SELECT task_id, task_name, parent_task_id, 1 AS lvl
  FROM   t_task
  WHERE  task_id = '000001'
  UNION ALL
  SELECT t.task_id, t.task_name, t.parent_task_id, tt.lvl + 1
  FROM   t_task t
  JOIN   task_tree tt ON t.parent_task_id = tt.task_id
  WHERE  tt.lvl < 10
)
SELECT * FROM task_tree ORDER BY lvl, task_id;