스칼라 서브쿼리에서 건바이건으로 하위 데이터를 집계

ex)

with recur as

(

select 1 id , null amt , null p_id from dual union all

select 2 id , 303 amt , 1 p_id from dual union all

select 3 id , 188 amt , 1 p_id from dual union all

select 4 id , null amt , null p_id from dual union all

select 5 id , null amt , 4 p_id from dual union all

select 6 id , 412 amt , 5 p_id from dual union all

select 7 id , 678 amt , 5 p_id from dual union all

select 8 id , 583 amt , null p_id from dual

)

select

a.id

, ( SELECT SUM (AMT) FROM RECUR START WITH ...... )

, a.amt

, a.p_id

from recur a

connect by

prior a.id = a.p_id

start with a.p_id is null

order siblings by

하지만 건수가 많아 느린건 문제가 될거같네요.

채용근님 관심 감사합니다.

말씀하신대로 쿼리를 구성해서 제 로컬 pc에서 수행해 보았습니다.(서버에서는 권한이 없어서리..)

테스트해보니

32087 건 / 11908941 건 인 자료에서

그냥 계층 쿼리만 수행하니 3초 정도 걸렸구요

집계 컬럼이 1개 일 때 3.6초

2개 일때 4.2~3초

...

5개일때 6초 전후한 시간이 걸렸습니다.

더 좋은 방법은 없을까요?

Start With 절을 지정하지 않고 계층쿼리를 전개합니다.
그러면 모든 행이 시작점이 되는 거죠.
그후 connect_by_root 를 이용해 group by sum 하시면 됩니다.

SELECT empno
     , SUM(sal)
  FROM (SELECT CONNECT_BY_ROOT(empno) empno
             , sal
          FROM emp
         CONNECT BY PRIOR empno = mgr
        )
 GROUP BY empno
;

마농님 감사합니다.

그런데 마농님 말씀대로 하면 출력 순서는 어떻게 하지요?

-- 집계용 쿼리와 본래의 계층 쿼리는 별도로 가야겠지요. --
SELECT a.empno
     , a.mgr
     , a.sal
     , b.sal_1
     , b.sal_2
     , b.sal_3
  FROM emp a
     , (SELECT empno
             , SUM(sal) AS sal_1
             , SUM(sal*0.1) AS sal_2
             , SUM(sal+NVL(comm,0)) AS sal_3
          FROM (SELECT CONNECT_BY_ROOT(empno) empno
                     , sal
                     , comm
                  FROM emp
                 CONNECT BY PRIOR empno = mgr
                )
         GROUP BY empno
        ) b
 WHERE a.empno = b.empno
 START WITH a.mgr IS NULL
 CONNECT BY PRIOR a.empno = a.mgr
 ORDER SIBLINGS BY a.empno
;

마농님이 제시하신 방법을 사용하여 테스트한 결과

컬럼의 갯수가 늘어도 3.6초 안쪽으로 떨어집니다.

스칼라 서브쿼리는 컬럼 갯수에 영향을 받는데 마농님 방법은 영향을 받지 않네요

또 다른 방법은 없을까요?