对于树形查询的案例在我接触的系统中并不多见,只是在学习理论知识的时候知道有这么个东东查询,就好比一个公司的职员表,总经理下面有5位副总,副总下面有各有20个小弟,现在让查询总经理下面有多少小弟,这个时候就用到了树形查询,意思就是这么个意思,来看一下具体的优化案例。
select rownum, adn, zdn, 'cable'
from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
from AGGR_1 t
where t.tdl_operation <> 2
and exists (select 1
from CABLE_1 a
where a.tdl_operation <> 2
and a.tdl_dn = t.tdl_z_dn)
start with exists (select 1
from RESOURCE_FACING_SERVICE1_1 b
where b.tdl_operation <> 2
and t.tdl_a_dn = b.tdl_dn)
connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)
执行计划信息
![SQL优化经典案例----树形查询优化 SQL优化经典案例----树形查询优化](https://i-blog.csdnimg.cn/blog_migrate/79daee1cc3dff6e8e29907d8aad924e1.jpeg)
该SQL在优化前,执行30分钟才能出结果,就算是OLAP系统估计也很难忍受如此低效的SQL,我们来看优化后的SQL语句:
with a as
(select ![SQL优化经典案例----树形查询优化 SQL优化经典案例----树形查询优化](https://i-blog.csdnimg.cn/blog_migrate/d6141005ff7e31df5838a409524c633f.jpeg)
tdl_dn
from CABLE_1 a
where a.tdl_operation <> 2),
b as
(select ![SQL优化经典案例----树形查询优化 SQL优化经典案例----树形查询优化](https://i-blog.csdnimg.cn/blog_migrate/ba88c6d65d7a1fcb7c9068c7041b3834.jpeg)
tdl_dn
from RESOURCE_FACING_SERVICE1_1 b
where b.tdl_operation <> 2),
t as
(select ![SQL优化经典案例----树形查询优化 SQL优化经典案例----树形查询优化](https://i-blog.csdnimg.cn/blog_migrate/4dfd6678df639c49f0debd8168ad8ea9.jpeg)
tdl_a_dn, tdl_z_dn, tdl_operation
from AGGR_1 t)
select rownum, adn, zdn, 'cable'
from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
from t
where t.tdl_operation <> 2
and exists (select 1 from a where a.tdl_dn = t.tdl_z_dn)
start with exists (select 1 from b where t.tdl_a_dn = b.tdl_dn)
connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)
执行计划信息如下: