100만건 이상 조회를 해야해서 인덱스를 사용하였는데 속도가 잘안나옵니다 0 9 374

by 겸둥이k [SQL Query] 인덱스 쿼리 속도 [2019.04.01 14:32:00]


스크린샷.PNG (29,959Bytes)

쿼리~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT F_DE(B.KDE1,'1','K') KDe1Nm,
                            B.KDe1,
                            F_CODE(P_CODE) pCodeNm,
                            F_CODE(C_CODE) cCodeNm,
                            NVL ( SUM(cnt1),0) cnt1,
                            NVL (SUM(cnt2),0) cnt2,
                            NVL (SUM(cnt3),0) cnt3,
                            TRUNC(ROUND(NVL(SUM(cnt2)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum1,
                            TRUNC(ROUND(NVL(SUM(cnt3)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum2,
                            TRUNC(ROUND(NVL((SUM(cnt2)+SUM(cnt3))/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum3,
                            NVL (SUM(cnt5),0) cnt5,
                            NVL (SUM(cnt6),0) cnt6,
                            TRUNC(ROUND(NVL(SUM(cnt5)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum4,
                            TRUNC(ROUND(NVL(SUM(cnt6)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum5,
                            TRUNC(ROUND(NVL((SUM(cnt5)+SUM(cnt6))/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum6,
                        DECODE(Grouping_ID(KDe1,P_CODE,C_CODE),0,1,1,2,3,3,4) groupId
                    FROM(
                    SELECT A.* ,
                        (SELECT NVL(SUM(CNT),0) FROM V_CONTRACT VC WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND A.KDe1= VC.K_De1  AND A.KDe2= VC.K_De2) CNT1
                    , (SELECT COUNT(K_De1) FROM V_SCHEDULE_WORK VSC WHERE A.KDe1= VSC.K_De1 AND A.KDe2= VSC.K_De2 AND INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ2 >0 AND TO_CHAR(WORK_DATE,'WW')+0 <= ( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL) ) CNT2 
                    , (SELECT COUNT(K_De1) FROM V_SCHEDULE_WORK VSC WHERE A.KDe1= VSC.K_De1 AND A.KDe2= VSC.K_De2 AND INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ2 >0 AND TO_CHAR(WORK_DATE,'WW')+0 = ( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL )  ) CNT3
                    , (SELECT COUNT(K_De1) FROM V_SCHEDULE_WORK VSC WHERE A.KDe1= VSC.K_De1 AND A.KDe2= VSC.K_De2 AND INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ3 >'0' AND TO_CHAR(GAETONG_DATE,'WW')+0 <= ( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL )) CNT5 
                    , (SELECT COUNT(K_De1) FROM V_SCHEDULE_WORK VSC WHERE A.KDe1= VSC.K_De1 AND A.KDe2= VSC.K_De2 AND INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ3 >'0' AND TO_CHAR(GAETONG_DATE,'WW')+0 = ( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL )) CNT6
                    FROM ( 
                        SELECT 
                                    TKD.K_De1 KDe1,
                                    TKD.K_De2 KDe2,
                                    TKD.NO1 no1,
                                    SUBSTR( P_CODE ,0,4) P_CODE,
                                    F_CODE(SUBSTR( P_CODE ,0,4)) pCodeNm ,
                                    F_CODE( C_CODE ) cCodeNm ,
                                    C_CODE 
                        FROM T_K_De TKD,T_CODE TC 
                            WHERE 
                            P_CODE IN ('HW1010','HW2010','HW3010','HW4010')
                            ORDER BY KDe1,C_CODE,P_CODE 
                            )A
                        )B
                        WHERE 1=1
                        GROUP BY ROLLUP(B.KDe1,P_CODE,C_CODE)
                        ORDER BY MAX(B.NO1), NVL(P_CODE ,'ㅎ'),NVL(C_CODE,'ㅎ')

아래가 뷰~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SELECT 'D' DIV, K_De1,K_De2, De1, De2, BUSI_DIV, INST_L, INST_M,  INST_S ,WORK_DATE, SCH_SEQ, SCH_SEQ2, SCH_SEQ3 , DCU_ID INST_ID,GAETONG_DATE FROM T_SCHEDULE_DCU WHERE USE_YN ='Y' 
UNION ALL 
SELECT 'M' DIV, K_De1,K_De2, De1, De2, MIN(BUSI_DIV), REPLACE(INST_L ,'HW40','HW30') INST_L, MIN(REPLACE(INST_M,'HW40','HW30')) INST_M, MIN(REPLACE( INST_S,'HW40','HW30')) INST_S
,MIN(WORK_DATE),MIN(SCH_SEQ),MIN(SCH_SEQ2),MIN(SCH_SEQ3) ,MAC_MODEM INST_ID,MIN(GAETONG_DATE)   FROM T_SCHEDULE_MODEM  WHERE USE_YN ='Y'  AND MODEM_DIV='마스터' AND MAC_MODEM >'0' GROUP BY K_De1,K_De2, De1, De2,INST_L ,MAC_MODEM
UNION ALL
SELECT 'E' DIV, K_De1,K_De2, De1, De2, BUSI_DIV, INST_L, INST_M,  INST_S ,WORK_DATE, SCH_SEQ, SCH_SEQ2, SCH_SEQ3 ,INSTR_NUM  INST_ID,GAETONG_DATE FROM T_SCHEDULE_MODEM  WHERE USE_YN ='Y'
 

아래 뷰를 사용하여 V_SCHEDULE_WORK로 돌렸는데 너무 속도가 안나와서 인덱스를 사용했는데요 인덱스를 사용기준으로 10만건에 4초정도 나오는데 이정도면 문제가 큰데 뷰에인덱스? 를 건게아니라 해당 테이블에 인덱스를 걸어서 속도가 늦는건지  방법이있을까요? 

아래는 T_SCHEDULE_MODEM 이 풀스캔을 때려버리네요...ㅠ  이것때문에 그런건지 인덱스 똑같이 줬는데말이죠 

 

 

 

 

by 마농 [2019.04.01 15:42:58]

뷰 안의 3개 쿼리 중 2,3 번 쿼리가 같은 테이블이네요?
또한 조건을 보면 3번 결과가 2번 결과를 포함하네요? 왜 이런건지? 이러면 중복 카운트가 될 것 같은데요?
2번 쿼리는 Group By 를 하는데 왜 2번만 그룹바이 하나요? 일관성이 없어 보이네요.
2번 쿼리는 Group By 때문에 메인쿼리 조건이 서브쿼리 안으로 침투하지 못하는 것 같습니다.
스칼라 서브쿼리를 아우터조인 형태로 변경할 수 있습니다.
기타 문제점
- 쓸데 없는 함수 사용 : f_code 함수가 안에서고 실행되고 밖에서 또 다시 실행됨. 안쪽 함수 제거 필요
- 쓸데 없는 정렬 사용 : 안쪽 정렬은 불필요합니다.
- tkd 와 tc 간 조인 조건이 없는데? 이거 맞나요?


by 겸둥이k [2019.04.01 16:27:19]

그렇군요 근데 뷰에서 저렇게써야 해당 3번째쿼리기준에서  2번째 쿼리를 값을 뽑아올수있거든요 ..... 침투가안된다면 서브쿼리를 쓰지말고 조인으로 쓰라는말씀이신가요? 

그리고 tkd 기준으로  tc 를 그대로 뽑아줄려고 조건이없습니다. 

도대체 속도가 왜 늦을까요 ㅠ 

 

 


by 마농 [2019.04.01 16:39:32]

속도가 늦은 이유는 메인쿼리 조건이 스칼라서브쿼리로 침투하지 못해서 입니다.
침투하지 못하는 이유는 GROUP BY 사용 때문입니다.
그리고 GROUP BY 를 왜 사용해야 하는지 이유를 모르겠네요.
테이블의 특성을 알아야 이해가 갈것 같네요.


by 겸둥이k [2019.04.01 17:03:24]

답변감사합니다

그룹바이를 쓰는이유는 분류별로 뽑을려고 사용했습니다

뷰쪽보시면  DIV 컬럼 에서 3가지로 나눠져있습니다

D   ,M ,E           M은 E의 상위 호환 그러니까 만약 E가 5개가있는데 겹치는게 있다 그럼 M은 하나로 보는거죠 그값을 뽑아낼려고 저렇게했습니다 

그러면 각각 제가 원하는 D , M, E 값을 뽑아낼수있으니깐요 그래서 그룹바이를 썻는데 여기서 문제인가보군요   


by 마농 [2019.04.01 17:06:56]

뷰 안에서 Group By 하지 말고
뷰 밖에서 COUNT(*) 대신 COUNT(DISTINCT inst_id) 해보세요.


by 겸둥이k [2019.04.01 17:17:17]

똑같습니다 ㅠ 

그리고 그룹바이 쿼리부분을 아예 없애고 돌려도 속도는 같네요 ㅠ


by 마농 [2019.04.01 17:23:18]

그러면 스칼라서브쿼리로는 한계가 있는거네요. 아우터 조인 형태로 바꿔야 합니다.


by 겸둥이k [2019.04.01 17:28:11]

답변감사합니다 

인덱스로 해보고 정안돼면 쿼리수정하겠습니다 ㅠㅠ


by 마농 [2019.04.02 09:22:01]
SELECT kde1
     , f_de(b.kde1, '1', 'K') kde1nm
     , p_code
     , f_code(p_code) pcodenm
     , c_code
     , f_code(c_code) ccodenm
     , NVL(SUM(cnt1), 0) cnt1
     , SUM(cnt2) cnt2
     , SUM(cnt3) cnt3
     , ROUND(SUM(cnt2)        / SUM(cnt1), 2) cntsum1
     , ROUND(SUM(cnt3)        / SUM(cnt1), 2) cntsum2
     , ROUND(SUM(cnt2 + cnt3) / SUM(cnt1), 2) cntsum3
     , SUM(cnt5) cnt5
     , SUM(cnt6) cnt6
     , ROUND(SUM(cnt5)        / SUM(cnt1), 2) cntsum4
     , ROUND(SUM(cnt6)        / SUM(cnt1), 2) cntsum5
     , ROUND(SUM(cnt5 + cnt6) / SUM(cnt1), 2) cntsum6
     , DECODE(GROUPING_ID(kde1, p_code, c_code), 0, 1, 1, 2, 3, 3, 4) groupId
  FROM (SELECT a.kde1
             , a.kde2
             , a.no1
             , a.p_code
             , a.c_code
             , NULLIF(a.cnt1, 0) cnt1
             , COUNT(CASE WHEN vsc.sch_seq2 > 0 AND TO_CHAR(vsc.work_date   , 'ww')+0 <= TO_CHAR(sysdate, 'ww')-1 THEN 1 END) cnt2
             , COUNT(CASE WHEN vsc.sch_seq2 > 0 AND TO_CHAR(vsc.work_date   , 'ww')+0 <= TO_CHAR(sysdate, 'ww')-0 THEN 1 END) cnt3
             , COUNT(CASE WHEN vsc.sch_seq3 > 0 AND TO_CHAR(vsc.gaetong_date, 'ww')+0 <= TO_CHAR(sysdate, 'ww')-1 THEN 1 END) cnt5
             , COUNT(CASE WHEN vsc.sch_seq3 > 0 AND TO_CHAR(vsc.gaetong_date, 'ww')+0 <= TO_CHAR(sysdate, 'ww')-0 THEN 1 END) cnt6
          FROM (SELECT tkd.k_de1 kde1
                     , tkd.k_de2 kde2
                     , tkd.no1
                     , SUBSTR(p_code, 1, 4) p_code
                     , c_code
                     , SUM(vc.cnt) cnt1
                  FROM t_k_de tkd
                 CROSS JOIN t_code tc
                  LEFT OUTER JOIN v_contract vc
                    ON tkd.k_de1 = vc.k_de1
                   AND tkd.k_de2 = vc.k_de2
                   AND vc.inst_l || '10' || SUBSTR(vc.inst_s, 7, 2) = c_code
                 WHERE p_code IN ('HW1010','HW2010','HW3010','HW4010')
                 GROUP BY tkd.k_de1, tkd.k_de2, tkd.no1, SUBSTR(p_code, 1, 4), c_code
                ) a
          LEFT OUTER JOIN v_schedule_work vsc
            ON a.kde1 = vsc.k_de1
           AND a.kde2 = vsc.k_de2
           AND vsc.inst_l || '10' || SUBSTR(vsc.inst_s, 7, 2) = a.c_code
         GROUP BY a.kde1, a.kde2, a.no1, a.p_code, a.c_code, a.cnt1
        ) b
 GROUP BY ROLLUP(kde1, p_code, c_code)
;

 

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