层级查询高级用法, 执行计划hash group by--工作备忘2016/02/02

1、

情况说明： 将层级关联查询出的结果，合并成一列。

方法说名： 在普通层级查询（start with connect by) 之上使用 sys_connect_by_path(para1,para2),参数1是要显示的字段，参数2是连接字段的分个符，

使用该函数可以将层级结果合并。

高级使用（工作中）只想显示特定的字段或者将多层树分成不同的列。

--可以配合case when语句只显示所需节点，然后加入多个sys_connect_by_path 是一整串节点分列显示

WITH employee_sales_area AS
 (SELECT /*+ materialize */
   aeh.id_head,
   MAX(CASE
           WHEN ae.type = '1' THEN
            1
           ELSE
            0
       END) AS flag_city_head,
   MAX(CASE
           WHEN ae.type = '2' THEN
            1
           ELSE
            0
       END) AS flag_province_head,
   MAX(CASE
           WHEN ae.type = '3' THEN
            1
           ELSE
            0
       END) AS flag_region_head
  FROM   owner_int.vd_hom_sales_area2head aeh ----fx, not in internal db
  LEFT   JOIN owner_int.vd_hom_sales_area ae ON ae.id = aeh.id_sales_area
                                         AND    ae.status = 'a'
  WHERE  trunc(SYSDATE) + 1 - (1 / 86400) BETWEEN aeh.valid_from AND nvl(aeh.valid_to, to_date('30000101', 'yyyymmdd'))
  GROUP  BY aeh.id_head)
SELECT id_emp,(CASE
                                    WHEN code_bank_role = 'sle' THEN
                                     id_emp
                                    ELSE
                                     NULL
                                END) AS id_emp_sales_leader ,
                                (CASE
                                    WHEN code_bank_role = 'sma' THEN
                                     id_emp
                                    ELSE
                                     NULL
                                END) AS id_emp_sales_manager,
                                (CASE
                                    WHEN code_bank_role = 'che' THEN
                                     id_emp
                                    WHEN code_bank_role = 'man'
                                         AND flag_city_head = 1 THEN
                                     id_emp
                                    ELSE
                                     NULL
                                END) AS id_emp_city_head,
       TRIM(sys_connect_by_path(/*CASE
                                    WHEN code_bank_role = 'sle' THEN*/
                                     id_emp
                                  /*  ELSE
                                     NULL
                                END,
                                ' '*/,' ')) AS id_emp_sales_leader1,
       TRIM(sys_connect_by_path(CASE
                                    WHEN code_bank_role = 'sma' THEN
                                     id_emp
                                    ELSE
                                     NULL
                                END,
                                ' ')) AS id_emp_sales_manager1,
       TRIM(sys_connect_by_path(CASE
                                    WHEN code_bank_role = 'che' THEN
                                     id_emp
                                    WHEN code_bank_role = 'man'
                                         AND flag_city_head = 1 THEN
                                     id_emp
                                    ELSE
                                     NULL
                                END,
                                ' ')) AS id_emp_city_head1

FROM   (SELECT emp.id_emp,
               emp.id_superior,
               emp.emp_name2          AS name_last,
               emp.bankrole_code      AS code_bank_role,
               sar.flag_city_head,
               sar.flag_province_head,
               sar.flag_region_head
        FROM   owner_int.vh_hom_employee emp
        LEFT   JOIN employee_sales_area sar ON sar.id_head = emp.id_emp
        WHERE  EMP.EMP_STATUS = 'a') e
WHERE  e.id_emp IN (142364, 182431)
START  WITH code_bank_role IN ('che')
CONNECT BY PRIOR id_emp = id_superior
               AND LEVEL <= 8;

参考资料：

sys_connect_by_path简单应用： http://blog.youkuaiyun.com/inthirties/article/details/4331685， http://www.cnblogs.com/huanghai223/archive/2010/12/10/1902696.html

其他相关函数用法 http://www.xuebuyuan.com/1580678.html，

http://blog.youkuaiyun.com/huangyunzeng2008/article/details/4799973，

http://my.oschina.net/kkrgwbj/blog/308311

CONNECT_BY_ROOT--查询每个分支的根节点

2、hash group by

如果执行计划最后group by是hash聚合且速度非常慢，可能是临时表空间不足导致，

该问题为oracle算法bug，偶然性发生，如何出现可使用

1）/*+no_use_hash_aggregation */

2）或者加上order by语句强行执行 sort group by