[완료]/* abtest.content.base.popupAbtestFormRouteListPageDataSeries */

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

* 개선 포인트

AS-IS 수행 시간 : 10초 내외
TO-BE 수행 시간 : -
분석 결과 : 2개의 recursive 쿼리 중 하단에 있는 recursive 를 제외하고 수행할 경우 0.2초 내외로 수행됨.
하단 recursive 영역에 대한 변경이 필요
하단 recursive 쿼리는 아래와 같다.

원본 쿼리

SELECT
/* abtest.content.base.popupAbtestFormRouteListPageDataSeries /
BB.RNUM,
BB.TEST_ID,
BB.VARIATION_ID,
BB.CONTENTS_ID,
BB.CONTENTS_NAME,
BB.CATEGORY_NAME,
BB.CATEGORY_ID,
BB.PARENT_CATEGORY_ID,
BB.CREATE_DATE,
BB.CATEGORY_GB,
BB.VIEWING_FLAG,
BB.FORM_ROUTE,
BB.PKG_YN,
BB.SERIES_YN,
BB.CLOSE_YN,
BB.PPS_ONLY_FLAG,
BB.NSC_GB,
BB.IS_HD,
BB.SERIES_ID,
BB.TEST_SBC,
BB.PR_INFO
FROM(
SELECT
ROW_NUMBER() OVER (
ORDER BY
(
CASE
WHEN (
(
AA.CATEGORY_GB = 'NSC'
OR AA.CATEGORY_GB = 'I20'
)
AND AA.NSC_GB = 'UFX'
) THEN 4
ELSE CASE
WHEN AA.CATEGORY_GB = 'NSC' THEN 3
WHEN AA.CATEGORY_GB = 'I30' THEN 2
WHEN AA.CATEGORY_GB = 'I20' THEN 1
END
END
) ASC,
AA.CATEGORY_ID ASC
) RNUM,
AA.TEST_ID,
AA.VARIATION_ID,
AA.CONTENTS_ID,
AA.CONTENTS_NAME,
AA.CATEGORY_NAME,
AA.CATEGORY_ID,
AA.PARENT_CATEGORY_ID,
AA.CREATE_DATE,
AA.CATEGORY_GB,
AA.VIEWING_FLAG,
AA.FORM_ROUTE,
AA.PKG_YN,
AA.SERIES_YN,
AA.CLOSE_YN,
AA.PPS_ONLY_FLAG,
AA.NSC_GB,
AA.IS_HD,
AA.SERIES_ID,
AA.TEST_SBC,
AA.PR_INFO
FROM(
SELECT
'' AS TEST_ID,
'' AS VARIATION_ID,
CONTENTS_ID,
CONTENTS_NAME,
CATEGORY_NAME,
CATEGORY_ID,
PARENT_CATEGORY_ID,
CREATE_DATE,
CATEGORY_GB,
VIEWING_FLAG,
FORM_ROUTE,
PKG_YN,
SERIES_YN,
CLOSE_YN,
PPS_ONLY_FLAG,
NSC_GB,
IS_HD,
SERIES_ID,
TEST_SBC,
PR_INFO
FROM(
SELECT
CONTENTS_ID,
MAX(CONTENTS_NAME) AS CONTENTS_NAME,
MAX(CATEGORY_NAME) AS CATEGORY_NAME,
CATEGORY_ID,
MAX(PARENT_CATEGORY_ID) AS PARENT_CATEGORY_ID,
MAX(CREATE_DATE) AS CREATE_DATE,
MAX(CATEGORY_GB) AS CATEGORY_GB,
MAX(VIEWING_FLAG) AS VIEWING_FLAG,
MAX(FORM_ROUTE) AS FORM_ROUTE,
PKG_YN,
SERIES_YN,
CLOSE_YN,
PPS_ONLY_FLAG,
NSC_GB,
IS_HD,
SERIES_ID,
TEST_SBC,
PR_INFO
FROM
(
SELECT
CASE
WHEN COALESCE(NULLIF(CT.VOD_SERIES_ID, ''), '') = '' THEN CM.VOD_CONTENTS_ID
ELSE CT.VOD_SERIES_ID
END CONTENTS_ID,
CASE
WHEN COALESCE(NULLIF(CT.VOD_SERIES_ID, ''), '') = '' THEN CM.VOD_CONTENTS_NAME
ELSE CT.VOD_CATEGORY_NAME
END CONTENTS_NAME,
CT.VOD_CATEGORY_NAME AS CATEGORY_NAME,
CT.VOD_CATEGORY_ID AS CATEGORY_ID,
CT.VOD_PARENT_CATEGORY_ID AS PARENT_CATEGORY_ID,
TO_CHAR(
TO_DATE(CT.VOD_CREATE_DATE, 'YYYYMMDDHH24MISS'),
'YYYYMMDD'
) AS CREATE_DATE,
CT.VOD_CATEGORY_GB AS CATEGORY_GB,
COALESCE(NULLIF(CM.VOD_VIEWING_FLAG, ''), 'V') AS VIEWING_FLAG,
CT.VOD_PKG_YN AS PKG_YN,
CT.VOD_SERIES_YN AS SERIES_YN,
CT.VOD_CLOSE_YN AS CLOSE_YN,
CT.VOD_PPS_ONLY_FLAG AS PPS_ONLY_FLAG,
CT.VOD_NSC_GB AS NSC_GB,
CT.VOD_IS_HD IS_HD,
CT.VOD_SERIES_ID AS SERIES_ID,
COALESCE(NULLIF(CT.VOD_TEST_SBC, ''), 'Y') AS TEST_SBC,
COALESCE(NULLIF(CT.VOD_PR_INFO, ''), '01') AS PR_INFO,
(
WITH RECURSIVE CTE_CONNECT_BY AS (
SELECT
1 AS LEVEL,
S.*
FROM
voduser.PT_VO_CATEGORY_UNITED S
WHERE
VOD_CATEGORY_ID = CT.VOD_CATEGORY_ID
UNION ALL
SELECT
LEVEL + 1 AS LEVEL,
S.*
FROM
CTE_CONNECT_BY R
INNER JOIN voduser.PT_VO_CATEGORY_UNITED S ON R.VOD_PARENT_CATEGORY_ID = S.VOD_CATEGORY_ID
)
SELECT
REPLACE(
REPLACE(
TRANSLATE(
ARRAY_TO_STRING(
ARRAY_AGG(
VOD_CATEGORY_NAME || '(' || VOD_CATEGORY_ID || ')'
ORDER BY
VOD_CATEGORY_LEVEL DESC
),
';'
),
';',
'|'
),
CHR(60),
CHR(38) || 'lt;'
),
CHR(62),
CHR(38) || 'gt;'
)
FROM
CTE_CONNECT_BY
) AS FORM_ROUTE
FROM
voduser.PT_VO_CATEGORY_UNITED CT,
voduser.PT_VO_CATEGORY_MAP_UNITED CM
WHERE
CT.VOD_CATEGORY_ID = CM.VOD_CATEGORY_ID
AND EXISTS (
SELECT
'X'
FROM
imcsuser.PT_LA_ALBUM_RELATION A
WHERE
A.GROUP_TYPE = 'S'
AND A.ALBUM_GROUP_ID = '0154254501'
AND A.ALBUM_ID = CM.VOD_CONTENTS_ID
LIMIT
1
)
) R
GROUP BY
CONTENTS_ID,
CATEGORY_ID,
PKG_YN,
SERIES_YN,
CLOSE_YN,
PPS_ONLY_FLAG,
NSC_GB,
IS_HD,
SERIES_ID,
TEST_SBC,
PR_INFO
) T
UNION
SELECT
TEST_ID,
VARIATION_ID,
CONTENTS_ID,
CONTENTS_NAME,
CATEGORY_NAME,
CATEGORY_ID,
PARENT_CATEGORY_ID,
CREATE_DATE,
CATEGORY_GB,
VIEWING_FLAG,
FORM_ROUTE,
PKG_YN,
SERIES_YN,
CLOSE_YN,
PPS_ONLY_FLAG,
NSC_GB,
IS_HD,
SERIES_ID,
TEST_SBC,
PR_INFO
FROM(
SELECT
TEST_ID,
VARIATION_ID,
CONTENTS_ID,
MAX(CONTENTS_NAME) AS CONTENTS_NAME,
MAX(CATEGORY_NAME) AS CATEGORY_NAME,
CATEGORY_ID,
MAX(PARENT_CATEGORY_ID) AS PARENT_CATEGORY_ID,
MAX(CREATE_DATE) AS CREATE_DATE,
MAX(CATEGORY_GB) AS CATEGORY_GB,
MAX(VIEWING_FLAG) AS VIEWING_FLAG,
MAX(FORM_ROUTE) AS FORM_ROUTE,
PKG_YN,
SERIES_YN,
CLOSE_YN,
PPS_ONLY_FLAG,
NSC_GB,
IS_HD,
SERIES_ID,
TEST_SBC,
PR_INFO
FROM
(
SELECT
CT.TEST_ID,
CT.VARIATION_ID,
CASE
WHEN COALESCE(NULLIF(CT.SERIES_ID, ''), '') = '' THEN CM.CONTENTS_ID
ELSE CT.SERIES_ID
END CONTENTS_ID,
CASE
WHEN COALESCE(NULLIF(CT.SERIES_ID, ''), '') = '' THEN CM.CONTENTS_NAME
ELSE CT.CATEGORY_NAME
END CONTENTS_NAME,
CT.CATEGORY_NAME AS CATEGORY_NAME,
CT.CATEGORY_ID AS CATEGORY_ID,
CT.PARENT_CATEGORY_ID AS PARENT_CATEGORY_ID,
TO_CHAR(
TO_DATE(CT.CREATE_DATE, 'YYYYMMDDHH24MISS'),
'YYYYMMDD'
) AS CREATE_DATE,
CT.CATEGORY_GB AS CATEGORY_GB,
COALESCE(NULLIF(CM.VIEWING_FLAG, ''), 'V') AS VIEWING_FLAG,
CT.PKG_YN AS PKG_YN,
CT.SERIES_YN AS SERIES_YN,
CT.CLOSE_YN AS CLOSE_YN,
CT.PPS_ONLY_FLAG AS PPS_ONLY_FLAG,
CT.NSC_GB AS NSC_GB,
CT.IS_HD,
CT.SERIES_ID AS SERIES_ID,
COALESCE(NULLIF(CT.TEST_SBC, ''), 'Y') AS TEST_SBC,
COALESCE(NULLIF(CT.PR_INFO, ''), '01') AS PR_INFO,
(
SELECT
REPLACE(
REPLACE(
TRANSLATE(
ARRAY_TO_STRING(
ARRAY_AGG(
CATEGORY_NAME || '(' || CATEGORY_ID || ')'
ORDER BY
CATEGORY_LEVEL DESC
),
';'
),
';',
'|'
),
CHR(60),
CHR(38) || 'lt;'
),
CHR(62),
CHR(38) || 'gt;'
)
FROM
(
WITH RECURSIVE TMP_LIST AS (
SELECT
DISTINCT TEST_ID,
VARIATION_ID,
CATEGORY_ID,
CATEGORY_NAME,
CATEGORY_LEVEL,
PARENT_CATEGORY_ID
FROM(
SELECT
'' AS TEST_ID,
'' AS VARIATION_ID,
VOD_CATEGORY_ID CATEGORY_ID,
VOD_CATEGORY_NAME CATEGORY_NAME,
VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
'' AS STAT_FLAG
FROM
voduser.PT_VO_CATEGORY_UNITED
UNION
SELECT
TEST_ID,
VARIATION_ID,
CATEGORY_ID,
CATEGORY_NAME,
CATEGORY_LEVEL,
PARENT_CATEGORY_ID,
STAT_FLAG
FROM
VODUSER.PT_AB_CATEGORY
EXCEPT
SELECT
'' AS TEST_ID,
'' AS VARIATION_ID,
A.VOD_CATEGORY_ID CATEGORY_ID,
A.VOD_CATEGORY_NAME CATEGORY_NAME,
A.VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
A.VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
'' AS STAT_FLAG
FROM
voduser.PT_VO_CATEGORY_UNITED A,
VODUSER.PT_AB_CATEGORY B
WHERE
A.VOD_CATEGORY_ID = B.CATEGORY_ID
AND B.TEST_ID = CT.TEST_ID
AND B.VARIATION_ID = CT.VARIATION_ID
) T
WHERE
COALESCE(NULLIF(STAT_FLAG, ''), 'U') <> 'D'
),
CATEGORY_LIST AS(
SELECT
1 AS LEVEL,
T.*
FROM
TMP_LIST T
WHERE
CATEGORY_ID = CT.CATEGORY_ID
AND TEST_ID = CT.TEST_ID
AND VARIATION_ID = CT.VARIATION_ID
UNION ALL
SELECT
LEVEL + 1 AS LEVEL,
T.*
FROM
CATEGORY_LIST R
INNER JOIN TMP_LIST T ON R.PARENT_CATEGORY_ID = T.CATEGORY_ID
AND CT.TEST_ID = COALESCE(NULLIF(T.TEST_ID, ''), CT.TEST_ID)
AND CT.VARIATION_ID = COALESCE(NULLIF(T.VARIATION_ID, ''), CT.VARIATION_ID)
)
SELECT
CATEGORY_ID,
CATEGORY_NAME,
CATEGORY_LEVEL
FROM
CATEGORY_LIST
) T
) AS FORM_ROUTE
FROM
VODUSER.PT_AB_CATEGORY CT,
VODUSER.PT_AB_CATEGORY_MAP CM
WHERE
CT.CATEGORY_ID = CM.CATEGORY_ID
AND CT.TEST_ID = CM.TEST_ID
AND CT.VARIATION_ID = CM.VARIATION_ID
AND EXISTS (
SELECT
'X'
FROM
imcsuser.PT_LA_ALBUM_RELATION A
WHERE
A.GROUP_TYPE = 'S'
AND A.ALBUM_GROUP_ID = '0154254501'
AND A.ALBUM_ID = CM.CONTENTS_ID
LIMIT
1
)
AND CT.STAT_FLAG <> 'D'
AND CM.STAT_FLAG <> 'D'
) R
GROUP BY
TEST_ID,
VARIATION_ID,
CONTENTS_ID,
CATEGORY_ID,
PKG_YN,
SERIES_YN,
CLOSE_YN,
PPS_ONLY_FLAG,
NSC_GB,
IS_HD,
SERIES_ID,
TEST_SBC,
PR_INFO
) T
) AA
) BB
ORDER BY
TO_NUMBER(BB.RNUM);

* 튜닝 쿼리

(
select
replace(
replace(
translate(ARRAY_TO_STRING(ARRAY_AGG(CATEGORY_NAME || '(' || CATEGORY_ID || ')'order by CATEGORY_LEVEL desc),';'),';','|'),
CHR(60),CHR(38) || 'lt;'),
CHR(62),CHR(38) || 'gt;')
from
(
with recursive TMP_LIST as (
select
distinct TEST_ID,
VARIATION_ID,
CATEGORY_ID,
CATEGORY_NAME,
CATEGORY_LEVEL,
PARENT_CATEGORY_ID
from
(
select
'' as TEST_ID,
'' as VARIATION_ID,
VOD_CATEGORY_ID CATEGORY_ID,
VOD_CATEGORY_NAME CATEGORY_NAME,
VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
'' as STAT_FLAG
from
voduser.PT_VO_CATEGORY_UNITED
union
select
TEST_ID,
VARIATION_ID,
CATEGORY_ID,
CATEGORY_NAME,
CATEGORY_LEVEL,
PARENT_CATEGORY_ID,
STAT_FLAG
from
VODUSER.PT_AB_CATEGORY
except
select
'' as TEST_ID,
'' as VARIATION_ID,
A.VOD_CATEGORY_ID CATEGORY_ID,
A.VOD_CATEGORY_NAME CATEGORY_NAME,
A.VOD_CATEGORY_LEVEL CATEGORY_LEVEL,
A.VOD_PARENT_CATEGORY_ID PARENT_CATEGORY_ID,
'' as STAT_FLAG
from
voduser.PT_VO_CATEGORY_UNITED A,
VODUSER.PT_AB_CATEGORY B
where
A.VOD_CATEGORY_ID = B.CATEGORY_ID
and B.TEST_ID = CT.TEST_ID
and B.VARIATION_ID = CT.VARIATION_ID
) T
where
coalesce(nullif(STAT_FLAG, ''), 'U') <> 'D'),
CATEGORY_LIST as(
select
1 as level,
T.*
from
TMP_LIST T
where
CATEGORY_ID = CT.CATEGORY_ID
and TEST_ID = CT.TEST_ID
and VARIATION_ID = CT.VARIATION_ID
union all
select
level + 1 as level,
T.*
from
CATEGORY_LIST R
inner join TMP_LIST T on
R.PARENT_CATEGORY_ID = T.CATEGORY_ID
and CT.TEST_ID = coalesce(nullif(T.TEST_ID, ''), CT.TEST_ID)
and CT.VARIATION_ID = coalesce(nullif(T.VARIATION_ID, ''), CT.VARIATION_ID)
)
select
CATEGORY_ID,
CATEGORY_NAME,
CATEGORY_LEVEL
from
CATEGORY_LIST
) T
) as FORM_ROUTE

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