DevStarSJ.github.io 으로 이사했습니다.: 대용량 데이터베이스 솔루션 2권 #14 INLINE VIEW 04 ~ 06

4. OUTER JOIN의 처리

SELECT ...
  FROM TAB1 X, TAB2 Y
 WHERE X.KEY = Y.KEY(+)
   AND X.FLD1 > 'AAA'
   AND Y.COL1(+) IN (10,30,50)

이 명령은 ERROR가 난다.
OUTER JOIN되는 집합에서의 비교는 1개만 가능하다.
왼쪽과 같이 실행시키는 방법으로는 INLINE VIEW, VIEW, NULL조건 추가 등의 방법이 있다.

* INLINE VIEW 활용

SELECT ...
  FROM (SELECT ...
          FROM TAB2
         WHERE COL1 IN (10,20,30)
       ) Y, TAB1 X
 WHERE X.KEY = Y.KEY(+)
   AND X.FLD1 > 'AAA'

* VIEW 활용

CREATE VIEW A_VIEW AS
  SELECT ...
    FROM TAB2
   WHERE COL1 IN (10,30,50)

SELECT ...
  FROM TAB1 X, A_VIEW Y
 WHERE X.KEY = Y.KEY(+)
   AND X.FIL1 > 'AAA'

* NULL 조건 추가

SELECT ...
  FROM TAB1 X, TAB2 Y
 WHERE X.KEY = Y.KEY(+)
   AND X.FLD1 > 'AAA'
   AND (Y.COL1(+) IN (10,30,50)
        OR Y.COL1 IS NULL)

- OUTER JOIN 되는 집합은 언제나 나중에 JOIN 된다.
- JOIN에 성공하든 실패하든 일의 양은 동일하다.
- 막연한 불안감으로 함부로 OUTER JOIN을 사용하지마라.

하나 이상의 잡합과의 OUTER JOIN

- OUTER JOIN의 기준은 2개가 되면 안된다.
- 만약 그렇게 된다면 INLINE VIEW를 이용하여 선처리하면 된다.

구재의뢰자재가 기준의 되는 Table이라는 것을 알 수 있다.

나머지 Table들이 OUTER JOIN인 경우
어떻게 SQL문을 작성해야 할까 ?

SELECT A.부서코드, MIN(부서명), C.자재코드,
       MIN(자재명), SUM(D.의뢰수량)
  FROM 부서 A, 구매의뢰 B, 자재 C, 구매의뢰자재 D
 WHERE B.부서코드 = A.부서코드
   AND D.부서코드(+) = B.부서코드
   AND D.일련번호(+) = B.일련번호
   AND D.자재코드(+) = C.자재코드
   AND A.위치 = '서울'
   AND C.자재코드 = '소모품'
   AND D.의뢰일자 LIKE '201407%'
 GROUP BY A.부서코드, C.자재코드;

-- ERROR 발생-- OUTER JOIN 기준은 2개가 되면 안된다.

SELECT X.부서코드, MIN(부서명), Y.자재코드,
       MIN(자재명), SUM(X.의뢰수량)
  FROM (SELECT A.부서코드, A.부서명,
               C.자재코드, C.의뢰수량
          FROM 부서 A, 구매의뢰 B,
               구매의뢰자재 C
         WHERE B.부서코드 = A.부서코드
           AND C.부서코드 = B.부서코드
           AND C.일련번호 = B.일련번호
           AND A.위치 = '서울'
           AND B.의뢰일자 LIKE '20140%') X, 자재 Y
 WHERE Y.자재코드 = X.자재코드(+)
   AND Y.자재코드 = '소모품'
 GROUP BY X.부서코드, X.자재코드;

5. 실행계획의 제어

SELECT A.사원번호, MIN(A.성명), MIN(급여총액), NVL(COUNT(*),0) 고령자수
  FROM 사원 A, 급여 B, 가족 C
 WHERE C.사원번호(+) = B.사원번호
   AND C.생년월일(+) < '19500101'
   AND A.사원번호 = B.사원번호
   AND B.년월 = '201001'
   AND B.급여총액 >= 35000000
 GROUP BY A.사원번호;

위 SQL문에 HINT를 사용하여 NESTED LOOPS JOIN 으로도 실행할수 있고, SORT MERGE JOIN 으로도 실행이 가능하다.
2가지 경우에 대해서 실행계획을 한번 살펴보자.

SORT (GROUP BY)
  NESTED LOOPS (OUTER)
    NESTED LOOPS
      TABLE ACCESS BY ROWID OF 급여
        INDEX RANGE SCAN OF 년월_IDX
      TABLE ACCESS BY ROWID OF 사원
        INDEX UNIQUE SCAN OF 사원_PK
    TABLE ACCESS BY ROWID OF 가족
      INDEX RANGE SCAN OF 가족_PK

SORT (GROUP BY NOSORT)
  MERGE JOIN
    MERGE JOIN (OUTER)
      SORT (JOIN)
        TABLE ACCESS BY ROWID OF 급여
          INDEX RANGE SCAN OF 년월_IDX
      SORT
        TABLE ACCESS BY ROWID OF 가족
          INDEX RANGE SCAN OF 생년월일_IDX
    SORT (JOIN)
      TABLE ACCESS FULL OF 사원

먼저 NESTED LOOPS JOIN의 실행계획을 한번 살펴보자. (왼쪽)
급여, 가족 Table은 CHECK 조건을 활용하여 독자적으로 범위를 줄였다.
하지만 가족의 조건을 보지 않은 사원과 급여의 JOIN이 먼저 되었다.
연봉이 3500만원인 사원의 수는 많지 않을까 ?
반면 1950년 이전생 고령자 가족을 가진 사원수는 그에 비해 훨씬 적을 것이다.
그런데도 불구하고 급여랑 사원을 먼저 JOIN해야한다. 왜 ? 가족은 OUTER JOIN 이니깐
더군다나 그 많은 쓸데없는 DATA들은 Random Access해야 한다.
별로 만족스러운 실행계획은 아니다.

그럼 이제 SORT MERGE JOIN의 실행계획을 살펴보자. (오른쪽)
급여 와 가족을 먼저 CHECK 조건으로 걸러낸 뒤 SORT MERGE 한다.
이까진 마음에 든다. 어찌보면 콩깍지 ( || ) 를 이용해서 조건에 맞는걸 하나로 뭉쳐 놓아서 비교 COST를 줄이는 거랑 모양새가 좀 비슷하다고 해야할까 ?
근데 밑에 보면 사원 Table과도 Sort Merge를 시도한다. 왜 ? 기껏 앞에서 이쁘게 조건에 맞는 것들만 하나로 뭉쳐놓고... 더군다나 사원 Table은 별 다른 CHECK 조건이 없어서 TALBE FULL SCAN ? 이거 진짜 환장할 노릇이다.

이 지경까지 오니 Optimizer만 믿고는 일을 못하겠단 생각이 든다.
당연히 급여랑 가족만 SORT MERGE JOIN 으로 이쁘게 조건에 맞는건만 모아놓고, 그 다음 사원 Table이랑은 NESTED LOOPS JOIN 하는게 가장 좋을 것같은데, Optimizer는 그걸 못한다니 말이다. 이럴때 INLINE VIEW와 HINT를 적절하게 사용하면 된다.

SELECT /*+ ORDERED USE_NL(X,Y) */
       Y.사원번호, Y.성명, 급여총액, 고령자수
  FROM (SELECT /*+ USE_MERGE(B,C) */
               B.사원번호, 급여총액, 고령자수
          FROM (SELECT 사원번호, COUNT(*) 고령자수
                  FROM 가족
                 WHERE 생년월일 < '19500101'
                 GROUP BY 사원번호) C, 급여 B
         WHERE C.사원번호(+) = B.사원번호
           AND B.년월 = '201001'
           AND B.급여총액 >= 35000000) X, 사원 Y
 WHERE Y.사원번호 = X.사원번호

NESTED LOOPS
  MERGE JOIN (OUTER)
    SORT (JOIN)
      TABLE ACCESS BY ROWID OF 급여
        INDEX RANGE SCAN OF 년월_IDX
    SORT (JOIN)
      VIEW
        SORT (GROUP BY)
          TABLE ACCESS BY ROWID OF 가족
            INDEX RANGE SCAN OF 생년월일_IDX
  TABLE ACCESS BY ROWID OF 사원
    INDEX UNIQUE SCAN OF 사원_PK

가족과 급여는 SORT MERGE JOIN을 하고 그 결과로 나온 사원번호 PK를 가지고 사원 Table과 NESTED LOOPS JOIN을 했다.

6. 부분범위처리로의 유도

SELECT A.DEPT_NAME, B.EMP_CODE, B.EMP_NAME, C.YYYYMM, C.SALARY
  FROM DEPT A, EMP B, SALARY C
 WHERE B.DEPT_CODE = A.DEPT_CODE
   AND A.LOC = '서울'
   AND B.GRADE = '과장'
   AND C.YYYYMM BETWEEN '200001' AND '201312'
 ORDER BY A.DEPT_NAME, B.JOIN_DATE, C.YYYYMM;

이 SQL은 ORDER BY 때문에 전체범위 처리가 된다.
ORDER BY에 1개만 있으면 INDEX를 이용하여 부분범위처리가 되게도 할수 있을텐데... 서로 다른 TABLE에서 3개의 Column으로...

이럴 땐 INLINE VIEW를 이용하여 비교적 DATA가 작은것들은 전체범위처리하고 그 정보를 제공자로 받아서 DATA가 큰 Table을 부분범위 처리로 하면 된다.

SELECT /*+ ORDERED USE_NL(X,Y) */
       X.DEPT_NAME, X.JOIN_DATE, X.EMP_CODE, X.EMP_NAME,
       Y.YYYYMM, Y.SALARY
  FROM (SELECT A.DEPT_NAME, B.JOIN_DATE, B.EMP_CODE,
               MAX(B.EMP_NAME)
          FROM DEPT A, EMP B
         WHERE B.DEPT_CODE = A.DEPT_CODE
           AND A.LOC = '서울'
           AND B.GRADE = '과장'
         GROUP BY A.DEPT_NAME, B.JOIN_DATE, B.EMP_CODE) X,
       SALARY Y
 WHERE X.EMP_CODE = Y.EMP_CODE
   AND Y.YYYYMM BETWEEN '200001' AND '201312';

그 동안 공부했던 것들이 많이 나왔다.
INLINE VIEW 안에서는 ORDER BY가 안된다. 그래서 ORDER BY 역할의 GROUP BY를 사용하였다.
EMP_NAME에 MAX를 사용한 이유는 ? GROUP BY 절의 SELECT에는 GROUP BY에 언급한 Column과 Group 함수들만 올 수 있기 때문에 MAX라는 Group 함수를 사용한 것이다.

먼저 범위가 작은 Table들을 원하는 순서대로 Sort하게 하는 전체범위 처리 한뒤, 그것과 Data가 많은 Table 을 부분범위처리로 유도하였다. 그 과정에서 Driving 되는 Table의 Data 순서대로 출력되기 때문에 HINT를 활용하여 Driving 되는 Table이 X가 되도록 명시해 주었다.