Oracle 階層式查詢完全指南：CONNECT BY 從入門到進階實戰

前言

Oracle 的階層式查詢（Hierarchical Query）是處理樹狀結構資料（組織架構、物料清單、目錄分類等）的利器。本文系統梳理 CONNECT BY、START WITH、SYS_CONNECT_BY_PATH、CONNECT_BY_ISLEAF 等核心語法，並深入探討 WHERE 子句執行順序對結果的影響，最後揭秘 CONNECT BY LEVEL 這一「非標準但極實用」的技巧。

一、測試環境建置

CREATE TABLE t_hierarchical (
    emp NUMBER PRIMARY KEY,       -- 員工編號
    mgr NUMBER,                   -- 直屬主管編號（自我參考外鍵）
    emp_name VARCHAR2(20)         -- 員工姓名
);

INSERT INTO t_hierarchical VALUES (1, NULL, 'AA');  -- 大老板
INSERT INTO t_hierarchical VALUES (2, 1, 'BB');
INSERT INTO t_hierarchical VALUES (3, 2, 'CC');
INSERT INTO t_hierarchical VALUES (4, 3, 'DD');
INSERT INTO t_hierarchical VALUES (5, 2, 'EE');
INSERT INTO t_hierarchical VALUES (6, 3, 'FF');
COMMIT;

樹狀結構：
1(AA) → 2(BB) → 3(CC) → 4(DD)
                      └→ 6(FF)
                      └→ 5(EE)

二、核心語法詳解

2.1 CONNECT BY：定義父子關係

SELECT emp, mgr, LEVEL
FROM t_hierarchical
CONNECT BY PRIOR emp = mgr
ORDER BY emp;

• PRIOR emp = mgr：指定「上一筆記錄的 emp = 當前記錄的 mgr」，即從父節點走向子節點（自上而下）。
• LEVEL 偽欄：根節點為 1，每遞迴一層 +1。
• 無 START WITH 時，每一筆記錄都會被視為根節點嘗試遞迴，產生笛卡兒積般的結果（見原文輸出）。

2.2 START WITH：指定根節點

SELECT emp, mgr, LEVEL
FROM t_hierarchical
START WITH emp = 1
CONNECT BY PRIOR emp = mgr;

僅從 emp=1 開始遞迴，結果清晰對應組織層級：

EMP  MGR  LEVEL
1    -    1
2    1    2
3    2    3
4    3    4
6    3    4
5    2    3

2.3 SYS_CONNECT_BY_PATH：輸出完整路徑

SELECT emp, mgr, LEVEL,
       SYS_CONNECT_BY_PATH(emp, '/') AS path
FROM t_hierarchical
START WITH emp = 1
CONNECT BY PRIOR emp = mgr;

輸出：/1、/1/2、/1/2/3、/1/2/3/4、/1/2/3/6、/1/2/5。極適合麵包屑導航、完整路徑顯示等場景。

2.4 CONNECT_BY_ISLEAF：識別葉子節點

SELECT emp, mgr, LEVEL,
       SYS_CONNECT_BY_PATH(emp, '/') AS path
FROM t_hierarchical
WHERE CONNECT_BY_ISLEAF = 1
START WITH emp = 1
CONNECT BY PRIOR emp = mgr;

僅輸出無子節點的葉子：4、6、5。常用於「只顯示最底層明細」的報表需求。

三、進階陷阱：WHERE 子句執行順序與 JOIN 陷阱

3.1 執行順序鐵律

Oracle 文檔明確規定階層查詢執行順序：

1. JOIN（含 WHERE 中的連接條件）
2. CONNECT BY（遞迴建構樹狀結構）
3. 其他 WHERE 條件（過濾已建構的結果集）

3.2 實戰案例：註釋表關聯

假設註釋表 t_desc(emp, lang, emp_desc)，每員工有中/英兩筆註釋。需在階層查詢中帶出中文註釋。

錯誤寫法（WHERE 過濾在 CONNECT BY 之後）

SELECT t.emp, t.mgr, td.emp_desc, LEVEL
FROM t_hierarchical t, t_desc td
WHERE t.emp = td.emp
  AND td.lang = 'chinese'   -- 非 JOIN 條件，視為一般 WHERE
START WITH t.emp = 1
CONNECT BY PRIOR t.emp = t.mgr;

結果：因 td.lang='chinese' 在遞迴後過濾，遞迴過程中每員工對應兩筆註釋（中/英），導致樹狀結構膨脹、產生大量重複行。

正確寫法（JOIN ON 內包含過濾，在 CONNECT BY 之前）

SELECT t.emp, t.mgr, td.emp_desc, LEVEL
FROM t_hierarchical t
JOIN t_desc td ON (t.emp = td.emp AND td.lang = 'chinese')
START WITH t.emp = 1
CONNECT BY PRIOR t.emp = t.mgr;

結果：遞迴前已完成過濾，每員工僅一筆中文註釋，樹狀結構正確。

3.3 核心啟示

階層查詢中，所有影響樹狀結構形狀的過濾條件，必須寫在 JOIN ON 子句中，不可寫在 WHERE 子句。

四、特殊技巧：CONNECT BY LEVEL 產生序列

SELECT LEVEL FROM dual CONNECT BY LEVEL <= 6;

輸出：1,2,3,4,5,6。

4.1 原理解析

• 標準語法要求 CONNECT BY 條件中至少包含一個 PRIOR 運算子，但 LEVEL 是偽欄，非實際欄位。
• Oracle 內部將其視為一種特殊的「計數器遞迴」：從 LEVEL=1 開始，每次遞迴 LEVEL+1，直到條件 LEVEL<=6 不成立。
• 曾被視為 Bug，但 10g/11g/12c/19c/21c 均穩定支援，已成事實標準。

4.2 實用場景

• 快速生成日期序列：SELECT TRUNC(SYSDATE) + LEVEL - 1 FROM dual CONNECT BY LEVEL <= 30;
• 拆分逗號分隔字串、填補缺失日期、生成測試資料等。

4.3 注意事項

• 若寫成 CONNECT BY 1=1 將產生無限迴圈（直到資源耗盡報錯）。
• 大量資料生成時建議加上 /*+ MATERIALIZE */ 或使用 WITH 子查詢物化，避免重複計算。

五、總結速查表

六、最佳實踐建議

1. 永遠加上 START WITH 限定根節點，避免全表遞迴效能災難。
2. 涉及關聯表時，嚴格區分「樹形結構決定條件」（JOIN ON）與「結果展示過濾」（WHERE）。
3. 大型樹狀資料考慮建立 CONNECT BY 專用索引（如 mgr 索引）並收集統計資訊。
4. 12c 起可使用遞迴 WITH（RECURSIVE WITH）替代，語法更標準（ANSI SQL），移植性更好。