안녕하세요.
이와같은 쿼리를 2개정도 더 만들어서 ( 상위 몇개 ) 유니온으로 잡은 뒤 셀렉트 날린결과와 인서트를 날린결과가 다르게 들어갑니다.
/*+ RULE */ 이라는 힌트를 사용해서 셀렉트 해보니 인서트시 잘못들어간 데이터가 나오더군요 인서트할경우 저 힌트가 들어간 실행계획을 타는것 같습니다.
각각 정렬을하고 유니온으로 잡는것때문에 문제가 되고있는거같습니다 각각 정렬을 해서 한데모아 집계를 해야해서 각각 정렬은 불가피한것같은데
실행계획이 변경되지 않게 인서트하도록 사용하는 힌트라던가 혹은 다른방법이 있는지 조언좀부탁드리겠습니다...
SELECT *
FROM (
SELECT TELECOM_CD
,CERT_NUM
,SUM( IS_TOT_CNT ) IS_TOT_CNT
,SUM( NS_TOT_CNT ) NS_TOT_CNT
,SUM(TRUVIR_00) TRUVIR_00
,SUM( IS_TOT_CNT + NS_TOT_CNT ) IS_NS_CNT
,SUM( IS_TOT_CNT - TRUVIR_00 ) UVIR_ERR
,SUM( OCR_CHG_00) OCR_CHG_00
,SUM( OCR_CHG_01) OCR_CHG_01
,SUM( OCR_CHG_02) OCR_CHG_02
FROM ICSM_SCANNER_OPEN_STAT_TB
WHERE OPEN_DT >= 20170101
AND OPEN_DT <= 20170108
AND TELECOM_CD = 'F3001'
GROUP BY CERT_NUM
ORDER BY IS_NS_CNT DESC, UVIR_ERR DESC , NS_TOT_CNT DESC
)
WHERE 1=1
AND ROWNUM <= 200
답변감사합니다.
이런식으로 묶어서 하나로 만들어 TELECOM_CD 기준으로 그룹하여 집계를 합니다
SELECT *
FROM (
SELECT TELECOM_CD
,CERT_NUM
,SUM( IS_TOT_CNT ) IS_TOT_CNT
,SUM( NS_TOT_CNT ) NS_TOT_CNT
,SUM(TRUVIR_00) TRUVIR_00
,SUM( IS_TOT_CNT + NS_TOT_CNT ) IS_NS_CNT
,SUM( IS_TOT_CNT - TRUVIR_00 ) UVIR_ERR
,SUM( OCR_CHG_00) OCR_CHG_00
,SUM( OCR_CHG_01) OCR_CHG_01
,SUM( OCR_CHG_02) OCR_CHG_02
FROM ICSM_SCANNER_OPEN_STAT_TB
WHERE OPEN_DT >= 20170101
AND OPEN_DT <= 20170108
AND TELECOM_CD = 'F3001'
GROUP BY TELECOM_CD,CERT_NUM
ORDER BY IS_NS_CNT DESC, UVIR_ERR DESC , NS_TOT_CNT DESC
)
WHERE 1=1
AND ROWNUM <= 200
UNION
SELECT *
FROM (
SELECT TELECOM_CD
,CERT_NUM
,SUM( IS_TOT_CNT ) IS_TOT_CNT
,SUM( NS_TOT_CNT ) NS_TOT_CNT
,SUM(TRUVIR_00) TRUVIR_00
,SUM( IS_TOT_CNT + NS_TOT_CNT ) IS_NS_CNT
,SUM( IS_TOT_CNT - TRUVIR_00 ) UVIR_ERR
,SUM( OCR_CHG_00) OCR_CHG_00
,SUM( OCR_CHG_01) OCR_CHG_01
,SUM( OCR_CHG_02) OCR_CHG_02
FROM ICSM_SCANNER_OPEN_STAT_TB
WHERE OPEN_DT >= 20170101
AND OPEN_DT <= 20170108
AND TELECOM_CD = 'F3002'
GROUP BY TELECOM_CD,CERT_NUM
ORDER BY IS_NS_CNT DESC, UVIR_ERR DESC , NS_TOT_CNT DESC
)
WHERE 1=1
AND ROWNUM <= 200
UNION
SELECT *
FROM (
SELECT TELECOM_CD
,CERT_NUM
,SUM( IS_TOT_CNT ) IS_TOT_CNT
,SUM( NS_TOT_CNT ) NS_TOT_CNT
,SUM(TRUVIR_00) TRUVIR_00
,SUM( IS_TOT_CNT + NS_TOT_CNT ) IS_NS_CNT
,SUM( IS_TOT_CNT - TRUVIR_00 ) UVIR_ERR
,SUM( OCR_CHG_00) OCR_CHG_00
,SUM( OCR_CHG_01) OCR_CHG_01
,SUM( OCR_CHG_02) OCR_CHG_02
FROM ICSM_SCANNER_OPEN_STAT_TB
WHERE OPEN_DT >= 20170101
AND OPEN_DT <= 20170108
AND TELECOM_CD = 'F3003'
GROUP BY TELECOM_CD,CERT_NUM
ORDER BY IS_NS_CNT DESC, UVIR_ERR DESC , NS_TOT_CNT DESC
)
WHERE 1=1
AND ROWNUM <= 200
SELECT * FROM (SELECT telecom_cd , cert_num , SUM(is_tot_cnt) is_tot_cnt , SUM(ns_tot_cnt) ns_tot_cnt , SUM(truvir_00 ) truvir_00 , SUM(is_tot_cnt + ns_tot_cnt) is_ns_cnt , SUM(is_tot_cnt - truvir_00 ) uvir_err , SUM(ocr_chg_00) ocr_chg_00 , SUM(ocr_chg_01) ocr_chg_01 , SUM(ocr_chg_02) ocr_chg_02 , ROW_NUMBER() OVER( PARTITION BY telecom_cd ORDER BY SUM(is_tot_cnt + ns_tot_cnt) DESC , SUM(is_tot_cnt - truvir_00 ) DESC , SUM(ns_tot_cnt) DESC , cert_num -- Unique 항목 추가 ) rn FROM icsm_scanner_open_stat_tb WHERE open_dt >= '20170101' AND open_dt <= '20170108' AND telecom_cd IN ('F3001', 'F3002', 'F3003') GROUP BY telecom_cd, cert_num ) WHERE 1=1 AND rn <= 200 ;