SQL优化案例4-树形查询优化

对于树形查询的案例在我接触的系统中并不多见，只是在学习理论知识的时候知道有这么个东东查询，就好比一个公司的职员表，总经理下面有5位副总，副总下面有各有20个小弟，现在让查询总经理下面有多少小弟，这个时候就用到了树形查询，意思就是这么个意思，来看一下具体的优化案例。

select rownum, adn, zdn, 'cable'
  from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dnzdn
          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  /*+ materialize */

   tdl_dn
    from CABLE_1 a
   where a.tdl_operation <> 2),
b as
 (select  /*+ materialize */

   tdl_dn
    from RESOURCE_FACING_SERVICE1_1 b
   where b.tdl_operation <> 2),
t as
 (select  /*+ materialize */

   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_dnzdn
          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)

执行计划信息如下：

优化后，该SQL查询由原来的半小时优化到现在的2分钟出结果，当然该SQL还有优化余地,下一步就是常用的消除全表扫描，太常规的思路，不值得去研究了。

优化思路总结：该案例中我们用到的SQL优化思路有两点，第一是用with as对子查询进行优化，第二通过添加hint/*+ materialize */缓存临时结果集，此hint在此处相当于临时表，注意，该hint在官网上并没有对其进行说明。可以说在SQL语句优化中，是一个很经典的例子，在此记录一下！