搞过 Oracle 层次查询的人基本都有一部血泪史。CONNECT BY写起来很爽跑起来却经常让人怀疑人生数据量一上来明明只有十几行递归逻辑执行计划却像脱了缰的野马。层次查询优化这件事绝不是一个索引就能解决的它牵扯到连接方向、基数估算、剪枝时机以及递归 WITH 的迭代规则。这篇文章我会把CONNECT BY和递归WITH的执行模型拆开用一个从 370 秒压到 1.2 秒的 BOM 展开案例手把手带你走一遍优化流程。适合数据库运维、开发也适合被慢查询折腾到想直接全量加载的兄弟。1. 先搞懂层次查询的执行模型否则优化就是瞎调参1.1 CONNECT BY 的迭代本质CONNECT BY的语法是声明式的让你写“从哪开始、父子怎么连”但数据库实际执行时是严格的迭代。每次迭代以上一轮返回的每一行作为驱动通过PRIOR条件去连接下一层。你可以把它理解成隐藏的嵌套循环每个父行去子表里找自己的孩子孩子再变成父行进入下一轮直到找不到新行。执行计划里常见的CONNECT BY PUMP就是 Oracle 用来缓存上一轮结果、供下一轮继续繁殖的工作区。这个迭代模型决定了两个直接影响性能的点。第一每一层的成本等于“上一层返回行数 × 子表探测成本”所以树的宽度比深度更致命。宽度 100、深度 10最坏情况下总行数是 100^10 级别加什么索引都救不回来深度 100、宽度 2同样要展开 2^100 行。因此优化的第一步永远是估算展开总量是否可控如果总量已经大到不现实应该从业务上限制层级或剪枝而不是在 SQL 里硬扛。我在实际项目里见过不少“一定要一次展开所有层级”的需求最后都是通过业务拆分解决的。1.2 递归 WITH 的执行机制与差异递归 WITHRecursive CTE是另一套执行模型。它用UNION ALL把两个分支拼起来锚点分支先产生第一批行递归分支不断引用 CTE 自身把上一轮产生的新行作为输入继续UNION ALL累积直到某一次迭代不再产生新行为止。Oracle 从 11gR2 开始支持这种写法语义上比CONNECT BY更靠近标准 SQL优化器可以做不少转换。实际优化时最关键的差异有三个。其一递归 WITH 的每个分支独立参与优化锚点分支的过滤条件更容易被推到最内层CONNECT BY则经常遇到“WHERE 过滤被推迟到全部展开后”的情况。其二递归成员中不能使用聚合、DISTINCT、窗口函数等如果业务确实需要先聚合再递归必须把聚合放在锚点或提前物化好的临时表里。其三递归 WITH 对循环控制更灵活可以用CYCLE子句标记环状数据。我通常优先用递归 WITH 改写过大的层次查询因为它逻辑分块更清晰后期排查也方便。当然CONNECT BY在一些场景下写法更紧凑两者并不是谁完全取代谁的关系。2. 优化前先学会看执行计划三分钟定位瓶颈2.1 执行计划里的 CONNECT BY 算子怎么看优化不能靠猜先看执行计划。一个层次查询的执行计划如果高效几乎应该满足三个特征根集很小且走索引或快速定位循环体内部的连接是嵌套循环NESTED LOOPS并且通过索引探测不会在循环体里出现大表全表扫描。如果看到FULL TABLE SCAN出现在下层驱动路径中基本说明连接列上没有合适的索引或者优化器选错了驱动方向。此外还要注意两类“隐含排序”。一类是ORDER SIBLINGS BY引入的兄弟排序另一类是CONNECT_BY_ROOT、SYS_CONNECT_BY_PATH等在结果集上做聚合或窗口的操作。它们会把原本只需要递归展开的查询变成“先展开再排序”数据量一大就是灾难。所以看执行计划时尤其要关注是否出现了不必要的SORT ORDER BY或大窗口临时表操作。很多慢查询不是递归本身慢而是结果集被隐式排序拖住了。2.2 基数估算为什么总不准Oracle 对CONNECT BY的基数估算非常粗糙。默认情况下它根据根集行数、统计信息推导出的平均子代数按照“根集行数 × 平均度^层级数”来估算。问题在于树表的“平均度”在统计信息里很少真实反映往往被严重低估。低估的结果是优化器认为每层只返回几十行于是放心选择嵌套循环逐层探测一旦实际某层返回几百行嵌套循环的成本立刻失控。举个例子根节点 3 个平均子节点 20 个深度 5 层优化器估算约 3×20^5960 万行。如果实际有一颗“畸形树”其中一个父节点挂了十万个叶子实际结果集可能比千万行还要多但执行计划还停留在“估算小、走嵌套循环”的状态性能就会以一个数量级的速度恶化。解决思路是不要只依赖默认统计要为父子连接列建扩展统计或者用动态采样让优化器重新认识实际选择率。后面案例会演示这个动作。2.3 给父子关系列喂正确的统计信息统计信息对层次查询的影响比普通连表查询大得多。因为递归的每一层都要重新评估“要扫描多少行”一旦列分布偏移优化器会在循环的入口选择一个错误的访问路径然后把这个错误放大到每一层。实操上我一般分三步。第一步确认父键、子键的 NDV、空值比例和直方图是否准确第二步如果父子两列有关联关系用扩展统计把它们建成列组例如dbms_stats.create_extended_stats(user, BOM_RELATION, (PARENT_PART_NO, CHILD_PART_NO))然后重新收集统计信息第三步定期核对树表的行数变化尤其是大批删除后要及时刷新。这里有个坑扩展统计的列组名是一串哈希后续收集时如果直接用method_opt引用不到最好用gather_table_stats里的for all columns size skewonly一类的全列直方图方式避免列组失效。3. 五个真正能落地的优化手段从索引到改写3.1 索引方向一定要跟着展开方向走索引方向决定能不能在递归循环体内快速定位。自顶向下展开时下一层要用“当前行的子节点值”去探测“下一层行的父节点列”所以表上必须存在以父节点列开头的索引。自底向上时正好反过来用“当前行的父节点值”去探测“下一层行的子节点列”所以子节点列开头的索引才有效。很多人一上来就给两边都建索引当然也能用但至少要知道哪个索引真正被用到了。以 BOM 表为例表结构是PARENT_PART_NO、CHILD_PART_NO、QTY_PER_ASSY。如果主键是(PARENT_PART_NO, CHILD_PART_NO)自顶向下的递归已经能够通过主键前缀访问但如果 QTY 字段也被频繁带出可以考虑建一个覆盖索引(PARENT_PART_NO, CHILD_PART_NO, QTY_PER_ASSY)。如果自底向上应用多则建议(CHILD_PART_NO, PARENT_PART_NO, QTY_PER_ASSY)。不要建太多冗余索引选择业务最常用的那个方向否则更新开销会反噬。3.2 自顶向下还是自底向上先算行数再决定自顶向下和自底向上不是随便选的。判断依据只有一个哪种方向在每一层返回的行数更少。如果根节点很少、树整体是“稀疏树”自顶向下合适如果业务总是从某个零件出发找它在所有成品里的位置自底向上则更合适因为叶子数量通常远小于所有节点数量。一个容易搞反的例子当表结构是常见的父子两列NODE_ID、PARENT_ID想从某个叶子节点追溯到根路径正确写法应该是SELECT node_id, parent_id, level FROM tree START WITH node_id :leaf CONNECT BY PRIOR parent_id node_id;这里用PRIOR parent_id表示上一行的父节点等于当前行的节点号方向是向上。如果写成CONNECT BY PRIOR node_id parent_id就会变成从叶子向下展开所有子代行数完全失控。好多慢查询不是优化问题而是方向都写反了。3.3 剪枝条件一定要下推剪枝条件要下推。普通查询里习惯把过滤条件一股脑写在最外层 WHERE层次查询如果这样写Oracle 很可能先把整棵树展开再在外层过滤。正确的做法是能用START WITH限定的就在START WITH写能在CONNECT BY里作为追加条件就放在连接条件后面递归 WITH 则放在递归分支的 WHERE 中。例如只关心“外购件”子项而且外购件类型在节点表上SELECT node_id, parent_id, level FROM tree START WITH node_id :top CONNECT BY PRIOR node_id parent_id AND node_type 外包;要注意像node_type 外包这种条件加在CONNECT BY里控制的是“哪些孩子能进入下一层”。最稳妥的方式是在展开前的子查询里先把节点表过滤一次再进入递归。这听起来简单但执行计划差异很大提前过滤可以把递归成员中的每一层扫描范围缩小而不是全量展开后再截断。3.4 递归 WITH 改写不是简单替换递归 WITH 改写不是把CONNECT BY换成语法那么简单。关键点有三个。第一锚点分支要尽量窄。锚点行数决定了整个递归的起点如果锚点本身选择过多迭代每层都会被放大。第二递归分支里的连接列要有索引连接方向要和语义一致。第三最终排序不要放在递归内部。递归 WITH 支持SEARCH DEPTH FIRST BY和SEARCH BREADTH FIRST BY但这些会在迭代时排序很贵如果只是在最终结果排序放在外层查询更好。还有一个常见坑递归成员中不能直接做聚合。如果需要对同一子件在多层出现的用量做汇总应该在递归展开的结果上再GROUP BY或者提前在锚点用聚合把数据压缩。压缩不了时宁可多一个中间结果表也不要让递归成员里出现DISTINCT或GROUP BY很多版本会直接报错即便支持也容易产生巨大中间结果。3.5 用执行计划验证优化效果而不是只看耗时优化效果要以执行计划为准而不是只看最后耗时。用EXPLAIN PLAN FOR生成计划再通过DBMS_XPLAN.DISPLAY查看每个分支的访问路径核心关注点有三个递归分支对应的表访问是否走索引是否存在循环体内的全表扫描是否存在估算行数和实际行数严重偏差的节点。如果估算偏差大先补统计信息再考虑 SQL 改写不要一上来就加提示。这里要强调加优化器提示一定要基于计划证据。我看到过很多人为了加快一条层次查询随手加/* FULL */或/* PARALLEL */结果反而让循环体内反复全表扫描。提示本身没有好坏但方向错了就全是坏处。先把计划看明白再决定要不要用USE_NL、LEADING等提示。4. 一个 BOM 树优化案例从 370 秒到 1.2 秒4.1 场景描述与原始 SQL案例背景某制造企业的 BOM 关系表bom_relation有约 200 万行单棵产品树下实际需要展开 18 万行节点最大层级 22 层。原始需求很简单给定一个成品编号展开它的全部子件并计算累计用量。第一版 SQLSELECT parent_part_no, child_part_no, qty_per_assy, level FROM bom_relation START WITH parent_part_no PROD-001 CONNECT BY PRIOR child_part_no parent_part_no;跑一次要 370 秒业务完全无法接受。之所以用CONNECT BY PRIOR child_part_no parent_part_no是因为bom_relation是“边表”当前行的父件等于上一行的子件这本身没有写错。慢的原因需要在执行计划里看。4.2 执行计划诊断用EXPLAIN PLAN看计划问题非常清楚在CONNECT BY PUMP上方出现了对bom_relation的TABLE ACCESS FULL而且该全表扫描出现在循环体内部。也就是说每一层递归都把 200 万行表完整扫一遍22 层就是 22 次全表扫描耗时自然奔着几分钟去。进一步检查发现三个叠加因素根集过滤条件PROD-001被优化器识别为低选择率因为这列没有直方图连接列虽然有索引但循环体里优化器选择了一个低效驱动方向表的统计信息比实际行数低很多导致估算行数只有几百行优化器根本意识不到问题。另外原始 SQL 里没有对qty_per_assy做累计导致最后还要在外层对百万行结果做一次聚合进一步拖慢。这里的诊断步骤值得记下来先在循环体内找全表扫描再对比估算行数与实际返回行数最后检查统计信息收集时间。4.3 优化后的 SQL 与改造点优化后的 SQL 改用递归 WITH同时做三件事。第一是给bom_relation建覆盖索引方向按自顶向下走CREATE INDEX idx_bom_parent_child ON bom_relation(parent_part_no, child_part_no, qty_per_assy);第二是收集扩展统计信息让优化器重新认识父子列的选择率。第三是改写查询WITH bom_tree(parent_part_no, child_part_no, total_qty, lvl) AS ( SELECT parent_part_no, child_part_no, qty_per_assy, 1 FROM bom_relation WHERE parent_part_no PROD-001 UNION ALL SELECT b.parent_part_no, b.child_part_no, t.total_qty * b.qty_per_assy, t.lvl 1 FROM bom_relation b JOIN bom_tree t ON b.parent_part_no t.child_part_no ) SELECT parent_part_no, child_part_no, total_qty, lvl FROM bom_tree;递归分支里b.parent_part_no t.child_part_no就是典型的自顶向下上一层的子件成为下一层的父件。total_qty在递归过程中累乘避免了外层再聚合。用递归 WITH 后锚点过滤可以下推递归分支的探测稳定走idx_bom_parent_child执行计划不再是“全表扫描×22”而是“索引范围扫描×22”。4.4 实测数据对比优化前后对同一颗树测试原始 SQL 耗时 370 秒优化后耗时 1.2 秒开启并行查询情况下最快到 0.9 秒但单条查询 1.2 秒已经完全可接受。结果集行数 18 万和预期一致。相比执行计划真正的收益来自两点把循环体内的全表扫描换成索引扫描以及把累计用量提前到递归过程中少了一个结果集二次聚合。这里也要说明一个边界如果树的展开总量本身超过百万行优化手段只能缓解不能根治。最终还是要靠业务限制层数、按子件分批展开、或者把递归结果物化成扁平表。我们在这个项目里后来还把高频树的前三层物化到缓存表进一步把常规查询压到了几十毫秒。这个扩展思路大家可以按自己的业务照搬。5. 常见问题与避坑实录5.1 死循环与 NOCYCLE 的坑死循环是层次查询最高频的问题之一。CONNECT BY里一旦数据结构存在环会直接报 ORA-01436。加NOCYCLE可以绕过但要注意NOCYCLE只是跳过会导致循环的路径并不会自动把所有环上的行都正确展平结果里可能有缺失。递归 WITH 里推荐用CYCLE子句显式标记示意写法如下WITH bom_tree(...) AS (...) CYCLE child_part_no SET is_cycle TO 1 DEFAULT 0 SELECT * FROM bom_tree;这样查询可以继续执行同时能看到哪一行是环的起点。调试时比NOCYCLE直观很多也更安全。但CYCLE子句会增加一次行标识判断对超大数据量的性能有一定影响需要在业务容忍度和速度之间权衡。5.2 层级顺序与路径拼接如果需要保持树的展示顺序ORDER SIBLINGS BY是专用的它只对同一父节点下的兄弟排序不会破坏整个层级结构。普通ORDER BY会把结果拍平看起来像张表不是树。另一个容易踩坑的是SYS_CONNECT_BY_PATH拼接路径字段长度别超过 4000 字节否则触发 ORA-01489另外路径里的分隔符要注意数据本身可能包含特殊字符建议用不可见字符或转义函数处理。递归 WITH 里没有现成的SYS_CONNECT_BY_PATH需要自己用||拼接同样要考虑长度问题。5.3 分页查询里怎么保住层级结构分页查询一旦涉及层次结构不能用普通分页方式直接在原始表上ROWNUM或FETCH FIRST因为那只是在“边表”上分页展开后层级可能乱掉。正确套路是先在子查询里展开完整树并且带出CONNECT_BY_ROOT、LEVEL、SYS_CONNECT_BY_PATH然后外层按路径排序再分页。代价是必须先把完整树展开所以优化重点仍然放在“如何让展开本身更快”和“如何通过剪枝减少展开总量”。5.4 JOIN 导致重复展开层次查询关联其他表时最常见的倍增坑是 JOIN 导致一行变成多行。比如bom_relation作为驱动树再 JOIN 库存表如果一个子件有多条库存记录树上的同一个节点就会被展开多次结果集在每层成倍放大。我踩过一次树本身 2 万行JOIN 了一张存在重复记录的明细表最后中间结果跑到 20 亿行直接塞满临时表空间。解决办法是先对明细表按业务键去重或聚合再 JOIN或者用 EXISTS/标量子查询替代。总之关联表进入递归之前必须保证它在连接键上唯一。5.5 自底向上和自顶向下的选择自底向上和自顶向下的选择本质上是一个行数估算问题。自顶向下从根出发如果根很少展开合理但如果根很多、每层宽度大就会爆炸。自底向上从叶子出发适合“给定一个零件找它出现过的所有父级”如果叶子数量少于根数量性能会更好。但自底向上的结果可能包含大量重复的根节点逻辑还要通过CONNECT_BY_ROOT拿到顶值。我的建议是写 SQL 前先分别估算两个方向的首层行数哪个小就用哪个。不要凭习惯盲目选。6. 写在最后的几点实操心得在实际项目里折腾过几次层次查询后我的体会是优化层次查询八成功夫在数据模型和查询设计阶段而不是在 SQL 技巧上。表结构是否规范、环状数据是否清理、是否需要预聚合这些才是决定性能的天花板。如果能在数据源头把脏数据防住SQL 怎么写都不会太差。最后再分享一个小技巧测试层次查询时不要只看总行数和平均深度要找出树上“最宽”的那一层把这一层的数据量单独压测基本就能提前暴露基数估算问题。只要这层能扛住整个查询就不会差到哪里去。这个习惯帮我避开过很多次线上事故希望对你也有用。