Oracle CONNECT BY PRIOR 遞歸查詢完全指南：樹狀結構遍歷、PRIOR 位置語義、LEVEL 應用與效能優化

Oracle 階層式查詢：CONNECT BY PRIOR 深度解析

Oracle CONNECT BY PRIOR 是處理樹狀/層級結構資料（組織架構、物料清單、目錄分類）的標準 SQL 擴展。本文系統講解語法結構、遍歷邏輯、PRIOR 位置決定的遍歷方向、LEVEL 偽欄應用、節點/分支剪枝、排序顯示及效能優化技巧。

一、基本語法結構

SELECT column_list
FROM table_name
[WHERE condition3]          -- 過濾條件，作用於最終結果集
[START WITH condition1]     -- 根節點定義（可選，省略則所有滿足條件行為根）
CONNECT BY condition2;      -- 遞歸連接條件（必須包含 PRIOR）

二、核心概念：PRIOR 位置決定遍歷方向

PRIOR 必須放在連接條件等號兩側之一，表示「父節點」欄位：

• 自頂向下： CONNECT BY PRIOR parent_id = child_id
  從根節點出發，遞歸查找子節點。
  例：CONNECT BY PRIOR empno = mgr（empno 為父，mgr 為子）
• 自底向上： CONNECT BY child_id = PRIOR parent_id
  從葉節點出發，遞歸查找父節點。
  例：CONNECT BY mgr = PRIOR empno

⚠️ PRIOR 只能出現一次，且必須在等號直邊，不能用在運算式內部。

三、遍歷演算法：深度優先前序走訪

1. 從 START WITH 確定的根節點開始（可多根，形成森林）。
2. 訪問當前節點（輸出結果）。
3. 檢查是否有未訪問的子節點：
   有 → 移至最左側未訪問子節點，回步驟 2。
   無 → 步驟 4。
4. 若為根節點 → 遍歷結束。
   否則 → 返回父節點，回步驟 3。

此為標準樹的深度優先前序走訪，輸出順序即為遍歷順序。

四、實戰範例：EMP 表組織架構

-- 自頂向下：從 KING(7839) 往下查所有下屬
SELECT empno, ename, mgr, LEVEL
FROM emp
START WITH empno = 7839
CONNECT BY PRIOR empno = mgr;

-- 自底向上：從 MILLER(7934) 往上查所有主管
SELECT empno, ename, mgr, LEVEL
FROM emp
START WITH empno = 7934
CONNECT BY mgr = PRIOR empno;

五、關鍵偽欄與函數

六、節點與分支剪枝

• WHERE 子句：過濾最終結果集，不影響遍歷過程。被過濾節點的子孫仍會被遍歷並可能出現在結果中。
• CONNECT BY 條件中加入過濾：影響遍歷，可實現分支剪枝。
  例：CONNECT BY PRIOR empno = mgr AND deptno != 10（跳過 10 號部門整個子樹）。

七、排序顯示

• ORDER SIBLINGS BY col：同層節點按 col 排序，保持父子層級關係。
  例：ORDER SIBLINGS BY ename
• 一般 ORDER BY：打破層級順序，僅按欄位排序最終結果。

八、效能優化建議

1. 索引支撐： 父鍵欄（如 mgr）與子鍵欄（empno）均建立索引，加速父子關聯查找。
2. 避免全表掃描： START WITH 條件應高選擇性，利用索引快速定位根節點集合。
3. 限制遍歷深度： 11g+ 可用 WHERE LEVEL <= 5 限制最大深度，防止失控遞歸。
4. 大量資料考慮： 萬行以上樹結構，遞歸查詢可能消耗大量 PGA/TEMP。評估改用遞歸 WITH 子查詢 或應用層分頁載入。
5. 統計資訊： 確保表/索引統計最新，優化器才能正確估算階層查詢基數。

九、QBC (Query By Criteria) 中實現 CONNECT BY

主流 ORM 框架（Hibernate、MyBatis）不直接支援 CONNECT BY。建議方案：

• 將階層查詢封裝為資料庫視圖或管線函數，應用層查詢視圖/函數。
• MyBatis 使用 <select> 直接編寫原生 SQL，利用 resultMap 映射遞歸結果集。
• 應用層遞歸：查詢扁平化父子關係表，Java 端組裝樹結構（適用淺層、小數據量）。