层次查询及优化（递归查询）

第一章 适用范围

当SQL语句中包含层次查询时，通常意味着层次查询部分会返回相对较大的数据量。如果此时与外部条件的关联、过滤等写法不够高效，则意味着整条SQL会产生较大的性能问题。
本案例中的问题现象主要测试于当前主流的ORACLE：11G环境。不排除随着后续版本的升级可能会有一定细微的差异表现。但问题的现象及差异表现，不会有太大出入。

第二章 基本概念

Oracle层次化查询是Oracle特有的功能实现,主要用于返回一个数据集,这个数据集存在树的关系(数据集中存在一个父ID记录着当前数据集某一条记录的Id)。

举例说明：查询结构中需要包含父ID和子ID的相应关系。

如scott.emp表。MGR列为父ID列，记录着员工编号（EMPNO）的上级员工信息。

2.1 查询语法

层次化查询主要包含两部分子句：start with和 connect by。
start with:子句一般用于指定层次化查询的开始节点(也就是树的最顶级节点),找到最顶级节点,然后按照一定的规则开始查找其剩余的子节点。
connect by:这个子句就是上面所说的规则,用于查找剩余子节点的规则。
举例说明：

select *
  from scott.emp t
 start with empno = 7369
connect by prior mgr=empno;

1、start with empno = 7369 指定层次化查询的根节点。

红框内的节点为根节点,并开始遍历其余的节点。start with是可选的关键字，如果不写，代表以记录中的每一行作为根节点遍历树结构。

2、connect by prior mgr=empno以当前记录的上级信息（mgr列）连接下一层节点的员工编号(empno列) ，如果满足条件,就加入到树结果集中。
prior关键字表示前一个节点的意思。即：以prior部分为父列连接下一层（等号另一侧）的子列。按照上述规则递归查找,直到不符合这一规则,则查找停止。

3、nocycle：层次化查询会检测数据中是否存在回环(死循环)，如果存在回环，则会抛出 ORA-01436: CONNECT BY loop in user data . 的错误。如果在 connect by 后面加上nocycle则产生回环的最后一层的节点会被删除。
举例说明：构造死循环。让7839的MGR继续指向开始点的7369。

update scott.emp set mgr=7369 where empno=7839;

查询会报错，需要增加nocycle关键字以截断死循环。

截断产生死循环的部分。

4、where子句：如果层次查询包含WHERE过滤子句，则connect by的优先级要高于where。也就是sql引擎先执行connect by，Where条件是在完成connect by层次查询之后过滤。

会在循环后才做where的过滤动作。

2.2 伪列

1、伪列 LEVEL 返回这一行在树中的层次，根为第一层。CONNECT_BY_ISLEAF：当这一行是叶节点时返回 1 ，否则返回 0 。

如图所示，一共四层。叶子节点CONNECT_BY_ISLEAF返回1。

2、CONNECT_BY_ISCYCLE：当这一行有一个子节点同时也是它的跟节点时返回 1 ，否则返回 0。且需要与nocycle连用。

当发生死循环时，通过nocycle子句截断循环。即：下一层子节点同事也是根节点的情况，此行返回1。

2.3 函数

1、CONNECT_BY_ROOT：与connect by 之后的某个字段连用，表示获得这一行的根节点的该字段的值。

获取根节点的相应MGR列的值。

2、SYS_CONNECT_BY_PATH(exp,char): 函数返回从根节点到这一行计算其中每个exp 表达式的值，并把它们连接成字符串，每个节点之间用 char 字符来分割。

将层次查询的层级依次展示出来。

第三章 案例一：EXISTS+层次查询

当层次查询放在SQL的EXISTS子查询部分，通常会由于FILTER访问方式导致执行效率不高。

下面通过案例说明：

select count(*)
  from T_Main A
 where 1 = 1
   AND A.IS_SEARCH = '1'
   AND EXISTS
 (SELECT BRANCH.BRANCH_ID
          FROM T_BRANCHINFO BRANCH
         WHERE A.BRANCH_ID = BRANCH.BRANCH_ID
         START WITH BRANCH.BRANCH_ID = '1320900'
        CONNECT BY NOCYCLE PRIOR BRANCH.BRANCH_ID = BRANCH.BRANCH_ID_PARENT)
   AND A.HAPPEN_DATE >= TO_DATE('2022-08-01', 'YYYY-MM-DD')
   AND A.HAPPEN_DATE <= TO_DATE('2022-11-01', 'YYYY-MM-DD');

通过EXISTS子查询，在子查询内部完成层次查询后与外层主查询关联。

查看执行计划如下：

3.1 案例分析：

通过执行计划可知，执行效率偏低。主要问题来自于外层查询通过FILTER，多次将满足条件的值传入到exists子查询内部，并根据树形结构访问数据。
这里分析子查询中的层次查询执行效率：

SELECT BRANCH.BRANCH_ID
  FROM T_BRANCHINFO BRANCH
 START WITH BRANCH.BRANCH_ID = '1320900'
CONNECT BY NOCYCLE PRIOR BRANCH.BRANCH_ID = BRANCH.BRANCH_ID_PARENT;

查看执行计划：

层次查询仅返回10行，且执行效率非常好。可以理解为具有很好的过滤作用。适合将子查询部分作为驱动表，对子查询做展开与外部直接关联。而本例中，却没有做子查询展开。

经过测试，无论如何添加HINT：UNNEST、MERGE等，均无法改变当前执行计划。这里分析与子查询中的exists关联写法有关。

查看10053，确实找到了子查询展开被跳过的字样，原因是层次查询时不能走子查询展开。

尝试将exists子查询改写为IN子查询的方式：

select count(*)
  from T_Main A
 where 1 = 1
   AND A.IS_SEARCH = '1'
   AND a.BRANCH_ID in
       (SELECT BRANCH.BRANCH_ID
          FROM T_BRANCHINFO BRANCH
         START WITH BRANCH.BRANCH_ID = '1320900'
        CONNECT BY NOCYCLE PRIOR BRANCH.BRANCH_ID = BRANCH.BRANCH_ID_PARENT)
   AND A.HAPPEN_DATE >= TO_DATE('2022-08-01', 'YYYY-MM-DD')
   AND A.HAPPEN_DATE <= TO_DATE('2022-11-01', 'YYYY-MM-DD');

对比执行计划：

子查询作为一个单独的视图整体执行，不再与外层查询通过FILTER多次关联查询。子查询部分执行效率很好。且在外层查询存在相应关联列索引的情况下，外层查询的效率也能得到保障，改写后执行效率有了较大提升。

3.2 案例总结：

正是通过将EXISTS子查询改写为IN子查询，数据库将IN子查询当做一个独立的代码段去处理，再与外层表做关联。避免本案例中的FILTER访问方式，让本条低效SQL有了较大的性能提升。

因此实际书写时，在包含层次查询的过滤，如果子查询过滤性很好的情况，需要将子查询写成IN的形式，避免EXISTS+层次查询。

第四章 案例二：额外的关联条件

当层次查询放在SQL的子查询部分，且书写了额外的关联条件时，也可能造成执行效率问题。

下面通过案例说明：

select a.batno, a.origin_note, sum(a.fee) fee
  from T_MAIN a
 where a.batno like '162000%'
   and batno in
       (select batno
          from T_MAIN_EMP payemp
         where payemp.batno = a.batno
         start with payemp.batno = '162000020220618'
        connect by nocycle prior payemp.batno = payemp.source_batno)
 group by a.batno, a.origin_note;

通过IN子查询与层次查询做关联，但有问题的是在子查询内部还出现了相同的关联条件payemp.batno = a.batno。

查看执行计划如下：

4.1 案例分析：

仍然通过FILTER步骤，多次的执行树形查询导致异常低效。这里查询10053，分析转换后的SQL语句：

可以看到，经过优化器对SQL文本的转换，最终仍然是将SQL语句转换成类似于上一案例中的场景，exists+层次查询的写法。因此，也就印证了本例中的执行计划与上一案例基本接近，仍然通过FILTER多次访问层次查询，造成查询低效。

明确了问题原因，是由于额外的关联条件导致，那也就可以去掉不必要的关联列，调整为如下写法：

select a.batno, a.origin_note, sum(a.fee) fee
  from T_MAIN a
 where a.batno like '162000%'
   and a.batno in
       (select batno
          from T_MAIN_EMP payemp
         start with payemp.batno = '162000020220618'
        connect by nocycle prior payemp.batno = payemp.source_batno)
 group by a.batno, a.origin_note;

调整后可以顺利的对层次查询单独执行：

这里看到，去掉不必要的关联条件，采用案例一的IN子查询写法，执行效率有了很好的保障。

这里有个疑问，如果是普通的exists子查询，存在额外的关联条件时，是否也会出现上述问题呢？

elect a.batno, a.origin_note, sum(a.fee) fee
  from T_MAIN a
 where a.batno like '162000%'
   and batno in
       (select batno
          from T_MAIN_EMP payemp
         where payemp.batno = a.batno
           and payemp.batno in ('162000020220618', 'fmis162000120220822'))
 group by a.batno, a.origin_note;

去掉子查询中的层次部分，查看执行计划：

并没有出现上述低效的FILTER步骤。且通过子查询展开执行效率很好。再次分析10053事件：

可以看到，优化器是把表转换为JOIN的形式，并将关联条件转换罗列后，最后消除重复的关联条件。得到如下的转换后语句：

之后再按正常的子查询展开等手段处理即可。这里明确，传统的普通子查询，不会因为重复的关联条件导致低效的FILTER步骤。

4.2 案例总结：

通过本案例分析，在包含层次查询的子查询中，如果出现子查询与外层主查询存在多余的重复关联条件时，也可能造成无法做查询转换的问题。导致通过FILTER访问方式，产生执行效率问题。

因此实际书写时，在包含层次查询的过滤，需要检查关联条件，避免不必要的关联产生低效访问步骤。

第五章 案例三：关联层次查询根节点

当外层查询与层次查询的根节点返回列做关联时，需要考虑能否对提前传入层次查询的开始点，以便缩减层次查询过程中的数据量。否则可能导致层次查询部分耗时过大。

下面通过案例说明：

select uwp.ID, initial_cm.POLICY_CODE initial_year_policy_code
  from T_MAIN uwp,
       (select CONNECT_BY_ROOT(tporr.send_code) origin_send_code,
               tporr.POLICY_CODE
          from T_DETAIL tporr
        connect by tporr.send_code = prior tporr.POLICY_CODE) initial_cm
 where uwp.apply_code = initial_cm.origin_send_code
   and uwp.ID = 334491111;

外层查询与层次查询循环后的根节点值列做关联。
查看执行计划如下：

内层查询返回375K的数据量，消耗较大的资源开销及执行时间。并在层次循环完获得根节点列之后，在第四步与外层关联时，却发现只有一行数据满足关联条件。因此这里可以看出，内部层次查询访问了很多无用数据。

5.1 案例分析：

外层查询是与层次查询的根节点去关联，根节点生成方式是通过CONNECT_BY_ROOT函数。目的是获得这一行的根节点的tporr.send_code的值。

而此种写法是在层次查询视图的最后才进行根节点的过滤的，基于CONNECT_BY_ROOT函数的特性，完全可以在视图内部提前传入层次查询的开始点，也就是增加start with 得到uwp.apply_code

在原有层次查询部分增加开始点过滤。

select uwp.ID, initial_cm.POLICY_CODE initial_year_policy_code
  from T_MAIN uwp,
       (select CONNECT_BY_ROOT(tporr.send_code) origin_send_code,
               tporr.POLICY_CODE
          from T_DETAIL tporr
         start with tporr.send_code in
                    (select apply_code from T_MAIN where ID = 334491111)
        connect by tporr.send_code = prior tporr.POLICY_CODE) initial_cm
 where uwp.apply_code = initial_cm.origin_send_code
   and uwp.ID = 334491111;

这里增加start with开始点，入口设置为与外层关联的根节点的起始值。调整后的执行效率测试如下：

从开始点就只会返回一行数据，层次查询也只会基于这一行数据去循环。而不会出现本案例中的，针对所有值做层次查询，并在最外层关联时抛弃不满足的数据的场景。

5.2 案例总结：

通过本案例分析，在包含层次查询的子查询中，如果层次查询与外层的关联条件是通过CONNECT_BY_ROOT函数获取的根节点值去关联的，需要考虑给层次查询增加满足关联条件的开始点，让层次查询只访问必要的数据量。

因此实际书写时，在包含层次查询根节点的关联过滤时，需要检查开始点，避免不必要额外循环造成查询低效。

第六章 问题总结

本篇仅是总结了工作中遇到的层次查询低效场景，均是由于SQL写法或没有应用有效的过滤条件。因此在遇到层次查询时，需要对层次查询部分给与额外关注，以确定是否造成性能问题并做相应改进。