쿼리속도문제 0 3 201

by 일회용 [Oracle Tuning] oracle [2018.09.13 14:13:23]


--그룹별로 작업 상태에 따라 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

 

by 우리집아찌 [2018.09.13 14:50:30]

서브 쿼리를 하나의 쿼리로 만들어서 outer join 하시는게 좋아보입니다 .

where 조건들을 case 문으로 분기해서 처리하세요.


by 일회용 [2018.09.17 09:27:39]

조언해주신 방법으로 하니 간결하고 빨라졌네요. 감사합니다.

 


by 마농 [2018.09.18 10:58:26]
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
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입