层次查询SQL性能故障不断？给你份可靠的避坑指南！

近期频频遇到层次查询SQL的性能问题，结合历史故障案例，汇总了一些场景connect by常见的性能故障类型，在本文中做个分享。

一、结果中过滤or生成树中过滤

过滤条件放置于where后，为在结果树生成完成后裁剪叶子节点;放置于connect by后，为在生成树的过程中裁剪子树。

频繁发生的现象是业务逻辑上其实并不需要先生成结果树再去过滤，由于开发人员对过滤条件放置于不同的位置(where 后，connect by后)产生的过滤效果混淆，导致了低效的性能。

下面这个SQL就是典型案例。用户反馈，zzzz.SYS_RC_ROUTE_DETAIL表上生产环境就3000+条数据，但SQL语句运行时却跑不出来结果：

select  xxxxx 
  from zzzz.SYS_RC_ROUTE_DETAIL t 
 where t.route_id = (select a.route_id 
                       from xxx.sys_rc_route a, xxx.g_wo_base b 
                      where a.route_id = b.route_id 
                        and b.work_order = 'yyyyyyyyy') 
 start with t.node_type = '0' 
connect by nocycle prior next_node_id = node_id 

让客户运行了SQL一分钟后cancel掉，抓取了监视报告如下：

问题点很明显，表中nextnodeid = node_id的重复值很多，导致了海量的结果集。SQL运行的一分钟内，connect by尚未把完整的树生产完成，就已经有了3000W+数据，于是我们开始思考，在逻辑上是否有必要在构建完整的树后再过滤。

与业务部门沟通后，发现果然不需要。

以下数据可以测试下，3000行数据量，但是count(*) 会非常慢。

SQL> create table test1 as 
select 
    mod(rownum,2)                     id, 
    mod(rownum +1 ,2)                  id2 
from 
    dual 
connect by level <= 3000 
;  2    3    4    5    6    7    8 
 
Table created. 
 
SQL> set timing on 
SQL> select count(*) from test1  where id =0  start with id =0 connect by nocycle prior id = id2 ; 
 
  COUNT(*) 
---------- 
      1500 
 
Elapsed: 00:09:26.88 
SQL> 

结果中过滤如上所示，用了9分钟;而生成树中过滤则只用0.3s：

SQL> select count(*) from test1  start with id =0 connect by nocycle prior id = id2 and id = 0 ; 
 
  COUNT(*) 
---------- 
      1500 
 
Elapsed: 00:00:00.31 

很多情况下，两种写法的结果集可能是相同的，如下：

create table test2 as 
 select 
      rownum                     id, 
      rownum +1                 id2, 
      rownum + 2               id3 
 from 
     dual 
 connect by level <= 3000; 
 
 SQL> select id from test2 where id3 < 10 start with id = 3 connect by nocycle prior id2 = id; 
 
     ID 
 ---------- 
      1 
      2 
      3 
      4 
      5 
      6 
      7 
 
 7 rows selected. 
 
 SQL> select id from test2  start with id = 1 connect by nocycle prior id2 = id and id3 <10; 
 
     ID 
 ---------- 
      1 
      2 
      3 
      4 
      5 
      6 
      7 
 
 7 rows selected. 

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!