树形结构优化查询的相关问题

昨天参加一场面试，面试题是关于树形结构查询的相关内容，之前在实际工作中没有遇到此类问题，就从网上看了一下相关的内容，总结一下：

一般情况下我们对递归查询是这样的：

select last_name,employee_id,manager_id,level from employees start with employee_id=100 connect by prior employee_id=manager_id order siblings by last_name 

这条语句的含义是求出雇员id为100以下的所有人员信息，包含会员的姓名，会员id,上级id,并按照兄弟节点进行排序；

例如进行查询某个员工下的所有下属员工：

select employee_id,last_name,manager_id from employees start with last_name='KEVIN' connect prior employee_id=manager_id

查询某个员工上的所有上级领导：

select employee_id,last_name,manager_id from employees start with last_name='KEVIN' connect by prior manager_id=employee_id

如果查找到姓名是KEVIN结构树形的路径：

select sys_connect_by_path(ename,'>') tree from employees start with last_name='KEVIN' connect by prior employee_id=manager_id

展示的结果就是：

tree

>KEVIN

>KEVIN>JSON

>KEVIN>TONNY

那面对节点数上千万个，查询速度就会很慢，那如何解决这个问题？

首先是遇到一种场景：一个部门有多个领导，领导下面带有多个队员，假定这个场景中出现很多节点，那查询出这个节点可能就会非常慢，一开始我并没有见过这种业务，当时也一时语塞，于是面试回来我就从网上查了相关的资料。

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为：

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

这个含义就是建立关联表以及上级表，使用递归查询就可以查询到对应父节点下子节点的相关信息。