[완료] /* content.base.seriesListPageData */

원샷원따봉 2022. 8. 29. 11:57

개선 포인트
- AS-IS 수행결과
  Total rows: 1000 of 4534
  Query complete 00:00:03.300
  Ln 153, Col 7
- TO-BE 수행결과
  Query complete 00:00:01.600
- 특별히 개선할 포인트가 없지만, 불필요한 구문을 제거하여, 50% 정도의 수행 시간 단축 효과가 있었음.

* 원본 SQL

SELECT
/* content.base.seriesListPageData */
ROW_NUMBER() OVER (ORDER BY AI.CREATE_DATE DESC, AI.SERIES_ID_IMCS, AI.SCREEN_TYPE ) RNUM,
AI.SERIES_ID_IMCS,
AI.ALB_SER_TYPE,
ALP.ALBUM_ID,
AI.SERIES_NAME,
AI.SCREEN_TYPE,
COALESCE(NULLIF(ALS.GENRE_LARGE, ''), '') AS GENRE_LARGE_NAME,
COALESCE(NULLIF(ALS.GENRE_MID, ''), '') AS GENRE_MID_NAME,
COALESCE(NULLIF(ALS.GENRE_SMALL, ''), '') AS GENRE_SMALL_NAME,
COALESCE(NULLIF(
        COALESCE(NULLIF(GENRE_LARGE, ''), '')
        || CASE WHEN NULLIF(GENRE_MID, '') IS NULL THEN '' ELSE '|' || GENRE_MID END
        || CASE WHEN NULLIF(GENRE_SMALL, '') IS NULL THEN '' ELSE '|' || GENRE_SMALL END
        ,''), '-') AS GENRE,
COALESCE(NULLIF(AI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
TO_CHAR(TO_DATE(AI.CREATE_DATE, 'YYYYMMDD'), 'YYYY-MM-DD' ) AS CREATE_DATE,
AI.SERIES_GROUP_CNT,
COUNT(1) OVER (PARTITION BY AI.SERIES_ID_IMCS) ALBUM_ID_CNT,
    COALESCE(REGEXP_REPLACE(NULLIF(ALP.MAXIMUM_VIEWING_LENGTH, ''),'[^0-9]','','g') :: INTEGER / 2400,'0') AS MAXIMUM_VIEWING_DAY,
UFLIX_PROD_YN,
VIEWING_FLAG
FROM
(
SELECT
SERIES_ID_IMCS,
MAX(ALBUM_ID) AS ALBUM_ID,
MAX(ALB_SER_TYPE) AS ALB_SER_TYPE,
SCREEN_TYPE,
COUNT(DISTINCT ALBUM_ID) AS SERIES_GROUP_CNT,
MAX(RELEASE_DATE) AS RELEASE_DATE,
MAX(SERIES_NAME) AS SERIES_NAME,
MAX(SUGGESTED_PRICE) AS SUGGESTED_PRICE,
MAX(CREATE_DATE) AS CREATE_DATE,
MAX(UFLIX_PROD_YN) AS UFLIX_PROD_YN,
MAX(VIEWING_FLAG) AS VIEWING_FLAG
FROM
(
SELECT
ALI.ALB_SER_TYPE,
ALI.ALBUM_ID,
ALI.SERIES_NAME,
ALI.RELEASE_DATE,
ALI.SERIES_ID_IMCS,
ALI.ASSET_ID,
COALESCE(NULLIF(ALI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
ALI.CREATE_DATE,
ALI.SCREEN_TYPE,
CASE
WHEN COALESCE(NULLIF(C.VOD_NSC_GB, ''), 'NNNN') = 'UFX' THEN 'Y'
ELSE 'N'
END UFLIX_PROD_YN,
P.IMCS_PRODUCT_NAME,
P.IMCS_EXPIRED_DATE,
P.IMCS_PRICE,
(
CASE
WHEN ALI.SCREEN_TYPE = 'I' THEN (CASE
WHEN ALI.ASSET_TYPE = '3D' THEN '1'
WHEN ALI.ASSET_TYPE = 'HD' THEN '2'
WHEN ALI.ASSET_TYPE = 'SH' THEN '3'
WHEN ALI.ASSET_TYPE = 'SD' THEN '4'
WHEN ALI.ASSET_TYPE = 'U1' THEN '5'
WHEN ALI.ASSET_TYPE = 'U2' THEN '6'
WHEN ALI.ASSET_TYPE = 'PR' THEN '7'
ELSE '8'
END)
ELSE ALI.ASSET_TYPE
END
) AS ASSET_TYPE,
(
CASE
WHEN ALI.SCREEN_TYPE = 'N'
AND C.VOD_CATEGORY_GB = 'NSC' THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
WHEN ALI.SCREEN_TYPE = 'I'
AND C.VOD_CATEGORY_GB IN ('I20', 'I30') THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
ELSE ''
END
) AS VIEWING_FLAG,
DENSE_RANK () OVER (PARTITION BY SERIES_ID_IMCS, SCREEN_TYPE ORDER BY ALI.ASSET_TYPE ) RNK
FROM
(
SELECT
(
CASE
WHEN NULLIF(SAM.ALBUM_GROUP_ID,
'') IS NULL THEN 'CON'
ELSE 'S'
END
) AS ALB_SER_TYPE,
(
CASE
WHEN SAM.ALBUM_GROUP_ID IS NULL
OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_ID
ELSE SAM.ALBUM_GROUP_ID
END
) AS SERIES_ID_IMCS,
(
CASE
WHEN SAM.ALBUM_GROUP_ID IS NULL
OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_NAME
ELSE SER_MST.ALBUM_GROUP_NM
END
) AS SERIES_NAME,
ALI.ALBUM_ID,
ASI.ASSET_ID,
ALI.RELEASE_DATE,
COALESCE(NULLIF(ASI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
ASI.CREATE_DATE,
ASI.SCREEN_TYPE,
ASI.ASSET_TYPE
FROM
imcsuser.PT_LA_ALBUM_INFO ALI
INNER JOIN imcsuser.PT_LA_ASSET_INFO ASI ON
ALI.ALBUM_ID = ASI.ALBUM_ID
LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION SAM ON
ALI.ALBUM_ID = SAM.ALBUM_ID
AND 'S' = SAM.GROUP_TYPE
LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_GROUP SER_MST ON
SAM.ALBUM_GROUP_ID = SER_MST.ALBUM_GROUP_ID
AND SAM.GROUP_TYPE = SER_MST.GROUP_TYPE
WHERE
1 = 1
AND ALI.ALBUM_ID LIKE 'M01211' || '%'
) ALI
LEFT OUTER JOIN voduser.PT_VO_CATEGORY_MAP_UNITED CM ON
CM.VOD_CONTENTS_ID = ALI.ALBUM_ID
LEFT OUTER JOIN voduser.PT_VO_CATEGORY_UNITED C ON
CM.VOD_CATEGORY_ID = C.VOD_CATEGORY_ID
LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_DETAIL PD ON
PD.CONTENTS_ID = ALI.ASSET_ID
LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_UNITED P ON
PD.PRODUCT_ID = P.IMCS_PRODUCT_ID
WHERE
1 = 1
) A
WHERE
RNK = 1
GROUP BY
SERIES_ID_IMCS,
SCREEN_TYPE
) AI
INNER JOIN imcsuser.PT_LA_ALBUM_SUB ALS ON
AI.ALBUM_ID = ALS.ALBUM_ID
INNER JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALP ON
AI.ALBUM_ID = ALP.ALBUM_ID
AND AI.SCREEN_TYPE = ALP.SCREEN_TYPE
LEFT OUTER JOIN imcsuser.PT_CD_CP_MST CP ON
ALP.CP_ID = CP.CP_ID
WHERE
1 = 1

* 튜닝 SQL

SELECT
/* content.base.seriesListPageData /
ROW_NUMBER() OVER (ORDER BY AI.CREATE_DATE DESC, AI.SERIES_ID_IMCS, AI.SCREEN_TYPE ) RNUM,
AI.SERIES_ID_IMCS,
AI.ALB_SER_TYPE,
ALP.ALBUM_ID,
AI.SERIES_NAME,
AI.SCREEN_TYPE,
COALESCE(NULLIF(ALS.GENRE_LARGE, ''), '') AS GENRE_LARGE_NAME,
COALESCE(NULLIF(ALS.GENRE_MID, ''), '') AS GENRE_MID_NAME,
COALESCE(NULLIF(ALS.GENRE_SMALL, ''), '') AS GENRE_SMALL_NAME,
COALESCE(NULLIF(
COALESCE(NULLIF(GENRE_LARGE, ''), '')
|| CASE WHEN NULLIF(GENRE_MID, '') IS NULL THEN '' ELSE '|' || GENRE_MID END
|| CASE WHEN NULLIF(GENRE_SMALL, '') IS NULL THEN '' ELSE '|' || GENRE_SMALL END
,''), '-') AS GENRE,
COALESCE(NULLIF(AI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
TO_CHAR(TO_DATE(AI.CREATE_DATE, 'YYYYMMDD'), 'YYYY-MM-DD' ) AS CREATE_DATE,
AI.SERIES_GROUP_CNT,
COUNT(1) OVER (PARTITION BY AI.SERIES_ID_IMCS) ALBUM_ID_CNT,
COALESCE(REGEXP_REPLACE(NULLIF(ALP.MAXIMUM_VIEWING_LENGTH, ''),'[^0-9]','','g') :: INTEGER / 2400,'0') AS MAXIMUM_VIEWING_DAY,
UFLIX_PROD_YN,
VIEWING_FLAG
FROM
(
SELECT
SERIES_ID_IMCS,
MAX(ALBUM_ID) AS ALBUM_ID,
MAX(ALB_SER_TYPE) AS ALB_SER_TYPE,
SCREEN_TYPE,
COUNT(DISTINCT ALBUM_ID) AS SERIES_GROUP_CNT,
MAX(RELEASE_DATE) AS RELEASE_DATE,
MAX(SERIES_NAME) AS SERIES_NAME,
MAX(SUGGESTED_PRICE) AS SUGGESTED_PRICE,
MAX(CREATE_DATE) AS CREATE_DATE,
MAX(UFLIX_PROD_YN) AS UFLIX_PROD_YN,
MAX(VIEWING_FLAG) AS VIEWING_FLAG
FROM
(
SELECT
ALI.ALB_SER_TYPE,
ALI.ALBUM_ID,
ALI.SERIES_NAME,
ALI.RELEASE_DATE,
ALI.SERIES_ID_IMCS,
ALI.ASSET_ID,
COALESCE(NULLIF(ALI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
ALI.CREATE_DATE,
ALI.SCREEN_TYPE,
CASE
WHEN COALESCE(NULLIF(C.VOD_NSC_GB, ''), 'NNNN') = 'UFX' THEN 'Y'
ELSE 'N'
END UFLIX_PROD_YN,
/* 불필요한 부분
P.IMCS_PRODUCT_NAME,
P.IMCS_EXPIRED_DATE,
P.IMCS_PRICE,
*/
(
CASE
WHEN ALI.SCREEN_TYPE = 'I' THEN (CASE
WHEN ALI.ASSET_TYPE = '3D' THEN '1'
WHEN ALI.ASSET_TYPE = 'HD' THEN '2'
WHEN ALI.ASSET_TYPE = 'SH' THEN '3'
WHEN ALI.ASSET_TYPE = 'SD' THEN '4'
WHEN ALI.ASSET_TYPE = 'U1' THEN '5'
WHEN ALI.ASSET_TYPE = 'U2' THEN '6'
WHEN ALI.ASSET_TYPE = 'PR' THEN '7'
ELSE '8'
END)
ELSE ALI.ASSET_TYPE
END
) AS ASSET_TYPE,
(
CASE
WHEN ALI.SCREEN_TYPE = 'N'
AND C.VOD_CATEGORY_GB = 'NSC' THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
WHEN ALI.SCREEN_TYPE = 'I'
AND C.VOD_CATEGORY_GB IN ('I20', 'I30') THEN COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V')
ELSE ''
END
) AS VIEWING_FLAG,
DENSE_RANK () OVER (PARTITION BY SERIES_ID_IMCS, SCREEN_TYPE ORDER BY ALI.ASSET_TYPE ) RNK
FROM
(
SELECT
(
CASE
WHEN NULLIF(SAM.ALBUM_GROUP_ID,
'') IS NULL THEN 'CON'
ELSE 'S'
END
) AS ALB_SER_TYPE,
(
CASE
WHEN SAM.ALBUM_GROUP_ID IS NULL
OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_ID
ELSE SAM.ALBUM_GROUP_ID
END
) AS SERIES_ID_IMCS,
(
CASE
WHEN SAM.ALBUM_GROUP_ID IS NULL
OR SAM.ALBUM_GROUP_ID = '' THEN ALI.ALBUM_NAME
ELSE SER_MST.ALBUM_GROUP_NM
END
) AS SERIES_NAME,
ALI.ALBUM_ID,
ASI.ASSET_ID,
ALI.RELEASE_DATE,
COALESCE(NULLIF(ASI.SUGGESTED_PRICE, ''), '0') AS SUGGESTED_PRICE,
ASI.CREATE_DATE,
ASI.SCREEN_TYPE,
ASI.ASSET_TYPE
FROM
imcsuser.PT_LA_ALBUM_INFO ALI
INNER JOIN imcsuser.PT_LA_ASSET_INFO ASI ON
ALI.ALBUM_ID = ASI.ALBUM_ID
LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_RELATION SAM ON
ALI.ALBUM_ID = SAM.ALBUM_ID
AND 'S' = SAM.GROUP_TYPE
LEFT OUTER JOIN imcsuser.PT_LA_ALBUM_GROUP SER_MST ON
SAM.ALBUM_GROUP_ID = SER_MST.ALBUM_GROUP_ID
AND SAM.GROUP_TYPE = SER_MST.GROUP_TYPE
WHERE
1 = 1
AND ALI.ALBUM_ID LIKE 'M01211' || '%'
) ALI
LEFT OUTER JOIN voduser.PT_VO_CATEGORY_MAP_UNITED CM ON
CM.VOD_CONTENTS_ID = ALI.ALBUM_ID
LEFT OUTER JOIN voduser.PT_VO_CATEGORY_UNITED C ON
CM.VOD_CATEGORY_ID = C.VOD_CATEGORY_ID
/* 불필요한 부분
LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_DETAIL PD ON
PD.CONTENTS_ID = ALI.ASSET_ID
LEFT OUTER JOIN imcsuser.PT_PD_PACKAGE_UNITED P ON
PD.PRODUCT_ID = P.IMCS_PRODUCT_ID
*/
WHERE
1 = 1
) A
WHERE
RNK = 1
GROUP BY
SERIES_ID_IMCS,
SCREEN_TYPE
) AI
INNER JOIN imcsuser.PT_LA_ALBUM_SUB ALS ON
AI.ALBUM_ID = ALS.ALBUM_ID
INNER JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALP ON
AI.ALBUM_ID = ALP.ALBUM_ID
AND AI.SCREEN_TYPE = ALP.SCREEN_TYPE
LEFT OUTER JOIN imcsuser.PT_CD_CP_MST CP ON
ALP.CP_ID = CP.CP_ID
WHERE
1 = 1

저작자표시 비영리 변경금지 (새창열림)