SQL优化经典案例----树形查询优化

SQL优化经典案例----树形查询优化

  

---转载http://blog.sina.com.cn/s/blog_61cd89f60102efcb.html

    对于树形查询的案例在我接触的系统中并不多见，只是在学习理论知识的时候知道有这么个东东查询，就好比一个公司的职员表，总经理下面有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在优化前，执行30分钟才能出结果，就算是OLAP系统估计也很难忍受如此低效的SQL，我们来看优化后的SQL语句：

with a as
 (select 
   tdl_dn
    from CABLE_1 a
   where a.tdl_operation <> 2),
b as
 (select 
   tdl_dn
    from RESOURCE_FACING_SERVICE1_1 b
   where b.tdl_operation <> 2),
t as
 (select 
   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)

执行计划信息如下：