[완료]/*abtest.categoryForm.base.getAlbumNameInSeriesAlbum*/

[완료]/abtest.categoryForm.base.getAlbumNameInSeriesAlbum/

원샷원따봉 2022. 8. 29. 12:15

AS-IS
실행 완료. 총 쿼리 실행시간: 12 secs 714 msec.

TO-BE
실행 완료. 총 쿼리 실행시간: 5 secs 405 msec.

개선효과 : 약 59% 개선

-- 세션 튜닝 파라미터

set work_mem=312000

* 튜닝 쿼리

with CATE_MAP as (
SELECT
VIEWING_FLAG,
CONTENTS_ID
  FROM(
SELECT
VOD_VIEWING_FLAG VIEWING_FLAG,
VOD_CONTENTS_ID CONTENTS_ID,
'' AS STAT_FLAG
  FROM
VODUSER.PT_VO_CATEGORY_MAP_UNITED a
where not exists (select  A.VOD_VIEWING_FLAG VIEWING_FLAG,
A.VOD_CONTENTS_ID CONTENTS_ID,
'' AS STAT_FLAG from VODUSER.PT_AB_CATEGORY_MAP B where A.VOD_CATEGORY_ID = B.CATEGORY_ID
AND A.VOD_CONTENTS_ID = B.CONTENTS_ID)
  UNION
  SELECT
VIEWING_FLAG,
CONTENTS_ID,
STAT_FLAG
  FROM
VODUSER.PT_AB_CATEGORY_MAP B
where not exists (select  A.VOD_VIEWING_FLAG VIEWING_FLAG,
A.VOD_CONTENTS_ID CONTENTS_ID,
'' AS STAT_FLAG from VODUSER.PT_VO_CATEGORY_MAP_UNITED A where A.VOD_CATEGORY_ID = B.CATEGORY_ID
AND A.VOD_CONTENTS_ID = B.CONTENTS_ID)


) T

→ 기존 Union 문에 EXCEPT 부분을 not exists 로 처리 및 anti join 으로 유도 하였다.

* 원본 쿼리

/*abtest.categoryForm.base.getAlbumNameInSeriesAlbum*/
WITH CATE_MAP AS (
  SELECT
    VIEWING_FLAG,
    CONTENTS_ID
  FROM(
      SELECT
        VOD_VIEWING_FLAG VIEWING_FLAG,
        VOD_CONTENTS_ID CONTENTS_ID,
        '' AS STAT_FLAG
      FROM
        VODUSER.PT_VO_CATEGORY_MAP_UNITED
      UNION
      SELECT
        VIEWING_FLAG,
        CONTENTS_ID,
        STAT_FLAG
      FROM
        VODUSER.PT_AB_CATEGORY_MAP
      EXCEPT
      SELECT
        A.VOD_VIEWING_FLAG VIEWING_FLAG,
        A.VOD_CONTENTS_ID CONTENTS_ID,
        '' AS STAT_FLAG
      FROM
        VODUSER.PT_VO_CATEGORY_MAP_UNITED A,
        VODUSER.PT_AB_CATEGORY_MAP B
      WHERE
        A.VOD_CATEGORY_ID = B.CATEGORY_ID
        AND A.VOD_CONTENTS_ID = B.CONTENTS_ID
    ) T
  WHERE
    COALESCE(NULLIF(STAT_FLAG, ''), 'U') <> 'D'
)
SELECT
  AI.ALBUM_ID,
  AI.ALBUM_NAME,
  AI.PRE_MAPPING_YN,
  SAM.ORDER_NO,
  ALP.TERR_YN,
  ALP.TERR_PERIOD,
  TO_DATE(
    COALESCE(NULLIF(ALP.LICENSING_WINDOW_END, ''), '29991231'),
    'YYYYMMDD'
  ) END_DATE,
  ALP.TERR_ST_DATE,
  ALP.TERR_ED_DATE,
  COALESCE(NULLIF(MAX(AUB.RATING_CD), ''), '01') AS RATING_CD,
  COALESCE(
    NULLIF(
      MAX(
        CASE
          WHEN COALESCE(ALP.LICENSING_WINDOW_START, '19700101') <= TO_CHAR(CLOCK_TIMESTAMP(), 'YYYYMMDD')
          AND COALESCE(ALP.LICENSING_WINDOW_END, '29991231') >= TO_CHAR(CLOCK_TIMESTAMP(), 'YYYYMMDD') THEN 'Y'
          ELSE 'N'
        END
      ),
      ''
    ),
    'N'
  ) AS SERVICE_FLAG,
  COALESCE(NULLIF(MAX(AUB.LAST_SERIES_YN), ''), 'N') AS LAST_SERIES_YN,
  COALESCE(NULLIF(MAX(AI.HIGH_QUALITY_TYPE), ''), 'N') AS HIGH_QUALITY_TYPE,
  MAX(COALESCE(NULLIF(MAP.VIEWING_FLAG, ''), 'V')) AS VIEWING_FLAG,
  MAX(AUDIO_TYPE) AS AUDIO_TYPE
FROM
  imcsuser.PT_LA_ALBUM_INFO AI
  INNER JOIN imcsuser.PT_LA_ALBUM_RELATION SAM ON AI.ALBUM_ID = SAM.ALBUM_ID
  INNER JOIN imcsuser.PT_LA_ALBUM_PLATFORM ALP ON AI.ALBUM_ID = ALP.ALBUM_ID
  INNER JOIN imcsuser.PT_LA_ALBUM_SUB AUB ON AI.ALBUM_ID = AUB.ALBUM_ID
  INNER JOIN imcsuser.PT_LA_ASSET_INFO AST ON AI.ALBUM_ID = AST.ALBUM_ID
  LEFT JOIN CATE_MAP MAP ON MAP.CONTENTS_ID = AI.ALBUM_ID
WHERE
  SAM.ALBUM_GROUP_ID = '0154254501'
  AND SAM.GROUP_TYPE = 'S'
  AND ALP.SCREEN_TYPE = 'I'
GROUP BY
  AI.ALBUM_ID,
  AI.ALBUM_NAME,
  AI.PRE_MAPPING_YN,
  SAM.ORDER_NO,
  ALP.TERR_YN,
  ALP.TERR_PERIOD,
  ALP.TERR_ST_DATE,
  ALP.TERR_ED_DATE,
  ALP.LICENSING_WINDOW_END
ORDER BY
  SAM.ORDER_NO DESC

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