oracle递归查询首选connect by（10g及以下唯一选择，11gr2+下性能与控制更优），方向需明确：查子孙用prior parent_id=child_id，查祖先用prior child_id=parent_id；where须慎放，避免误删子树；order siblings by保障层级有序。
Oracle 中递归查询有两个主流方式：START WITH ... CONNECT BY（原生、高效、必须掌握）和 WITH RECURSIVE（ANSI 标准、11gR2+ 支持、写法更通用）。选哪个不取决于“新旧”，而看你的 Oracle 版本、性能要求和团队习惯——10g 及以下只能用前者；11gR2 以上两者都行，但 CONNECT BY 在深度树遍历、层级过滤等场景仍更直接、执行计划更可控。
CONNECT BY PRIOR 方向写反是最常见的死因
方向错，结果全乱。它不是“谁等于谁”，而是“从哪来、往哪去”：
- 查某节点的所有子孙（自上而下）：用
CONNECT BY PRIOR parent_id = child_id✅
含义：上一行的parent_id等于当前行的child_id，即“上一行是父，这一行是子” - 查某节点的所有祖先（自下而上）：用
CONNECT BY PRIOR child_id = parent_id✅
含义：上一行的child_id等于当前行的parent_id，即“上一行是子，这一行是父” -
CONNECT BY PRIOR child_id = parent_id却配START WITH root_id❌——逻辑冲突，常返回空或意外单行
一个快速验证法：把 PRIOR 想成“上一行的值”，再代入等式左边，看是否自然成立。比如员工表中 manager_id 指向上级，要查 CEO（id=100）的所有下属，就该是 CONNECT BY PRIOR employee_id = manager_id。
WHERE 条件放错位置会让整棵子树消失
写在 CONNECT BY 外面的 WHERE 是全局后过滤，Oracle 先递归完所有节点，再砍掉不满足条件的行——中间某个节点被砍掉，它下面所有合法子节点也跟着没了。
- 错误写法：
SELECT * FROM dept START WITH id = 1 CONNECT BY PRIOR id = parent_id WHERE status = 'ACTIVE'
→ 若 id=5 的部门 status='INACTIVE'，那 id=5 的所有子部门（哪怕 status='ACTIVE'）都不会出现 - 正确做法一（推荐）：把条件塞进
CONNECT BY：CONNECT BY PRIOR id = parent_id AND status = 'ACTIVE'
→ 这个AND只约束子节点，根节点不受影响 - 正确做法二：提前子查询过滤：
SELECT * FROM (SELECT * FROM dept WHERE status = 'ACTIVE') d START WITH d.id = 1 CONNECT BY PRIOR d.id = d.parent_id
LEVEL 和 ORDER SIBLINGS BY 不是同一件事
LEVEL 是伪列，只告诉你当前行离根有多远（根为 1），但它不控制显示顺序。你看到层级错乱，不是 LEVEL 没用对，而是漏了 ORDER SIBLINGS BY。
-
ORDER BY dept_name→ 全局排序，树结构被彻底打散 -
ORDER SIBLINGS BY dept_name→ 同一级的兄弟节点按名称排序，树形结构完整保留 - 不写
ORDER SIBLINGS BY时，Oracle 按数据块物理顺序返回，每次执行结果可能不同——生产环境务必显式指定 - 缩进常用
LPAD(' ', (LEVEL-1)*2) || dept_name，但注意：若用了NOCYCLE，循环点之后的LEVEL不再增长，此时不能靠LEVEL做业务判断（比如“只取 LEVEL
WITH RECURSIVE 写法更通用，但 Oracle 里有细节坑
虽然语法接近 PostgreSQL/MySQL，但在 Oracle 中要注意：
- 必须显式声明 CTE 列名，且类型需与锚成员一致，否则报
ORA-32033 - 递归部分的
UNION ALL左右两边列数、类型、顺序必须严格一致 - Oracle 对递归深度默认无限制，但实际受
MAXDEPTH隐式约束（可通过/*+ NOCYCLE */或CYCLE子句显式处理环） - 性能上，简单树查
CONNECT BY通常更快；复杂逻辑（如多表 JOIN、聚合后再递归）用WITH RECURSIVE更易读、调试
最易被忽略的是：当树很深或数据量大时，CONNECT BY 的执行计划里常出现 CONNECT BY PUMP，这是 Oracle 专用的迭代算子，优化器对它的代价估算有时不准——如果发现慢，先看是否缺了 parent_id 上的索引，而不是急着换 CTE。
