oracle中递归查询必须用connect by而非with recursive，因其优化器支持更成熟、工具链适配更好、语法更简洁、nocycle容错更明确；查后代时prior id = parent_id，查祖先时prior parent_id = id；必用level、connect_by_isleaf等伪列提升性能与准确性。
oracle 用 connect by 做递归查询，不是“能用”，而是“必须用”——只要数据是树状结构（比如部门、菜单、bom），又不想写存储过程或应用层循环，connect by 就是唯一简洁、高效、原生支持的方案。
为什么非得用 CONNECT BY 而不是 WITH RECURSIVE
Oracle 11gR2 起确实支持 WITH RECURSIVE，但实际项目中它常被绕开，原因很实在：
-
WITH RECURSIVE在 Oracle 中仍属 ANSI 兼容实现，优化器对它的路径剪枝、索引下推支持不如CONNECT BY成熟； - 大量老系统、中间件、BI 工具（如 Oracle APEX、OBIEE）默认适配
CONNECT BY的伪列（如LEVEL、CONNECT_BY_ROOT），换 CTE 可能要重写整套报表逻辑； -
CONNECT BY语法更紧凑，一行START WITH ... CONNECT BY PRIOR ...就能跑通完整树遍历，而 CTE 至少要写两段SELECT+UNION ALL； - 遇到循环引用（比如 A→B→A），
CONNECT BY可直接加NOCYCLE，错误信息明确（ORA-01436: CONNECT BY loop in user data），CTE 报错更难定位。
START WITH 和 CONNECT BY PRIOR 怎么配对才不翻车
核心就一条：先想清楚你要“从哪开始查”，再决定 PRIOR 放在哪边。别死记“父=子”或“子=父”，看方向：
- 查某个节点的所有**后代**（向下）：起点是该节点本身，
PRIOR必须在**父字段侧** →CONNECT BY PRIOR id = parent_id； - 查某个节点的所有**祖先**（向上）：起点还是该节点，但
PRIOR要换到**子字段侧** →CONNECT BY PRIOR parent_id = id； -
START WITH不能省略，否则 Oracle 会以**全表每行作为潜在根节点**启动递归，结果爆炸且性能极差； - 如果表里有多个根（
parent_id IS NULL多行），START WITH parent_id IS NULL是合法的，但必须加过滤（如AND dept_name = '总公司'），否则返回多棵树混在一起。
几个必加的实用伪列和技巧
光查出 ID 没用，真实业务需要层级、路径、排序。这些不是“锦上添花”，而是避免应用层拼接出错的关键：
-
LEVEL：直接反映深度，根为 1；常用于限制只查两层：WHERE LEVEL ； -
SYS_CONNECT_BY_PATH(column, '→')：生成可读路径，比如→总公司→技术部→开发一组；注意它会在开头多一个分隔符，需用SUBSTR(..., 2)截掉； -
CONNECT_BY_ROOT column：快速拿到当前行所属树的根节点值，比如统计每个叶子部门的“归属总公司名称”，不用反复 JOIN； -
ORDER SIBLINGS BY column：控制**同级节点**顺序（不是全局排序！），比如按dept_order排，否则 Oracle 返回顺序不可控； - 加
NOCYCLE：哪怕你确信没环，也建议加上 —— 它让查询不报错，同时生成CONNECT_BY_ISCYCLE列标记可疑行，比停服修数据成本低得多。
容易被忽略的性能雷区
递归查询慢，90% 不是因为语法，而是没管住数据规模：
- 确保
parent_id字段有索引（最好是(parent_id, id)联合索引），否则每次递归都要全表扫； - 避免在
CONNECT BY条件里用函数，比如CONNECT BY PRIOR UPPER(id) = UPPER(parent_id)，会导致索引失效； - 不在
WHERE子句里对递归中间结果过滤（如WHERE LEVEL > 1 AND name LIKE '%开发%'），应改用START WITH或提前在锚点里过滤； - 如果只关心叶子节点，别用
NOT EXISTS后过滤，而是在递归 CTE 或CONNECT BY结果里加CONNECT_BY_ISLEAF = 1—— 这个伪列是 Oracle 内置计算的，比子查询快得多。
真正麻烦的从来不是写对第一行 START WITH，而是当树深超过 5 层、节点数破万时，如何让 LEVEL 不崩、SYS_CONNECT_BY_PATH 不截断、ORDER SIBLINGS BY 不让执行计划退化成嵌套循环。这些细节，往往比语法本身更决定上线成败。
