데이터베이스 질문과 답변
by PIANO [2015.01.07 17:35:50]
속도가 너무 느려서 EXISTS 문으로 속도를 개선을 하려고 하니 더 느려졌습니다.
TN_FHL_MASTR [PK] : FHL_SEQ_NO , AWB_PREFIX , AWB_SN , HOUSBL_NO
TTXSTAT [PK] : M_STXID
COMPNY_CODE 로 조회를 해야하는데 TN_FHL_MASTR , TTXSTAT 에 COMPNY_CODE가 없어서 Mapping 테이블 사용 하였습니다.
첨부파일로 플렌정보를 집어넣었습니다.
----------------------------------------
기존 소스
SELECT*
FROM (SELECT T21.*,
CASE WHEN BEFORE_DUP_DT IS NULL
THEN ROW_NUMBER() OVER(PARTITION BY TO_CHAR(M_DCTIME, 'YYYYMMDD'), SENDER_ID, AWB_PREFIX, AWB_SN, HOUSBL_NO ORDER BY M_DCTIME)
ELSE 2
END DUP_NO
FROM (SELECT T11.FHL_SEQ_NO,
T11.AWB_PREFIX,
T11.AWB_SN,
T11.HOUSBL_NO,
T11.SENDER_ID, /* TPCODE */
T12.M_DCTIME,
(SELECT TO_CHAR(MIN(T02.M_DCTIME), 'YYYYMMDDHH24MISS')
FROM KCN.TN_FHL_MASTR T01,
KCN.TTXSTAT T02
WHERE 1=1
AND T01.FHL_SEQ_NO = T02.M_STXID
AND T02.M_SDOCCODE = 'FHLX'
AND T02.M_DCTIME >= TO_DATE(TO_CHAR(T12.M_DCTIME-30, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS')
AND T02.M_DCTIME < TO_DATE(TO_CHAR(T12.M_DCTIME, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS')
AND T01.AWB_PREFIX = T11.AWB_PREFIX
AND T01.AWB_SN = T11.AWB_SN
AND T01.HOUSBL_NO = T11.HOUSBL_NO
AND T01.SENDER_ID = T11.SENDER_ID) BEFORE_DUP_DT
FROM KCN.TN_FHL_MASTR T11,
KCN.TTXSTAT T12
WHERE 1=1
AND T11.FHL_SEQ_NO = T12.M_STXID
AND T12.M_SDOCCODE = 'FHLX'
AND T12.M_DCTIME BETWEEN TO_DATE(:V_JOB_DAY || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE(:V_JOB_DAY || '235959', 'YYYYMMDDHH24MISS') )T21 )T31,
--AND T12.M_DCTIME BETWEEN TO_DATE('20140901' || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE('20140914' || '235959', 'YYYYMMDDHH24MISS') )T21 )T31,
(SELECT *
FROM KCNMFCS.TN_CMCS_TPCODE_MAPPING
WHERE 1=1
AND COMPNY_TYPE = 'F'
AND (COMPNY_CODE,SYS_IDX,SUB_COMPNY_CODE) NOT IN (('KECI','F0000475','KECI')) )T32
WHERE 1=1
AND T31.SENDER_ID = T32.SUB_COMPNY_CODE
AND DUP_NO = '1'
AND T32.COMPNY_CODE = 'HDLC'
수정소스
SELECT *
FROM (SELECT T11.*
FROM KCN.TN_FHL_MASTR T11,
KCN.TTXSTAT T12
WHERE 1=1
AND T11.FHL_SEQ_NO = T12.M_STXID
AND T12.M_SDOCCODE = 'FHLX'
AND T12.M_DCTIME BETWEEN TO_DATE('20141121' || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE('20141121'|| '235959', 'YYYYMMDDHH24MISS')
AND NOT EXISTS (SELECT 1
FROM KCN.TN_FHL_MASTR T01,
KCN.TTXSTAT T02
WHERE 1=1
AND T01.FHL_SEQ_NO = T02.M_STXID
AND T02.M_SDOCCODE = 'FHLX'
AND T02.M_DCTIME >= TO_DATE(TO_CHAR(T12.M_DCTIME-30, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS')
AND T02.M_DCTIME < T12.M_DCTIME
AND T01.AWB_PREFIX = T11.AWB_PREFIX
AND T01.AWB_SN = T11.AWB_SN
AND T01.HOUSBL_NO = T11.HOUSBL_NO
AND T01.SENDER_ID = T11.SENDER_ID
)
) T33 ,
(SELECT *
FROM KCNMFCS.TN_CMCS_TPCODE_MAPPING
WHERE 1=1
AND COMPNY_TYPE = 'F'
AND (COMPNY_CODE,SYS_IDX,SUB_COMPNY_CODE) NOT IN (('KECI','F0000475','KECI'))
)T34
WHERE 1=1
AND T33.SENDER_ID = T34.SUB_COMPNY_CODE
AND T34.COMPNY_CODE = 'HDLC'
30 일 내에 자료가 존재하지 않는 건 1건을 가져오는 건가요?
기존 1건만 가져오는 조건이 수정 SQL 에는 없네요?
Not Exists 조건 밑에 AND ROWNUM <= 1 조건 추가하세요.
넵 30일 기간 중에 중복건에 대해 1건만 가져와서 처리하는 로직입니다.
넵 ROWNUM <=1 추가 하였습니다. 감사합니다.
SELECT *
FROM KCN.TN_FHL_MASTR T11
, KCN.TTXSTAT T12
, KCNMFCS.TN_CMCS_TPCODE_MAPPING M11
WHERE T11.FHL_SEQ_NO = T12.M_STXID
AND T11.SENDER_ID = M11.SUB_COMPNY_CODE
AND T12.M_SDOCCODE = 'FHLX'
AND T12.M_DCTIME BETWEEN TO_DATE('20141121' || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE('20141121'|| '235959', 'YYYYMMDDHH24MISS')
AND M11.COMPNY_TYPE = 'F'
AND M11.COMPNY_CODE = 'HDLC'
AND (M11.SYS_IDX,SUB_COMPNY_CODE) NOT IN (('F0000475','KECI'))
AND NOT EXISTS (SELECT 1
FROM KCN.TTXSTAT T02
WHERE T02.M_STXID = T12.M_STXID
AND T02.M_SDOCCODE = 'FHLX'
AND T02.M_DCTIME >= TO_DATE(TO_CHAR(T12.M_DCTIME-30, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS')
AND T02.M_DCTIME < T12.M_DCTIME
)
AND ROWNUM = 1
혹시 이렇게 안 되나요?
KCNMFCS.TN_CMCS_TPCODE_MAPPING는 인라인뷰 말고 직접 조인하고
NOT EXISTS 조건에서 굳이 조인을 하지 않아도 될거 같은데요.
그리고 T11은 조건이 없어서 FULL SCAN 할수 밖에 없을 거 같으니 T12를 리딩테이블로 하는게 좋을거 같고요.
-- 다시 보니 Partition By 구문으로 인해 단순 Rownum = 1 만 해서는 안되겠네요. SELECT * FROM (SELECT t21.* , ROW_NUMBER() OVER( PARTITION BY sender_id, awb_prefix, awb_sn, housbl_no ORDER BY m_dctime ) rn FROM (SELECT t11.* , COUNT(*) OVER( PARTITION BY t11.sender_id, t11.awb_prefix, t11.awb_sn, t11.housbl_no ORDER BY t11.m_dctime ) cnt FROM kcn.tn_fhl_mastr t11 , kcn.ttxstat t12 WHERE 1=1 AND t11.fhl_seq_no = t12.m_stxid AND t12.m_sdoccode = 'FHLX' AND t12.m_dctime >= TO_DATE(:v_job_day, 'yyyymmdd') - 30 AND t12.m_dctime < TO_DATE(:v_job_day, 'yyyymmdd') + 1 ) t21 WHERE cnt = 1 AND m_dctime >= TO_DATE(:v_job_day, 'yyyymmdd') ) WHERE rn = 1 ;
) 버튼을 클릭하여 작성 하시면 됩니다.