JOIN과 SUBQUERY 가 동일한 예제

조건.
    열 이름         널?      유형
    --------------- -------- ----
    SITE_CD         NOT NULL VARCHAR2(6)  -- 사업장
    YYMM_YM         NOT NULL VARCHAR2(6)  -- 년월
    BUDGET_CD       NOT NULL VARCHAR2(8)  -- 항목
    DEPT_CD         NOT NULL VARCHAR2(6)  -- 부서
    DRIVER_QN                NUMBER       -- 배부값
    DRIVER_CD       NOT NULL VARCHAR2(6)  -- 배부기준
    DIV_RT                   NUMBER       -- 배부율

    PRIMARY KEY는 TABLE DESCRIPTION 상에 나타나는 순서대로
    SITE_CD 부터 DEPT_CD 까지이다.

    COMCOSTTARGET 와 COMCOSTTARGETB 라는 위와 같이 동일한
    구조를 가진 TABLE 둘이 있다.
    이하에서는 편의상 COMCOSTTARGET 를 1번 으로
    COMCOSTTARGETB를 2번 으로 칭하겠다.

문제.
    1번에서 읽은 DATA를 2번으로 INSERT 시키는것이 목표이다.
    1번에 FROM/TO기간과  사업장을 입력조건으로 주어준다.
    해당기간에 있는 배부값을 SUM하여 '1999Q1' 과 같은 특정 기간명으로
    2번 에 INSERT 한다.
    이하 입력시 년월과 사업장은 동일하므로 제외하고 이야기 하겠다.
    이때 항목과 부서까지가 PRIMARY KEY 이므로 주어진 기간에 존재하는
    항목과 부서는 모두 이관 시키다.
    문제가 되는 가장 큰 부분은 해당기간중 동일한 항목/부서에서
    서로다른 배부기준을 사용할 수 있다는 것이다.
    배부기준은 KEY가 아니므로 동일항목 부서에 다른 배부기준이 존재한다면
    INSERT 시점에서 DUPLICTION ERROR가 발생할 것이다.
    이것을 방지하기 위해 동일 항목과 부서에서 해당기간중에 다른 배부기준을
    사용한 경우가 있으면 가장 최근월에 사용한 배부기준과 그값만을 SUM하고
    동일항목과 부서에서 사용한 과거의 배부기준과 부서는 무시한다.
    쉽게 얘기해서 항목/부서(KEY)별로 배부기준이 월에따라 다른경우가 있으면
    최근월의 배부기준만을 사용하고 그 이전것은 무시한다.
    위의 제약을 빼고 단순히 얘기하면 1번에서 주어진 기간내의 DATA를 SUM해서
    2번에 특정기간으로 INSERT 하는 것이다.
    위의 TABLE에는 PRIMARY KEY 외에
    BUDGET_CD       NOT NULL VARCHAR2(8)  -- 항목
    DEPT_CD         NOT NULL VARCHAR2(6)  -- 부서
    두개의 COLUMN에 대해서도 INDEX가 존재한다.
    INSERT INTO TABLE SELECT 어쩌구저쩌구 .....
    와 같이 하나의 문장으로 모든것을 해결하고자 한다.

생각.
    위의 문제를 해결하기 위해 INLINE VIEW를 이용한 JOIN과
    SUB-QUERY 이용하는 두가지를 비교 해보고자 만들어진 문제다.
    SUB-QUERY와 JOIN의 특성및 차이점에 대해서 상세하게 설명하고 있는
    좋은 책이 많으므로 그에 대해서는 깊게 언급하지 않겠다.
    지금 경우는 JOIN과 SUB-QUERY 가 동일하게 작용하는 경우라서
    그 실제예를 통해 이해를 돕고자 하는데 목적이 있다.

해법.    
   단계1.먼저 INLINE VIEW를 생각해 보자.
         QUERY를 구성하는 INLINE VIEW는 주어진 년월과 사업장에
         포함되는 DATA중 항목과 부서와 배부기준을 읽어오는데
         배부기준이 KEY를 구성하지 않으므로 동일 항목과 배부기준중
         최근월에서 사용한 배부기준만을 가져온다.
         그렇게 하면 주어진 사업장에서 기간 내에 존재하는 유일한
         항목/부서로 하나의 배부기준만이 존재하게 된다.
         다음과 같이 구성해 보자.

         SELECT BUDGET_CD,DEPT_CD,
                SUBSTR(MAX(YYMM_YM||DRIVER_CD),7) DRIVER_CD
         FROM   COMCOSTTARGET
         WHERE  YYMM_YM BETWEEN '199901' AND '199903'
         AND    SITE_CD = '서울'
         GROUP BY BUDGET_CD,DEPT_CD

         항목과 부서로 GROUP BY 했기때문에 KEY의 유일성이 일단 보장된다.
         년월과 배부기준을 CONCATENATE 한 MAX값은 년월별로
         다른 배부기준이 사용되었을 경우는 최근월의 DATA를
         RETURN 한다.
         그 중 배부기준만을 사용하기위해 년월을 잘라냈다.
         해당기간중에 같은 배부기준을 썼다해도
         당연히 문제될것은 없다.
    단계2.이제 위의 VIEW를 1번 TABLE과 JOIN을 걸어보자.
         이때 OUTER JOIN이 아닌 이상은 INLINE VIEW안에 존재하는
         배부기준을 사용하는 DATA만이 끌려 나올 것이다.

         INSERT INTO COMCOSTTARGETB                           (YYMM_YM,SITE_CD,BUDGET_CD,DEPT_CD,
                DRIVER_CD,DRIVER_QN,DIV_RT)
         SELECT '1999Q1','서울',A.BUDGET_CD,A.DEPT_CD,
                MAX(A.DRIVER_CD),
                SUM(A.DRIVER_QN),
                NULL
         FROM   COMCOSTTARGET A,
               (SELECT BUDGET_CD,DEPT_CD,
                       SUBSTR(MAX(YYMM_YM||DRIVER_CD),7) DRIVER_CD
                FROM   COMCOSTTARGET
                WHERE  YYMM_YM BETWEEN '199901' AND '199903'
                AND    SITE_CD = '서울'
                GROUP BY BUDGET_CD,DEPT_CD) B
         WHERE  A.YYMM_YM BETWEEN '199901' AND '199903'
         AND    A.SITE_CD   = '서울'
         AND    A.BUDGET_CD = B.BUDGET_CD
         AND    A.DEPT_CD   = B.DEPT_CD
         AND    A.DRIVER_CD = B.DRIVER_CD
         GROUP BY
                '1999Q1','서울',A.BUDGET_CD,A.DEPT_CD
    
         위의 QUERY에서 배부기준에 MAX 함수를 사용한 이유는 KEY인
         부서까지 만을
         GROUP BY 에 사용하기 위해서 이다.
         그러나 배부기준까지를 GROUP BY 에 사용하더라도 문제 될것은 없다.
         위의 경우 PLAN을 이용해 접근경로를 살펴보면 다음과 같다.

         SELECT STATEMENTCost Estimate:                                                  
           SORTGROUP BY                                                                  
             NESTED LOOPS                                                                
               VIEW(2)                                                                  
                 SORTGROUP BY                                                            
                   TABLE ACCESSBY ROWID:HABC,,,,,,,,,,,,,,,HCDT_COMCOSTTARGET(3)        
                     INDEXRANGE SCAN:HABC,,,,,,,,,,SYS_C00100252 (U)                    
               TABLE ACCESSBY ROWID:HABC,,,,,,,,,,,,,,,HCDT_COMCOSTTARGET(1)            
                 INDEXRANGE SCAN:HABC,,,,,,,,,,,,,,,,HCDI1_COMCOSTTARGET                
    단계3.JOIN과 SUBQUERY의 차이점중 하나가
          SELECT문에서 해당 COLUMN을 이용할 수 있느냐  없느냐이다.
          다시말해서 JOIN은 동등한 LEVEL에서 이루어지므로 양쪽 DATASET의
          어떤 컬럼이라도 SELECT문에서 이용 가능하다.
          하지만 SUB-QUERY가 들어간 문장은 SUB-QUERY DATASET을
          SELECT 문에서 이용할 수가 없다. 단지 조건절에서
          비교단위로만 쓸수가 있다.
          따라서 지금같은 경우는 INLINE VIEW가 SUB-QUERY로 내려가더라도
          하등의 문제가 될 것이 없다.
          SUB-QUERY로 내려보자 그리고 PLAN을 보자.

         INSERT INTO COMCOSTTARGETB                           (YYMM_YM,SITE_CD,BUDGET_CD,DEPT_CD,
                DRIVER_CD,DRIVER_QN,DIV_RT)
         SELECT '1999Q1','서울',BUDGET_CD,DEPT_CD,
                MAX(DRIVER_CD),
                SUM(DRIVER_QN),
                NULL
         FROM   COMCOSTTARGET
         WHERE  YYMM_YM BETWEEN '199801' AND '199803'
         AND    SITE_CD = '서울'
         AND   (BUDGET_CD,DEPT_CD,DRIVER_CD) IN
               (SELECT BUDGET_CD,DEPT_CD,SUBSTR(MAX(YYMM_YM||DRIVER_CD),7)
                FROM   COMCOSTTARGET
                WHERE  YYMM_YM BETWEEN '199901' AND '199903'
                AND    SITE_CD = '서울'
                GROUP BY BUDGET_CD,DEPT_CD)
         GROUP BY
                '1999Q1','서울',BUDGET_CD,DEPT_CD

         INLIN VIEW를 SUB-QUERY로 내리고 JOIN문장만 없앤것을 빼면
         모든 것이 단계2와 동일하다.
         물론 결과도 동일하다.
         PLAN을 보자.

         SELECT STATEMENTCost Estimate:                                                  
           SORTGROUP BY                                                                  
             NESTED LOOPS                                                                
               VIEW                                                                      
                 SORTGROUP BY                                                            
                   TABLE ACCESSBY ROWID:HABC,,,,,,,,,,,,,,,HCDT_COMCOSTTARGET(2)        
                     INDEXRANGE SCAN:HABC,,,,,,,,,,SYS_C00100252 (U)                    
               TABLE ACCESSBY ROWID:HABC,,,,,,,,,,,,,,,HCDT_COMCOSTTARGET(1)            
                 INDEXRANGE SCAN:HABC,,,,,,,,,,,,,,,,HCDI1_COMCOSTTARGET
         PLAN도 수행구조가 같다.
         수행시간도 거의 차이가 없다.
뒷풀이.
    하지만 모든 JOIN이 SUB-QUERY와 동일하게 작용하지 않는다는 것은
    그리고 언제 JOIN을 사용하고 언제 SUB-QUERY를 사용해야 하는가도
    모두가 잘 알고 있으리라.
    이 문제 또한 그리 쉽고 만만한 문제가 아니다.
    SUB-QUERY와 JOIN의 문제를 다시 다룰 기회가 있다면 그때 다시
    심도 깊게 생각해 보자.