-
[완료]/*contentExpand.distr.selectExposureNewScheduleList*/Postgresql / PPAS/Query tuning 2022. 8. 29. 14:17
[튜닝 결과]
- 튜닝 전 : 2.7초
- 튜닝 후 : 0.3초
[튜닝 내용]
- 튜닝 후 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
;'Postgresql / PPAS > Query tuning' 카테고리의 다른 글
[완료] /* index.base.getNewRegIndexAlbumList */ (0) 2022.08.29 [완료]/*VoteRelation.base.selectAlbumList*/ (0) 2022.08.29 [완료]/*album.base.deleteMultiSeriesCategoryMap*/ (0) 2022.08.29 [완료]/* categoryForm.base.getAlbumNameSearchList */ (0) 2022.08.29 [완료]/*distribute.distr.distributeListStatusPageData*/ (0) 2022.08.29