설명하신 내용으로 봐서는 쿼리가 크게 개선되기는 힘들 것으로 보입니다.

아래와 같은 쿼리로도 동일한 결과를 얻어 올 수 있습니다.

참고만 하세요.

SELECT DISTINCT A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS
       ,L.LLLL, M.MMMM, S.SSSS
FROM BASE A
LEFT JOIN SUB L ON A.PPPP = L.PPPP AND A.RRRR = L.RRRR AND A.LLLL = L.LLLL
LEFT JOIN SUB M ON A.PPPP = M.PPPP AND A.RRRR = M.RRRR AND A.LLLL = M.LLLL AND A.MMMM = M.MMMM
LEFT JOIN SUB S ON A.PPPP = S.PPPP AND A.RRRR = S.RRRR AND A.LLLL = S.LLLL AND A.MMMM = S.MMMM AND A.SSSS = S.SSSS
ORDER BY A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS 

 감사합니다.

 다음과 같이 해결하였더니 속도 개선이 되었습니다.

 

 

(

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'LK' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_FNS' AS MMMM, 'CEL' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'SU3' AS SSSS FROM DUAL  UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'ASC' AS LLLL, 'ESD' AS MMMM, 'FFA' AS SSSS FROM DUAL  

 

 ),

 SUB AS

(

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'CU_E' AS MMMM, 'SAB' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'FOUP' AS MMMM, 'UP' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'CU' AS LLLL, 'CU_F' AS MMMM, 'NS' AS SSSS FROM DUAL UNION ALL

 

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSM' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'FNM' AS MMMM, 'NSMD' AS SSSS FROM DUAL UNION ALL

 

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSD' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSE' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'SIN' AS LLLL, 'S_HS' AS MMMM, 'NSG' AS SSSS FROM DUAL UNION ALL

 SELECT 'C' AS PPPP, 'CLE' AS RRRR, 'DES' AS LLLL, 'S_HS' AS MMMM, 'NSG' AS SSSS FROM DUAL 

 )

 SELECT PPPP, RRRR, LLLL, MMMM, SSSS

         ,MAX(LLLLLLLLLLLLLLLL)

         ,MAX(MMMMMMMMMMMMMM)

         ,MAX(SSSSSSSSSSSSSSSS)

 FROM

 (

 SELECT A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

      --     ,B.LLLL, B.MMMM, B.SSSS

           ,(CASE WHEN A.LLLL = B.LLLL THEN B.LLLL

                     ELSE '' END) AS LLLLLLLLLLLLLLLL

           ,(CASE WHEN A.LLLL = B.LLLL AND A.MMMM = B.MMMM  THEN B.MMMM

                     ELSE '' END) AS MMMMMMMMMMMMMM

           ,(CASE WHEN A.LLLL = B.LLLL  AND A.MMMM = B.MMMM 

                     AND A.SSSS = B.SSSS THEN B.SSSS

                     ELSE '' END) AS SSSSSSSSSSSSSSSS

 FROM BASE A

          FULL OUTER JOIN SUB B

          ON A.PPPP = B.PPPP AND A.RRRR = B.RRRR

          AND A.LLLL = B.LLLL

    WHERE A.PPPP IS NOT NULL

  --  GROUP BY A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

   --ORDER BY A.PPPP, A.RRRR, A.LLLL, A.MMMM, A.SSSS

   )

   GROUP BY PPPP, RRRR, LLLL, MMMM, SSSS