--그룹별로 작업 상태에 따라 count 를 구하는 쿼리입니다. --서브쿼리로 그룹별 카운트를 구하다보니 속도가 느리다고 느껴지는 상황입니다. --인덱스를 잡고 해서 조금은 나아졌지만 그래도 좀 느리네요. --다른 방법으로 할 수 있는 방법이 있을까요.? --쿼리 SELECT a.* FROM (SELECT LEVEL - 1 lvl, gtb.group_id groupid, gtb.group_name groupname, gtb.parent_group_id parentgroupid, (SELECT Count(DISTINCT ta.key_id) AS bcnt FROM t_ta ta inner join t_td td ON ta.key_id = td.key_id inner join t_userGroup ug ON td.wk_id = ug.user_id inner join t_group eg ON ug.group_id = eg.group_id WHERE ug.rep_group = 1 AND eg.group_id IN (SELECT group_id FROM t_group eg1 START WITH eg1.group_id = gtb.group_id CONNECT BY PRIOR eg1.group_id = eg1.parent_group_id) AND ta.user_id = 'userName' AND ta.status = 'B' AND ta.start_date >= To_date('20180311', 'yyyyMMdd') AND ta.start_date < To_date('20180911', 'yyyyMMdd') + 1) bCnt, (SELECT Count(DISTINCT ta.key_id) AS bcnt FROM t_ta ta inner join t_td td ON ta.key_id = td.key_id inner join t_userGroup ug ON td.wk_id = ug.user_id inner join t_group eg ON ug.group_id = eg.group_id WHERE ug.rep_group = 1 AND eg.group_id IN (SELECT group_id FROM t_group eg1 START WITH eg1.group_id = gtb.group_id CONNECT BY PRIOR eg1.group_id = eg1.parent_group_id) AND ta.user_id = 'userName' AND (ta.status = 'A' OR ta.status = 'C' OR ta.status = 'R') AND ta.start_date >= To_date('20180311', 'yyyyMMdd') AND ta.start_date < To_date('20180911', 'yyyyMMdd') + 1) acrCnt, (SELECT Count(DISTINCT ta.key_id) AS bcnt FROM t_ta ta inner join t_td td ON ta.key_id = td.key_id inner join t_userGroup ug ON td.wk_id = ug.user_id inner join t_group eg ON ug.group_id = eg.group_id WHERE ug.rep_group = 1 AND eg.group_id IN (SELECT group_id FROM t_group eg1 START WITH eg1.group_id = gtb.group_id CONNECT BY PRIOR eg1.group_id = eg1.parent_group_id) AND ta.user_id = 'userName' AND ta.status != 'B' AND td.is_complete = 1 AND ta.start_date >= To_date('20180311', 'yyyyMMdd') AND ta.start_date < To_date('20180911', 'yyyyMMdd') + 1) cCnt, (SELECT Count(DISTINCT ta.key_id) AS bcnt FROM t_ta ta inner join t_td td ON ta.key_id = td.key_id inner join t_userGroup ug ON td.wk_id = ug.user_id inner join t_group eg ON ug.group_id = eg.group_id WHERE ug.rep_group = 1 AND eg.group_id IN (SELECT group_id FROM t_group eg1 START WITH eg1.group_id = gtb.group_id CONNECT BY PRIOR eg1.group_id = eg1.parent_group_id) AND ta.user_id = 'userName' AND (SYSDATE - ta.end_date) >= 0 AND (ta.status = 'A' OR ta.status = 'C' OR ta.status = 'R') AND ta.start_date >= To_date('20180311', 'yyyyMMdd') AND ta.start_date < To_date('20180911', 'yyyyMMdd') + 1) dCnt, (SELECT Count(1) FROM t_group eg WHERE eg.parent_group_id = gtb.group_id ) childcount FROM t_group gtb WHERE 1=1 START WITH gtb.group_id = '1' CONNECT BY PRIOR gtb.group_id = gtb.parent_group_id ORDER SIBLINGS BY gtb.parent_group_id, gtb.lev_order, gtb.group_id) a WHERE 0 < bCnt+acrCnt; --결과 lv groupId groupName parentGroupId bcnt ccnt acrcnt dcnt chldcount 0 1 ALL 2 7 2 5 2 1 221 팀A 1 2 7 2 5 11 2 222 팀1 221 0 1 0 1 7 3 367 팀2 222 0 1 0 1 4 4 235 팀3 367 0 1 0 1 0 2 322 팀B 221 2 6 2 4 3 3 323 팀1 322 1 4 0 3 0 3 324 팀2 322 2 6 2 4 0 3 325 팀3 322 0 1 1 1 0
SELECT a.lvl , a.groupid , a.groupname , a.parentgroupid , b.bcnt , b.acrCnt , b.ccnt , b.childcount FROM (SELECT LEVEL - 1 lvl , group_id groupid , group_name groupname , parent_group_id parentgroupid , ROWNUM rn FROM t_group START WITH group_id = '1' CONNECT BY PRIOR group_id = parent_group_id ORDER SIBLINGS BY lev_order, group_id ) a INNER JOIN (SELECT eg.groupid , COUNT(DISTINCT CASE WHEN ta.status = 'B' THEN ta.key_id END) bcnt , COUNT(DISTINCT CASE WHEN ta.status IN ('A', 'C', 'R') THEN ta.key_id END) acrCnt , COUNT(DISTINCT CASE WHEN ta.status != 'B' AND td.is_complete = 1 THEN ta.key_id END) ccnt , COUNT(DISTINCT CASE WHEN eg.lv = 2 THEN eg.group_id END) childcount FROM t_ta ta INNER JOIN t_td td ON ta.key_id = td.key_id INNER JOIN t_userGroup ug ON td.wk_id = ug.user_id INNER JOIN (SELECT CONNECT_BY_ROOT group_id AS groupid , group_id , LEVEL lv FROM t_group CONNECT BY PRIOR group_id = parent_group_id ) eg ON ug.group_id = eg.group_id WHERE ug.rep_group = 1 AND ta.user_id = 'userName' AND ta.start_date >= TO_DATE('20180311', 'yyyyMMdd') AND ta.start_date < TO_DATE('20180911', 'yyyyMMdd') + 1 GROUP BY eg.groupid ) b ON a.groupid = b.groupid WHERE b.bCnt + b.acrCnt > 0 ORDER BY a.rn ;