层次查询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. 

（编辑：湖南网）

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