by 겸둥이k [SQL Query] 인덱스 쿼리 속도 [2019.04.01 14:32:00]
쿼리~~~~~~~~~~~~~~~~~~~~~~~~~
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 이 풀스캔을 때려버리네요...ㅠ 이것때문에 그런건지 인덱스 똑같이 줬는데말이죠
뷰 안의 3개 쿼리 중 2,3 번 쿼리가 같은 테이블이네요?
또한 조건을 보면 3번 결과가 2번 결과를 포함하네요? 왜 이런건지? 이러면 중복 카운트가 될 것 같은데요?
2번 쿼리는 Group By 를 하는데 왜 2번만 그룹바이 하나요? 일관성이 없어 보이네요.
2번 쿼리는 Group By 때문에 메인쿼리 조건이 서브쿼리 안으로 침투하지 못하는 것 같습니다.
스칼라 서브쿼리를 아우터조인 형태로 변경할 수 있습니다.
기타 문제점
- 쓸데 없는 함수 사용 : f_code 함수가 안에서고 실행되고 밖에서 또 다시 실행됨. 안쪽 함수 제거 필요
- 쓸데 없는 정렬 사용 : 안쪽 정렬은 불필요합니다.
- tkd 와 tc 간 조인 조건이 없는데? 이거 맞나요?
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) ;