반응형

/********************************************************************************************
-- Title : [10g] 1 대 多 테이블 실행 계획 비교
-- Key word : execution explain plan 멀티값 싱글값 xmlagg xmlelement
********************************************************************************************/

-- 1:多 설계에서 XMLAGG로 Inline View 사용
-- 51.61 Sec
SELECT CF.*, BB.SM_CLS_CD
FROM KIMSTER.COM_INFO CF
LEFT JOIN
(
    SELECT COM_INFO_CD
         , REPLACE(SUBSTR(SM_CLS_CD, 2), '@', '''') SM_CLS_CD
    FROM
    (
        SELECT COM_INFO_CD
             , XMLAGG(XMLELEMENT(X, ','||SM_CLS_CD||'')
               ORDER BY SM_CLS_CD).EXTRACT('//text()').GETSTRINGVAL() SM_CLS_CD
        FROM KIMSTER.KIMST_SM_CLS
        GROUP BY COM_INFO_CD 
    ) A
) BB
ON CF.COM_INFO_CD = BB.COM_INFO_CD;


-- 1:多 설계에서 SYS_CONNECT_BY_PATH로 Inline View 사용
-- 15.46 Sec
SELECT CF.*, BB.SM_CLS_CD
FROM KIMSTER.COM_INFO CF
LEFT JOIN
(
    SELECT A.COM_INFO_CD
         , MAX(SUBSTR(SYS_CONNECT_BY_PATH(A.SM_CLS_CD, '|'), 2)) AS SM_CLS_CD
    FROM (SELECT ROW_NUMBER() OVER(PARTITION BY COM_INFO_CD
                                   ORDER BY COM_INFO_CD
                                  ) AS RNUM
               , COM_INFO_CD, SM_CLS_CD
          FROM KIMSTER.KIMST_SM_CLS
         ) A
    START WITH A.RNUM = 1
    CONNECT BY PRIOR A.RNUM = A.RNUM - 1 AND PRIOR A.COM_INFO_CD = A.COM_INFO_CD
    GROUP BY A.COM_INFO_CD
) BB
ON CF.COM_INFO_CD = BB.COM_INFO_CD;


-- XMLAGG를 Scholar Subquery로 사용
-- 93.11 Sec
SELECT CF.*
     , (SELECT RTRIM(XMLAGG(XMLFOREST(SM_CLS_CD || ',' SM_CLS_CD) ORDER BY SM_CLS_CD).EXTRACT('//text()'),',')
        FROM KIMST_SM_CLS
        WHERE COM_INFO_CD = CF.COM_INFO_CD
       ) "SM_CLS_CD"
FROM KIMSTER.COM_INFO CF;

 

반응형

+ Recent posts