CONNECT BY 到递归 CTE：树查询迁移详解

1. 对应简历段落

这篇文章对应简历中“参与 Oracle 到 OceanBase 信创迁移，负责组织架构、客户归属、销售团队等树形查询从 CONNECT BY 到递归 CTE 的改造，并处理层级、排序、环路和性能问题”等经历。

面试里讲树查询迁移，不能只背一段 WITH RECURSIVE 模板。资深面试官会继续问：CONNECT BY PRIOR 的方向怎么判断？LEVEL、SYS_CONNECT_BY_PATH、CONNECT_BY_ROOT 怎么替代？ORDER SIBLINGS BY 怎么保持？如果数据有环怎么办？递归 CTE 在大组织树上会不会慢？这些问题才是项目经验的分水岭。

可以把这段经历定位为：我负责把历史系统中强依赖 Oracle 层级查询的 SQL 梳理出来，按“向下查子树、向上查父链、查根节点、查路径、查叶子节点、按兄弟排序展示”分类，然后分别用递归 CTE、路径字段、闭包表或应用层缓存改造，并通过结果对比工具校验节点集合、层级、路径和排序。

2. 业务背景

保险、银行、销售管理类系统里树形数据非常多。典型例子包括销售机构树、部门树、代理人团队树、客户归属树、产品分类树、地区行政区划树、菜单权限树等。老 Oracle 系统里这类查询常用 CONNECT BY：

SELECT org_id,
       parent_org_id,
       org_name,
       LEVEL AS org_level
FROM sales_org
START WITH org_id = :rootOrgId
CONNECT BY PRIOR org_id = parent_org_id;

业务页面可能依赖它展示“某个机构下所有子机构”，权限系统可能依赖它判断“当前用户能看哪些下级机构”，报表统计可能依赖它聚合“某分公司及所有下级机构业绩”。一旦树查询迁移出错，影响面非常大：用户看不到数据、看到越权数据、统计口径变小或变大、前端树顺序混乱。

迁移到 OceanBase MySQL 模式后，Oracle 的 CONNECT BY 不能直接使用，通常要改成递归 CTE：

WITH RECURSIVE org_tree AS (
    SELECT org_id, parent_org_id, org_name, 1 AS org_level
    FROM sales_org
    WHERE org_id = :rootOrgId

    UNION ALL

    SELECT c.org_id, c.parent_org_id, c.org_name, p.org_level + 1
    FROM sales_org c
    JOIN org_tree p ON c.parent_org_id = p.org_id
)
SELECT *
FROM org_tree;

但树查询迁移的真实难点在于历史 SQL 往往使用了 Oracle 的一整套层级查询能力，不只是查子节点。比如用 LEVEL 控制层级，用 CONNECT_BY_ISLEAF 判断叶子，用 SYS_CONNECT_BY_PATH 拼路径，用 ORDER SIBLINGS BY 控制兄弟节点排序，用 CONNECT_BY_ROOT 取根机构，用 NOCYCLE 防止环。

3. 核心原理

CONNECT BY 的核心是“从起点集合出发，按父子关系递归查找”。START WITH 定义根节点，CONNECT BY 定义当前行与下一层行的连接关系。最容易混淆的是 PRIOR 的方向。

START WITH org_id = :rootOrgId
CONNECT BY PRIOR org_id = parent_org_id

这里 PRIOR org_id 指上一层节点的 org_id，等于当前候选行的 parent_org_id，所以语义是“从父节点向下找子节点”。

如果写成：

START WITH org_id = :childOrgId
CONNECT BY PRIOR parent_org_id = org_id

语义就变成“从子节点向上找父节点”。迁移时如果只看字段名，很容易把方向写反。

递归 CTE 由两部分组成：锚点查询和递归查询。

WITH RECURSIVE cte AS (
    -- anchor：第一层
    SELECT ...
    UNION ALL
    -- recursive：下一层
    SELECT child...
    FROM base child
    JOIN cte parent ON ...
)
SELECT * FROM cte;

锚点对应 START WITH，递归部分对应 CONNECT BY。LEVEL 要自己维护，路径要自己拼接，是否叶子节点要额外判断，兄弟排序也要通过路径排序字段实现。

UNION ALL 通常比 UNION 更适合树查询，因为 UNION 会去重并带来额外排序或临时表开销。但如果数据存在环，UNION ALL 可能无限递归，所以要配合深度限制或路径去环。

4. 项目落地

项目里我会先把所有 CONNECT BY SQL 分类。

第一类是向下查子树，用于权限范围、机构下钻、菜单展示。这类最常见，直接用递归 CTE 改造。

第二类是向上查父链，用于查询客户所属机构的上级分公司、团队负责人链路。这类 join 方向与子树查询相反。

第三类是查路径，用于页面展示“总公司/分公司/营业部/团队”。原来可能用 SYS_CONNECT_BY_PATH，迁移后需要在 CTE 中维护 path。

第四类是查叶子或根节点，原来可能用 CONNECT_BY_ISLEAF、CONNECT_BY_ROOT，迁移后要通过 NOT EXISTS 或在 CTE 中携带 root 字段实现。

第五类是大树统计，比如统计某机构及下级机构的保费、客户数、活动转化。这类不能只关注语法，要关注执行计划。递归先得到机构集合，再 join 大业务表时，要保证机构集合能有效参与过滤。

对于小型树，比如菜单和字典，可以考虑应用启动时缓存，减少数据库递归。对于中大型组织树，如果读多写少，可以维护冗余路径字段或闭包表。闭包表结构类似：

CREATE TABLE org_closure (
    ancestor_id   BIGINT NOT NULL,
    descendant_id BIGINT NOT NULL,
    depth         INT NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id)
);

这样查询某机构所有下级只需要：

SELECT descendant_id
FROM org_closure
WHERE ancestor_id = :rootOrgId;

闭包表写入和变更成本更高，但对权限范围和报表统计非常友好。面试中如果能说明“递归 CTE 是兼容改造方案，闭包表是高频读场景的性能方案”，会比只会写 CTE 更有深度。

5. 示例 SQL 或流程

向下查子树，Oracle 原写法：

SELECT org_id,
       parent_org_id,
       org_name,
       LEVEL AS org_level
FROM sales_org
START WITH org_id = :rootOrgId
CONNECT BY PRIOR org_id = parent_org_id
ORDER SIBLINGS BY sort_no;

递归 CTE 改造：

WITH RECURSIVE org_tree AS (
    SELECT org_id,
           parent_org_id,
           org_name,
           sort_no,
           1 AS org_level,
           CAST(LPAD(sort_no, 6, '0') AS CHAR(1000)) AS sort_path,
           CAST(org_id AS CHAR(1000)) AS id_path
    FROM sales_org
    WHERE org_id = :rootOrgId

    UNION ALL

    SELECT c.org_id,
           c.parent_org_id,
           c.org_name,
           c.sort_no,
           p.org_level + 1 AS org_level,
           CONCAT(p.sort_path, '/', LPAD(c.sort_no, 6, '0')) AS sort_path,
           CONCAT(p.id_path, '/', c.org_id) AS id_path
    FROM sales_org c
    JOIN org_tree p ON c.parent_org_id = p.org_id
    WHERE p.org_level < 20
      AND INSTR(CONCAT('/', p.id_path, '/'), CONCAT('/', c.org_id, '/')) = 0
)
SELECT org_id, parent_org_id, org_name, org_level
FROM org_tree
ORDER BY sort_path;

这里 sort_path 用来模拟 ORDER SIBLINGS BY，id_path 用来防环，org_level < 20 是额外保护。实际项目中最大层级要根据业务数据设定，比如机构树一般不会超过 10 层。

向上查父链：

WITH RECURSIVE parent_tree AS (
    SELECT org_id,
           parent_org_id,
           org_name,
           1 AS distance
    FROM sales_org
    WHERE org_id = :currentOrgId

    UNION ALL

    SELECT p.org_id,
           p.parent_org_id,
           p.org_name,
           c.distance + 1
    FROM sales_org p
    JOIN parent_tree c ON c.parent_org_id = p.org_id
    WHERE c.distance < 20
)
SELECT *
FROM parent_tree
ORDER BY distance;

查机构范围并统计保费：

WITH RECURSIVE org_tree AS (
    SELECT org_id
    FROM sales_org
    WHERE org_id = :rootOrgId
    UNION ALL
    SELECT c.org_id
    FROM sales_org c
    JOIN org_tree p ON c.parent_org_id = p.org_id
)
SELECT SUM(o.premium) AS total_premium
FROM policy_order o
JOIN org_tree t ON o.org_id = t.org_id
WHERE o.create_time >= :startTime
  AND o.create_time < :endTime;

配套索引至少要有：

CREATE INDEX idx_sales_org_parent_sort ON sales_org(parent_org_id, sort_no, org_id);
CREATE INDEX idx_policy_org_time ON policy_order(org_id, create_time);

6. 常见坑

第一个坑是递归方向写反。CONNECT BY PRIOR org_id = parent_org_id 是向下查子节点，CTE 中应该是 child.parent_org_id = parent.org_id。如果写成 parent.parent_org_id = child.org_id，结果会变成查父链。

第二个坑是忘记处理层级。Oracle 的 LEVEL 是内置的，CTE 中必须自己 level + 1。如果页面、权限或报表依赖层级，漏掉会导致业务逻辑错误。

第三个坑是忽略兄弟排序。ORDER SIBLINGS BY 不是普通全局排序，它是在每个父节点下对子节点排序。迁移后只写 ORDER BY sort_no 会把不同层级的节点混在一起。要维护 sort_path 或在应用层构树后排序。

第四个坑是没有防环。组织数据理论上是树，生产上可能因为历史导入或人工维护出现环。递归 CTE 如果没有深度限制或路径判断，可能执行异常或消耗大量资源。

第五个坑是递归结果再 join 大表时没有索引。树查询本身可能很快，但 join 保单、客户、交易明细后变慢。要检查业务表上的 org_id + 时间、customer_id 等索引。

第六个坑是把所有场景都用递归 CTE。菜单这种小树可以缓存；权限范围这种高频查询可以闭包表；报表统计可以预聚合。递归 CTE 是工具，不是唯一答案。

7. 面试追问

面试官可能问：CONNECT BY PRIOR a = b 到底谁是父谁是子？

推荐回答：PRIOR 修饰的是上一层记录的字段。CONNECT BY PRIOR org_id = parent_org_id 表示上一层的 org_id 等于当前行的 parent_org_id，所以是父查子。

面试官可能问：递归 CTE 怎么替代 LEVEL 和路径？

回答：在锚点查询里初始化 level = 1，递归查询里 level + 1；路径字段用 CONCAT(parent.path, '/', child.id) 维护。如果要排序，可以维护 sort_path；如果要防环，可以维护 id_path 并判断当前节点是否已出现。

面试官可能问：树查询慢怎么优化？

回答要分场景。先看 parent_id 是否有索引，再看递归结果规模和后续 join。小树可缓存，大树高频读可用闭包表或路径枚举，报表统计可以预聚合。对于递归 CTE 本身，要限制层级、避免无谓列、确认 join 条件走索引。

面试官可能问：怎么验证树查询迁移正确？

可以比较节点集合、节点数量、每个节点层级、父子关系、路径、叶子节点和排序。对于权限类查询，还要抽样验证用户可见机构范围，防止少查或越权。

8. 推荐回答

可以这样说：

“我们系统里组织架构、团队和权限范围大量使用 Oracle CONNECT BY。迁移时我没有直接按模板替换，而是先按向下查子树、向上查父链、路径展示、叶子判断、报表统计分类。标准子树查询改成递归 CTE，锚点对应 START WITH，递归 join 对应 CONNECT BY，同时自己维护 level、path、sort_path。对于 ORDER SIBLINGS BY，我用排序路径保证树展开顺序；对于脏数据环路，增加层级上限和路径去重。高频权限范围查询如果递归成本高，则考虑闭包表或缓存。验证时不仅比数量，还比节点集合、层级、路径和排序。”

这个回答既说明了语法迁移，也体现了数据质量、性能和业务权限意识。

9. 延伸学习路线

第一，系统学习 Oracle START WITH ... CONNECT BY，重点掌握 PRIOR、LEVEL、CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH、ORDER SIBLINGS BY。

第二，学习 SQL 标准递归 CTE，理解锚点查询、递归查询、UNION ALL、递归终止条件和执行代价。

第三，掌握树模型设计，包括邻接表、路径枚举、嵌套集合、闭包表。知道每种模型的读写成本。

第四，结合 Java 学习应用层构树、权限范围缓存、组织树变更事件和缓存失效。

第五，做一次真实压测：同一棵组织树分别用递归 CTE、闭包表、缓存查询，比较延迟、CPU、索引和维护成本。