안녕하세요 항상 많은 정보를 보고 배워가는 개발자 입니다.

제가 이번에 로직을 구현하면서 메인 기준으로 나머지 거래자료를 outer join하여 전체 고객에 대한 정보를 보려 합니다.

data 양이 그렇게 많지는 않은거 같은데 시간이 좀 걸리네요

기존에 작성한 로직을 간단히 수정해서 올려봅니다.

WITH CALENDAR AS (                   
SELECT TO_CHAR(TO_DATE('19001231', 'YYYYMMDD') + ROWNUM, 'YYYYMMDD') YMD
  FROM DICT                                                                                  
 WHERE ROWNUM <= 365 * 200                                                                                  
   AND TO_CHAR(TO_DATE('19001231', 'YYYYMMDD') + ROWNUM, 'YYYYMMDD') <= TO_CHAR(TRUNC(TO_DATE(:BASE_DT,'YYYYMMDD'),'MM')-1,'YYYYMMDD')
)
SELECT /*+ PARALLEL(A2 4) USE_CONCAT(A1) USE)HASH(A1 B1 C1) NO_USE_HASH_AGGREGATION */          
       TO_CHAR(TRUNC(TO_DATE(:BASE_DT,'YYYYMMDD'),'MM')-1,'YYYYMM')        AS BAS_YM                         
     , A1.MAIN_GROUP                                                       AS MAIN_GROUP           
     , A1.PERSON_NO                                                        AS PERSON_NO                 
     , MAX(CASE WHEN A1.CRP_SEC_CD = '2'                                                                     
                 AND A1.PSNL_CRP_SEC_CD = '2'            THEN 'Y'                                            
            END)                                                           AS UN_PROFIT_GRP_YN             
     , MAX(CASE WHEN D1.YMD IS NOT NULL                                                                      
                 AND A1.PSNL_CRP_SEC_CD = '1'                                                                
                 AND ((TRUNC(TO_DATE(:BASE_DT,'YYYYMMDD'),'MM')-1) - TO_DATE(A1.BR_D,'YYYYMMDD'))            
                     / 365 < 18                          THEN 'Y'                                            
            END)                                                           AS UAMAN_YN                       
     , MAX(CASE WHEN C1.RL_ID = 'A01'              THEN 'Y'                                            
            END)                                                           AS CUST_CNT1
     , MAX(CASE WHEN B1.CSH_AM > 0                       THEN 'Y'                                            
            END)                                                           AS CSH_CUST_YN                  
  FROM 기본 main                     A1       (2천만건)                                                               
     , 서브 tab1                     B1       (1천5백만건)
     , 서브 tab2                     C1       (1천만건)
     , CALENDAR                      D1                                                                      
 WHERE A1.MAIN_GROUP = B1.MAIN_GROUP(+)                                                        
   AND A1.PERSON_NO    = B1.PERSON_NO(+)                                                            
   AND A1.MAIN_GROUP = C1.MAIN_GROUP(+)                                                            
   AND A1.PERSON_NO    = C1.PERSON_NO(+)                                                                   
   AND A1.BRDD                 = D1.YMD(+)                                                                  
   AND NVL(A1.가입일,'0')    <= '전월말일'
   AND B1.거래일(+)            >= '전월초일'
   AND B1.거래일(+)            <= '전월말일'
   AND C1.주문코드(+)            IN ('A01')   
   AND C1.거래일(+)            >= '전월초일'
   AND C1.거래일(+)            <= '전월말일'
 GROUP BY A1.MAIN_GROUP
        , A1.PERSON_NO

미성년자 여부를 체크하기 위해 with절에 날짜를 구하여 비교를 하였고 메인을 기준으로 한번 읽어서 전체 고객기준으로 자료를 넣으려 합니다.

오렌지에서 뜬 플랜에서 cost가 나쁜 방향을 잡아서 속도를 개선해보려 하는데 어떻게 봐야 하는지

또 어떻게 튜닝을 해야 하는지 감이 안잡히네요

고수님들의 조언 부탁드립니다.