oracle 递归分组统计

累加，比如id=1下面包含所有的子节点和自身统计，也就是统计每个子树和，根不要就用WHERE CONNECT_BY_ISLEAF=1过滤

SQL> SELECT id,p_id,value,
  2  ( SELECT SUM(value) FROM
  3   test_tree a
  4   START WITH a.id=b.id
  5   CONNECT BY PRIOR a.id=a.p_id
  6  ) sum_sal
  7  FROM
  8  test_tree b;

        ID       P_ID      VALUE    SUM_SAL
---------- ---------- ---------- ----------
         1          0          3         36
         2          1          6         23
         3          1          5         10
         4          2          2          2
         5          2          7          7
         6          2          8          8
         7          3          5          5

7 rows selected

SQL>
SQL> SELECT root_id,SUM(value)
  2    FROM (select CONNECT_BY_ROOT a.id root_id,p_id,value
  3            from test_tree a
  4          CONNECT BY PRIOR id = a.p_id
  5          )
  6  GROUP BY root_id;

   ROOT_ID SUM(VALUE)
---------- ----------
         1         36
         2         23
         3         10
         4          2
         5          7
         6          8
         7          5

7 rows selected

 

现在遇到了效率问题。如果统计的列比较多，会有很多个 递归的子查询，查询速度非常慢。

 

如何优化处理呢？