[완료] /* index.base.getNewRegIndexAlbumList */

원샷원따봉 2022. 8. 29. 14:31

AS-IS

2.5 sec

TO-BE
1 sec

개선 포인트

api에서 set WORK_MEM=102400
CREATE INDEX idx_pt_la_asset_info_05 ON imcsuser.pt_la_asset_info USING btree (album_id,screen_type); 생성으로 1 Sec

* 원본 쿼리

/* index.base.getNewRegIndexAlbumList */
        /*CREATE OR REPLACE FUNCTION pg_temp.FN_GETSTRPOS(STR TEXT)
            RETURNS
            INT AS $RETVAL$
            DECLARE
                RETVAL INT;
            BEGIN
                SELECT STRPOS(STR, (REGEXP_MATCHES(STR,'[^0-9]'))[1]) INTO RETVAL;
            RETURN RETVAL;
            END;
        $RETVAL$ LANGUAGE PLPGSQL;*/

SELECT
TOTAL_CNT
, RNUM
, ALBUM_ID
, ALBUM_NAME
, ASSET_ID
, CASE COALESCE(imcsuser.fnc_bpas_str_pos(REQUEST_SEQ), 0) WHEN 0 THEN REQUEST_SEQ::INTEGER ELSE ASCII(REQUEST_SEQ)-55 END REQUEST_SEQ
, ASSET_FILE_NAME
, TO_CHAR(ASSET_FILE_SIZE) ASSET_FILE_SIZE
, ONAIR_DATE
, RUN_TIME
FROM (
SELECT
COUNT(ALBUM_ID) OVER() AS TOTAL_CNT
, ROW_NUMBER() OVER (ORDER BY ALBUM_DATE DESC) AS RNUM
, ALBUM_ID
, ALBUM_NAME
, ASSET_ID
, SUBSTR(ADI_PRODUCT_ID, 18, 1) REQUEST_SEQ
, ASSET_FILE_NAME
, ASSET_FILE_SIZE
, ONAIR_DATE
, RUN_TIME
FROM (
SELECT DISTINCT
ALBUM_ID
, ALBUM_NAME
, ALBUM_DATE
, FIRST_VALUE(ASSET_ID) OVER w2 ASSET_ID
, FIRST_VALUE(ADI_PRODUCT_ID) OVER w2 ADI_PRODUCT_ID
, FIRST_VALUE(ASSET_FILE_NAME) OVER w2 ASSET_FILE_NAME
, FIRST_VALUE(ASSET_FILE_SIZE) OVER w2 ASSET_FILE_SIZE
, ONAIR_DATE
, RUN_TIME
FROM (
SELECT
ALBUM_ID
, ALBUM_NAME
, ALBUM_DATE
, MIN(ASSET_ID) OVER w1 ASSET_ID
, MIN(ADI_PRODUCT_ID) OVER w1 ADI_PRODUCT_ID
, ASSET_TYPE
, MIN(CONTENT_VALUE) OVER w1 ASSET_FILE_NAME
, MIN(CONTENT_FILESIZE) OVER w1 ASSET_FILE_SIZE
, ONAIR_DATE
, RUN_TIME
FROM (
SELECT
INFO.ALBUM_ID
, INFO.ALBUM_NAME
, INFO.ALBUM_DATE
, ASI.ASSET_ID
, ASI.ADI_PRODUCT_ID
, (CASE ASI.ASSET_TYPE WHEN '3D' THEN '1' WHEN 'HD' THEN '2' WHEN 'SH' THEN '3' WHEN 'SD' THEN '4' WHEN 'U1' THEN '5' WHEN 'U2' THEN '6' WHEN 'PR' THEN '7' ELSE '8' END) ASSET_TYPE
, ASI.CONTENT_VALUE
, ASI.CONTENT_FILESIZE
, INFO.ONAIR_DATE
, SUB.RUN_TIME
FROM
IMCSUSER.PT_LA_ALBUM_INFO INFO
, IMCSUSER.PT_LA_ALBUM_SUB SUB
, IMCSUSER.PT_LA_ALBUM_PLATFORM PLAT
, IMCSUSER.PT_LA_ASSET_INFO ASI
WHERE
INFO.ALBUM_ID = SUB.ALBUM_ID
AND
INFO.ALBUM_ID = PLAT.ALBUM_ID
AND
INFO.ALBUM_ID = ASI.ALBUM_ID
AND
PLAT.SCREEN_TYPE = ASI.SCREEN_TYPE
AND
PLAT.SCREEN_TYPE = 'I'
AND
INFO.ALBUM_ID LIKE 'M%'

AND
INSTR(UPPER(INFO.ALBUM_NAME), UPPER('개발')) > 0

) T1
WINDOW w1 AS (PARTITION BY ALBUM_ID, ALBUM_NAME, ALBUM_DATE, ONAIR_DATE, RUN_TIME, ASSET_TYPE)
) T2
WINDOW w2 AS (PARTITION BY ALBUM_ID, ALBUM_NAME, ALBUM_DATE, ONAIR_DATE, RUN_TIME ORDER BY ASSET_TYPE)
) T3
) T4
WHERE
RNUM BETWEEN 80 + 1 AND 80 + 20

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