Postgresql / PPAS/Query tuning
[완료]/*contentExpand.distr.selectExposureNewScheduleList*/
원샷원따봉
2022. 8. 29. 14:17
[튜닝 결과]
|
[튜닝 내용]
- 튜닝 후 sql 줄번호 209 ~ 216 참고
- 조건을 초기 필터 조건으로 추가하여 GROUP BY 대상을 줄임. 218663 건을 70건으로 줄임
* 원본 쿼리
SELECT /*contentExpand.distr.selectExposureNewScheduleList*/ * FROM( SELECT AA.SCHEDULE_ID, AA.SCHEDULE_TYPE, AA.SCHEDULE_TITLE, AA.SCREEN_TYPE_LIST, CASE WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 1, 1) = '1' THEN 'I' WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE = 'N' THEN 'N' WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE <> 'N' THEN 'M' WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 3, 1) = '1' THEN 'U' ELSE '' END SCREEN_TYPE, AA.SCREEN_TYPE_LIST_NAME, AA.CONTENT_CNT::integer, AA.STATUS, AA.AUTH_YN, AA.NEW_SCHEDULE_YN, AA.APPROVE_DATE, AA.INSERT_DATE, AA.INSERT_ID, AA.INSERT_NAME, AA.UPDATE_DATE, AA.START_DATE, AA.START_DAY, AA.END_DATE, AA.ALBUM_ID, AA.ISSUED_ALBUM_ID, CASE WHEN AA.STATUS ='P' THEN '1' ELSE '2' END STATUS_SB, AA.COMPLETE_Y, AA.COMPLETE_N, AA.CUESHEET_ITEM_TYPE, CASE WHEN AA.SCHEDULE_TYPE = 'N' THEN '일반' ELSE '재배포' END SCHEDULE_TYPE_NM, CASE WHEN AA.STATUS = 'E' THEN '공정에러' WHEN AA.STATUS = 'N' THEN '등록' WHEN AA.STATUS = 'R' THEN '스케줄실행중' WHEN AA.STATUS = 'C' THEN '대기' WHEN AA.STATUS = 'A' THEN '적용' WHEN AA.STATUS = 'S' THEN '보류' WHEN AA.STATUS = 'Y' THEN '완료' WHEN AA.STATUS = 'X' THEN '임시저장' WHEN AA.STATUS = 'F' THEN '실패(취소)' WHEN AA.STATUS = 'P' THEN '부분승인' ELSE '' END STATUS_NM, AA.EXCEPT_STATUS, AA.EXCEPT_CNT::integer FROM ( SELECT A.SCHEDULE_ID, A.SCHEDULE_TYPE, COALESCE(A.SCHEDULE_TITLE, '신규스케줄(컨텐츠명 존재하지 않음)') AS SCHEDULE_TITLE, A.SCREEN_TYPE_LIST, RTRIM( CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 1, 1) = '1' THEN 'I/' ELSE '' END || CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 2, 1) = '1' THEN 'N/' ELSE '' END || CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 3, 1) = '1' THEN 'U/' ELSE '' END, '/' ) AS SCREEN_TYPE_LIST_NAME, ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID ) AS CONTENT_CNT, CASE WHEN A.CHECK_STATE = 'E' THEN 'E' WHEN A.EXCEPT_YN = 'Y' THEN 'P' ELSE CASE WHEN A.STATUS = 'R' THEN CASE WHEN A.CHECK_STATE = 'Y' THEN CASE WHEN A.AUTH_YN = 'N' THEN 'C' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END ELSE CASE WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END END ELSE A.STATUS END END STATUS, CASE WHEN A.CHECK_STATE = 'E' THEN 'E' ELSE CASE WHEN A.STATUS = 'R' THEN CASE WHEN A.CHECK_STATE = 'Y' THEN CASE WHEN A.AUTH_YN = 'N' THEN 'C' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END ELSE CASE WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END END ELSE A.STATUS END END EXCEPT_STATUS, A.AUTH_YN, A.NEW_SCHEDULE_YN, A.APPROVE_DATE, A.INSERT_DATE, A.INSERT_ID, (SELECT USER_NAME FROM imcsuser.PT_LW_USER LU WHERE LU.USER_ID = A.INSERT_ID) INSERT_NAME, A.UPDATE_DATE, A.START_DATE, A.START_DAY, A.END_DATE, A.ALBUM_ID, A.ISSUED_ALBUM_ID, CASE WHEN A.SCREEN_TYPE = 'I' THEN ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND ZZ.AUTH_IPTV_YN IN ('Y', 'F') ) ELSE ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND ZZ.AUTH_NSC_YN IN ('Y', 'F') ) END COMPLETE_Y, CASE WHEN A.SCREEN_TYPE = 'I' THEN ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND COALESCE(ZZ.AUTH_IPTV_YN, 'N') = 'N' ) ELSE ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND COALESCE(ZZ.AUTH_NSC_YN, 'N') = 'N' ) END COMPLETE_N, A.CUESHEET_ITEM_TYPE, A.EXCEPT_CNT FROM ( SELECT MST.SCHEDULE_ID AS SCHEDULE_ID, AST.SCREEN_TYPE AS SCREEN_TYPE, MAX(MST.SCHEDULE_TYPE) AS SCHEDULE_TYPE, MAX(CASE WHEN MST.SERIES_YN = 'Y' THEN MST.SERIES_IMCS_NAME ELSE DTL.CONTENTS_NAME END) AS SCHEDULE_TITLE, MAX(CASE WHEN AST.SCREEN_TYPE = 'I' THEN '10' ELSE '01' END || CASE WHEN NULLIF(AST.UFLIX_FLAG,'') IS NULL THEN '0' ELSE '1' END) AS SCREEN_TYPE_LIST, MAX(MST.GENRE_LARGE) AS GENRE_LARGE, MAX(MST.GENRE_MID) AS GENRE_MID, MAX(MST.GENRE_SMALL) AS GENRE_SMALL, MAX(MST.SERIES_ID_IMCS) AS SERIES_ID_IMCS, MAX(MST.SERIES_IMCS_NAME) AS SERIES_IMCS_NAME, MAX(MST.STATUS) AS STATUS, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MIN(COALESCE(PRC.CHECK_IPTV_STATE, 'N')) ELSE MIN(COALESCE(PRC.CHECK_NSC_STATE, 'N')) END CHECK_STATE, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MAX(COALESCE(CASE WHEN DTL.AUTH_IPTV_YN = 'Y' THEN '' WHEN DTL.AUTH_IPTV_YN = 'F' THEN '' ELSE PRC.CHECK_IPTV_STATE END, 'N')) ELSE MAX(COALESCE(CASE WHEN DTL.AUTH_NSC_YN = 'Y' THEN '' WHEN DTL.AUTH_NSC_YN ='F' THEN '' ELSE PRC.CHECK_NSC_STATE END, 'N')) END CHECK_STATE_MAX, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MIN(CASE WHEN PRC.VIEW_FLAG = 'I' THEN 'Y' WHEN PRC.VIEW_FLAG = 'Y' THEN 'Y' ELSE 'N' END) ELSE MIN(CASE WHEN PRC.VIEW_FLAG = 'N' THEN 'Y' WHEN PRC.VIEW_FLAG = 'Y' THEN 'Y' ELSE 'N' END) END VIEW_FLAG, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MIN(COALESCE(DTL.AUTH_IPTV_YN, 'N')) ELSE MIN(COALESCE(DTL.AUTH_NSC_YN, 'N')) END AUTH_YN, MAX('Y') AS NEW_SCHEDULE_YN, MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS APPROVE_DATE, MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS START_DATE, MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYYMMDD')) AS START_DAY, MAX(TO_CHAR(TO_DATE(AST.SVC_END_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS END_DATE, MAX(TO_CHAR(TO_TIMESTAMP(MST.INSERT_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS INSERT_DATE, MAX(MST.INSERT_ID) AS INSERT_ID, MAX(TO_CHAR(TO_TIMESTAMP(MST.UPDATE_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS UPDATE_DATE, MAX(MST.UPDATE_ID), MAX(DTL.ALBUM_ID) AS ALBUM_ID, MAX(DTL.ISSUED_ALBUM_ID) AS ISSUED_ALBUM_ID, MAX(AST.EXCEPT_YN) AS EXCEPT_YN, (SELECT COUNT(*) FROM imcsuser.PT_LW_SCHE_ASSET AA WHERE MST.SCHEDULE_ID = AA.SCHEDULE_ID AND AA.EXCEPT_YN = 'Y') AS EXCEPT_CNT, COALESCE(MAX(DTL.CUESHEET_ITEM_TYPE),'N') AS CUESHEET_ITEM_TYPE FROM imcsuser.PT_LW_SCHE_MST MST, imcsuser.PT_LW_SCHE_ASSET AST, imcsuser.PT_LW_SCHE_DTL DTL, imcsuser.PT_LW_SCHE_PROCESS_INFO PRC WHERE MST.SCHEDULE_ID = AST.SCHEDULE_ID AND MST.SCHEDULE_ID = DTL.SCHEDULE_ID AND MST.SCHEDULE_ID = PRC.SCHEDULE_ID AND DTL.SCHEDULE_ID = AST.SCHEDULE_ID AND DTL.SEQ_NO = AST.SEQ_NO AND DTL.SEQ_NO = PRC.SEQ_NO AND MST.STATUS != 'X' AND PRC.PROGRAMMED IN ('M','A','Y') AND AST.SVC_START_DATE BETWEEN '20200801' AND '20210801' GROUP BY MST.SCHEDULE_ID, AST.SCREEN_TYPE ) A ) AA WHERE AA.STATUS = UPPER('P') )BB ORDER BY BB.STATUS_SB, BB.INSERT_DATE DESC, BB.SCREEN_TYPE_LIST DESC |
* 튜닝쿼리
SELECT /*contentExpand.distr.selectExposureNewScheduleList*/ * FROM( SELECT AA.SCHEDULE_ID, AA.SCHEDULE_TYPE, AA.SCHEDULE_TITLE, AA.SCREEN_TYPE_LIST, CASE WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 1, 1) = '1' THEN 'I' WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE = 'N' THEN 'N' WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 2, 1) = '1' AND AA.CUESHEET_ITEM_TYPE <> 'N' THEN 'M' WHEN SUBSTR(AA.SCREEN_TYPE_LIST, 3, 1) = '1' THEN 'U' ELSE '' END SCREEN_TYPE, AA.SCREEN_TYPE_LIST_NAME, AA.CONTENT_CNT::integer, AA.STATUS, AA.AUTH_YN, AA.NEW_SCHEDULE_YN, AA.APPROVE_DATE, AA.INSERT_DATE, AA.INSERT_ID, AA.INSERT_NAME, AA.UPDATE_DATE, AA.START_DATE, AA.START_DAY, AA.END_DATE, AA.ALBUM_ID, AA.ISSUED_ALBUM_ID, CASE WHEN AA.STATUS ='P' THEN '1' ELSE '2' END STATUS_SB, AA.COMPLETE_Y, AA.COMPLETE_N, AA.CUESHEET_ITEM_TYPE, CASE WHEN AA.SCHEDULE_TYPE = 'N' THEN '일반' ELSE '재배포' END SCHEDULE_TYPE_NM, CASE WHEN AA.STATUS = 'E' THEN '공정에러' WHEN AA.STATUS = 'N' THEN '등록' WHEN AA.STATUS = 'R' THEN '스케줄실행중' WHEN AA.STATUS = 'C' THEN '대기' WHEN AA.STATUS = 'A' THEN '적용' WHEN AA.STATUS = 'S' THEN '보류' WHEN AA.STATUS = 'Y' THEN '완료' WHEN AA.STATUS = 'X' THEN '임시저장' WHEN AA.STATUS = 'F' THEN '실패(취소)' WHEN AA.STATUS = 'P' THEN '부분승인' ELSE '' END STATUS_NM, AA.EXCEPT_STATUS, AA.EXCEPT_CNT::integer FROM ( SELECT A.SCHEDULE_ID, A.SCHEDULE_TYPE, COALESCE(A.SCHEDULE_TITLE, '신규스케줄(컨텐츠명 존재하지 않음)') AS SCHEDULE_TITLE, A.SCREEN_TYPE_LIST, RTRIM( CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 1, 1) = '1' THEN 'I/' ELSE '' END || CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 2, 1) = '1' THEN 'N/' ELSE '' END || CASE WHEN SUBSTR(A.SCREEN_TYPE_LIST, 3, 1) = '1' THEN 'U/' ELSE '' END, '/' ) AS SCREEN_TYPE_LIST_NAME, ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID ) AS CONTENT_CNT, CASE WHEN A.CHECK_STATE = 'E' THEN 'E' WHEN A.EXCEPT_YN = 'Y' THEN 'P' ELSE CASE WHEN A.STATUS = 'R' THEN CASE WHEN A.CHECK_STATE = 'Y' THEN CASE WHEN A.AUTH_YN = 'N' THEN 'C' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END ELSE CASE WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END END ELSE A.STATUS END END STATUS, CASE WHEN A.CHECK_STATE = 'E' THEN 'E' ELSE CASE WHEN A.STATUS = 'R' THEN CASE WHEN A.CHECK_STATE = 'Y' THEN CASE WHEN A.AUTH_YN = 'N' THEN 'C' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END ELSE CASE WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'N' THEN 'C' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'N' THEN 'A' WHEN CHECK_STATE_MAX = 'Y' AND A.AUTH_YN = 'Y' AND A.VIEW_FLAG = 'Y' THEN 'Y' ELSE A.STATUS END END ELSE A.STATUS END END EXCEPT_STATUS, A.AUTH_YN, A.NEW_SCHEDULE_YN, A.APPROVE_DATE, A.INSERT_DATE, A.INSERT_ID, (SELECT USER_NAME FROM imcsuser.PT_LW_USER LU WHERE LU.USER_ID = A.INSERT_ID) INSERT_NAME, A.UPDATE_DATE, A.START_DATE, A.START_DAY, A.END_DATE, A.ALBUM_ID, A.ISSUED_ALBUM_ID, CASE WHEN A.SCREEN_TYPE = 'I' THEN ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND ZZ.AUTH_IPTV_YN IN ('Y', 'F') ) ELSE ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND ZZ.AUTH_NSC_YN IN ('Y', 'F') ) END COMPLETE_Y, CASE WHEN A.SCREEN_TYPE = 'I' THEN ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND COALESCE(ZZ.AUTH_IPTV_YN, 'N') = 'N' ) ELSE ( SELECT COUNT(ZZ.ALBUM_ID) FROM imcsuser.PT_LW_SCHE_DTL ZZ WHERE A.SCHEDULE_ID = ZZ.SCHEDULE_ID AND COALESCE(ZZ.AUTH_NSC_YN, 'N') = 'N' ) END COMPLETE_N, A.CUESHEET_ITEM_TYPE, A.EXCEPT_CNT FROM ( SELECT MST.SCHEDULE_ID AS SCHEDULE_ID, AST.SCREEN_TYPE AS SCREEN_TYPE, MAX(MST.SCHEDULE_TYPE) AS SCHEDULE_TYPE, MAX(CASE WHEN MST.SERIES_YN = 'Y' THEN MST.SERIES_IMCS_NAME ELSE DTL.CONTENTS_NAME END) AS SCHEDULE_TITLE, MAX(CASE WHEN AST.SCREEN_TYPE = 'I' THEN '10' ELSE '01' END || CASE WHEN NULLIF(AST.UFLIX_FLAG,'') IS NULL THEN '0' ELSE '1' END) AS SCREEN_TYPE_LIST, MAX(MST.GENRE_LARGE) AS GENRE_LARGE, MAX(MST.GENRE_MID) AS GENRE_MID, MAX(MST.GENRE_SMALL) AS GENRE_SMALL, MAX(MST.SERIES_ID_IMCS) AS SERIES_ID_IMCS, MAX(MST.SERIES_IMCS_NAME) AS SERIES_IMCS_NAME, MAX(MST.STATUS) AS STATUS, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MIN(COALESCE(PRC.CHECK_IPTV_STATE, 'N')) ELSE MIN(COALESCE(PRC.CHECK_NSC_STATE, 'N')) END CHECK_STATE, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MAX(COALESCE(CASE WHEN DTL.AUTH_IPTV_YN = 'Y' THEN '' WHEN DTL.AUTH_IPTV_YN = 'F' THEN '' ELSE PRC.CHECK_IPTV_STATE END, 'N')) ELSE MAX(COALESCE(CASE WHEN DTL.AUTH_NSC_YN = 'Y' THEN '' WHEN DTL.AUTH_NSC_YN ='F' THEN '' ELSE PRC.CHECK_NSC_STATE END, 'N')) END CHECK_STATE_MAX, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MIN(CASE WHEN PRC.VIEW_FLAG = 'I' THEN 'Y' WHEN PRC.VIEW_FLAG = 'Y' THEN 'Y' ELSE 'N' END) ELSE MIN(CASE WHEN PRC.VIEW_FLAG = 'N' THEN 'Y' WHEN PRC.VIEW_FLAG = 'Y' THEN 'Y' ELSE 'N' END) END VIEW_FLAG, CASE WHEN AST.SCREEN_TYPE = 'I' THEN MIN(COALESCE(DTL.AUTH_IPTV_YN, 'N')) ELSE MIN(COALESCE(DTL.AUTH_NSC_YN, 'N')) END AUTH_YN, MAX('Y') AS NEW_SCHEDULE_YN, MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS APPROVE_DATE, MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS START_DATE, MAX(TO_CHAR(TO_DATE(AST.SVC_START_DATE, 'YYYYMMDD'), 'YYYYMMDD')) AS START_DAY, MAX(TO_CHAR(TO_DATE(AST.SVC_END_DATE, 'YYYYMMDD'), 'YYYY-MM-DD')) AS END_DATE, MAX(TO_CHAR(TO_TIMESTAMP(MST.INSERT_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS INSERT_DATE, MAX(MST.INSERT_ID) AS INSERT_ID, MAX(TO_CHAR(TO_TIMESTAMP(MST.UPDATE_DATE, 'YYYYMMDDHH24MISS'), 'YYYY-MM-DD HH24:MI:SS')) AS UPDATE_DATE, MAX(MST.UPDATE_ID), MAX(DTL.ALBUM_ID) AS ALBUM_ID, MAX(DTL.ISSUED_ALBUM_ID) AS ISSUED_ALBUM_ID, MAX(AST.EXCEPT_YN) AS EXCEPT_YN, (SELECT COUNT(*) FROM imcsuser.PT_LW_SCHE_ASSET AA WHERE MST.SCHEDULE_ID = AA.SCHEDULE_ID AND AA.EXCEPT_YN = 'Y') AS EXCEPT_CNT, COALESCE(MAX(DTL.CUESHEET_ITEM_TYPE),'N') AS CUESHEET_ITEM_TYPE FROM imcsuser.PT_LW_SCHE_MST MST, imcsuser.PT_LW_SCHE_ASSET AST, imcsuser.PT_LW_SCHE_DTL DTL, imcsuser.PT_LW_SCHE_PROCESS_INFO PRC WHERE MST.SCHEDULE_ID = AST.SCHEDULE_ID AND MST.SCHEDULE_ID = DTL.SCHEDULE_ID AND MST.SCHEDULE_ID = PRC.SCHEDULE_ID AND DTL.SCHEDULE_ID = AST.SCHEDULE_ID AND DTL.SEQ_NO = AST.SEQ_NO AND DTL.SEQ_NO = PRC.SEQ_NO AND MST.STATUS != 'X' AND PRC.PROGRAMMED IN ('M','A','Y') AND AST.SVC_START_DATE BETWEEN '20200801' AND '20210801' /* 마지막에 조건으로 추가되는 것을 초기 조건으로 변경 */ AND AST.EXCEPT_YN = 'Y' GROUP BY MST.SCHEDULE_ID, AST.SCREEN_TYPE ) A ) AA /* 해당 조건을 초기 필터 조건으로 추가하여 GROUP BY 대상을 줄임. 218663 건을 70건으로 줄임. WHERE AA.STATUS = UPPER('P') */ )BB ORDER BY BB.STATUS_SB, BB.INSERT_DATE DESC, BB.SCREEN_TYPE_LIST DESC ; |