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

제 3장 INLINE VIEW의 활용

다음 10가지 상황에 대해서 얘기해보겠다.

1. 단계적 JOIN을 위한 활용
2. Recursive 관계 전개시의 JOIN
3. 방사형 JOIN의 해결
4. OUTER JOIN시의 처리
5. Execution Plan의 제어
6. 부분범위처리로의 유도
7. Stored Function 사용시의 활용 (Function Call을 줄이기 위해)
8. SQL 기능확장을 위한 중간집합 생성
9. 상이한 집합의 통일
10. 기타 특이한 형태의 활용사례

1. 단계별 JOIN을 위한 활용

JOIN 수행에 포함되는 Record의 수를 줄이는 것이 목적이다.

1.1 GROUP BY 후 JOIN

SELECT Y.DEPT_NAME, SUM(X.SUM_QTY), SUM(X.SALE_AMT)
  FROM SALE X, DEPT Y
 WHERE X.DEPT_CD = Y.DEPT_CD
   AND X.YYMM = :IN_DATE
   AND X.SAUP = :SAUP
 GROUP BY Y.DEPT_NAME;

먼저 JOIN 한 후 GROUP BY를 수행함으로, 나중에 합쳐질 Row들까지 각각 따로 JOIN을 수행한다.

SELECT Y.DEPT_NAME, S_QTY, S_AMT
  FROM (SELECT DEPT_CD, SUM(SALE_QTY) S_QTY, SUM(SALE_AMT) S_AMT
          FROM SALE
         WHERE YYMM = :IN_DATE
           AND SAUP = :SAUP
         GROUP BY DEPT_CD) X, DEPT Y
 WHERE X.DEPT_CD = Y.DEPT_CD;

다량의 Data를 Inline View에서 먼저 GROUP BY하여 Row 수를 줄인 후 JOIN한다.

1.2 GROUP BY 후 DECODE

SELECT DEPT_CD, SUM(SALE_QTY) TOTAL,
       SUM(DECODE(SUBSTR(S_DATE,7,2),'01',QTY)) S_01,
       SUM(DECODE(SUBSTR(S_DATE,7,2),'02',QTY)) S_02,
       ...
       SUM(DECODE(SUBSTR(S_DATE,7,2),'31',QTY)) S_31,
  FROM SALE
 WHERE YYMM = :IN_DATE
   AND SAUP = :SAUP
 GROUP BY DEPT_CD;

원시 Table의 Row 마다 SUBSTR()을 31번씩 실행하게 된다. 그 결과는 모두 다 같다. 그냥 1번만하고 그 결과를 31군데서 같이 쓰면 된다.

SELECT DEPT_CD, SUM(SALE_QTY) TOTAL,
       SUM(DECODE(DD,'01',QTY)) S_01,
       SUM(DECODE(DD,'02',QTY)) S_02,
       ...
       SUM(DECODE(DD,'31',QTY)) S_31,
  FROM (SELECT DEPT_CD, SUBSTR(S_DATE,7,2) DD, SUM(QTY) QTY
          FROM SALE
         WHERE YYMM = :IN_DATE
           AND SAUP = :SAUP
         GROUP BY DEPT_CD, SUBSTR(S_DATE,7,2))
 GROUP BY DEPT_CD;

공통분모인 SUBSTR(S_DATE,7,2)를 Inline View에서 GROUP BY 절에 추가한다.
그리고 GROUP BY된 결과를 Main-query에서 DECODE로 수행한다.

2. Recursive 관계 전개시의 JOIN

* Recursive 관계의 유형
1. 1:1 (사내커플, 배우자) , 1:M (대부분의 조직구성도)
      - Recursive 관계를 위한 Column을 자신의 Table이 가지므로,
      - 별도의 JOIN이 필요없다.
2. M : M
      - BOM (Bill Of Material) 구조로서
      - 구조를 가진 Table과 정보를 가진 Table이 분리된다.
      - 실무에서는 이런 구조가 많이 발생하지만 정작 System에 적용되는 예를 적다.
      - 왜냐면 잘몰라서다. 고도의 기술력이 필요하다고 생각하는데, 그렇게 고도는 아니고 그냥 좀 익히면 된다.

* Recursive 구조의 전개
- SQL문장으로 해결된다. (CONNECT BY ... START WITH)
  - 하지만 JOIN을 한 집한은 전개가 불가능하다.

BOM 구조의 전개

SELECT LPAD('',2 * LEVEL)||X.ITEM_CODE, X.QTY, Y.ITEM_NAME
  FROM RECIPE X, ITEM Y
 WHERE X.CHILD_ITEM_CODE = Y.ITEM_CODE
CONNECT BY PRIOR X.CHILD_ITEM_CODE = X.SUPER_ITEM_CODE
START WHTH X.SUPER_ITEM_CODE = 'PA101';

위와 같이 Recursive 관계를 전개하는 SQL문에 JOIN이 있으면 ERROR가 발생한다.
이럴 때에는 Recursive 전개를 하는 SQL을 Inline View로 생성하여 그 결과를 다른 Table과 JOIN하면 된다.

SELECT LPAD('',2 * LEVEL)||X.ITEM_CODE, X.QTY, Y.ITEM_NAME
  FROM (SELECT LEVEL, ITEM_CODE, QTY
          FROM RECIPE
    CONNECT BY PRIOR X.CHILD_ITEM_CODE = X.SUPER_ITEM_CODE
    START WHTH X.SUPER_ITEM_CODE = 'PA101') X, ITEM Y
 WHERE X.ITEM_CODE = Y.ITEM_CODE;

BOM 처리 실무 사례

1. BOM을 전개하여 하위구성 부품을 찾아 재고를 확인한다.
2. 남아있는 재고가 안전재고 이하면 대체품목으로 대체한다.
3. 대체품목의 재고마저 안전재고 이하면 긴급구매를 표시한다.

SELECT LPAD('',2*LVL)||DECODE(SW,'2',Z.ITEM_CODE, X.ITEM_CODE) ITEM_CODE,
       X.QTY, DECODE(SW,'2',Z.ITEM_NAME, X.ITEM_NAME) ITEM_NAME,
       DECODE(SW,'1','출고가능','2','대체가능','3','긴급구매') STATUS
  FROM (SELECT LVL, X.ITEM_CODE, Z.ITEM_CODE, QTY,
               DECODE(LEAST(Y.SAFE_QTY, Y.QTY), Y.SAFE_QTY, '1',
               DECODE(LEAST(Z.SAFE_QTY, Z.QTY), Z.SAFE_QTY, '2', '3')) SW,
               Y.ITEM_NAME, Z.ITEM_NAME
          FROM (SELECT LEVEL LVL, ITEM_CODE, QTY
                  FROM RECIPE
            CONNECT BY PRIOR CHILD_ITEM_CODE = SUPER_ITEM_CODE
            START WITH SUPER_ITEM_CODE = 'PA101') X, ITEM Y, ITEM Z
         WHERE Y.ITEM_CODE - X.ITEM_CODE
           AND Z.ITEM_CODE(+) = Y.REPLACE_ITEM_CODE);

Z가 없을수도 있으니 OUTER JOIN 으로 처리해야 한다.

그런데 만약 대체품목이 1가지가 아니라 N개가 존재한다면 ?

SELECT ...
  FROM (SELECT ...
          FROM RECIPE
    CONNECT BY ... START WITH ...) X, ITEM Y,
       (SELECT /*+INDEX(W ITEM_PRIORITY_IDX) */
               ITEM_CODE, NVL(MAX(QTY),0) QTY
          FROM REPLACE_ITEM W
         WHERE QRY >= SAFE_QTY
           AND ROWNUM = 1) Z
 WHERE Y.ITEM_CODE = X.ITEM_CODE
   AND Z.ITEM_CODE(+) = Y.ITEM_CODE;

위와 같이 고치면 될것 같지만 저렇게 하면 원하는 결과가 안나온다.
왜 그럴까 ?
Optimizer는 ROWNUM만 보면 환장한다. 그래서 잴 먼서 수행한다.
직접 실행해보면 알겠지만, 최초의 1개의 조건으로만 1번 수행하여 그 결과를 다른 ITEM에 대해서도 계속 적용을 하는 것을 볼 수 있을 것이다.
즉, VIEW 의 ROWNUM 처리 특성상 밖에서 파고드는 조건이 먼저 처리되지 않기 때문에 원하는 결과를 얻을 수 없다.
해결방법은 ???
빨간색 부분을 Stored Function 으로 만들어서 대체하면 원하는 결과를 얻을 수 있다.

3. 방사형 JOIN의 해결

JOIN은 어느 한쪽이 반드시 1이어야 한다.
하지만 결과에 가족과 급여를 같이 보고자 한다면,
가족과 급여의 M:M JOIN이 발생한다.

그럼 어떻게 해야할까 ?
INLINE VIEW를 이용해서 해결이 가능하다.

SELECT DEPT_NAME, B.EMP_CODE,
       AVG_AMT * CNT * DECODE(B.CLASS,'A1',0.12,0.11)
  FROM DEPT A,
       EMP B,
       (SELECT EMP_CODE, COUNT(*) CNT
          FROM FAMILY
         WHERE LIVING_WITH = 'Y'
         GROUP BY EMP_CODE) C,   -- GROUP BY를 이용하여 사원 LEVEL
       (SELECT EMP_CODE, AVG(SALARY) AVG_AMT
          FROM SALARY
         WHERE DATE BETWEEN :ST_DATE AND :END_DATE
         GROUP BY EMP_CODE) D    -- GROUP BY를 이용하여 사원 LEVEL
 WHERE B.DEPT_CODE = A.DEPT_CODE      -- 30명
   AND C.EMP_CODE(+) = B.EMP_CODE     -- 2만명
   AND D.EMP_CODE(+) = B.EMP_CODE     -- 2만명
   AND A.DEPT_CODE = :DEPT_CD;        -- 1개 부서

모든 집합을 사원 기준으로 1:1 JOIN이 되도록 가공하였다.
그런뒤 각각 JOIN을 하면 해결이 된다.
하지만 상당히 주의해서 사용해야 한다.
이럴 경우 다른 집합의 처리결과를 제공 받을 수가 없다.
그래서 각 INLINE VIEW 마다 자신의 CHECK 조건으로 범위를 줄여야 하는데, 그게 안되면 필요없는 DATA들을 모두 ACCESS 해야 한다.
이 예제를 보더라도 C, D를 만들기 위해 30명에 대한 DATA만 가공하면 될것을 전사원 2만명 것을 모두 GROUP BY하여야 INLINE VIEW가 생성된다.

각 INLINE VIEW마다 독자적으로 범위를 줄일 수 있을 때만 사용하는 것이 좋다.

위와 같이 GROUP BY 된 INLINE VIEW JOIN의 문제점에 대해서 좀 더 자세히 알아보자.

먼저 SORT_MERGE JOIN으로 수행했을 때이다.

1. 급여 TABLE에서 전 사원에 대해서 FULL-SCAN 한 후 JOIN
2. 가족 TABLE에서 전 사원에 대해서 FULL-SCAN 한 후 JOIN

위와 같은 2가지 문제가 발생하였다.

먼저 처리된 결과를 받아서 처리할 수 없어 ACCESS 범위가 증가하였다.

만약 각각 자신의 범위를 줄일 수 있다면 유리한 방법이 될 수 있다.

그럼 어떻게 수행되어야 할까 ?

NESTED LOOPS 로 수행되면서
이와 같은 실행계획을 가지는 것이 최적이다.

먼저 처리대상 부서의 사원들만 ACCESS 하고
해당 사원의 급여에 대해서만 처리 하고
해당 사원의 가족에 대해서만 처리하면 아주 좋겠다.

하지만 이 실행계획은 어디까지나 그냥 가정이지 실제가 아니다.
이렇게 수행되는 실행계획은 나오지 않는다.

HINT를 이용해서 억지로 NESTED LOOPS 로 수행되게 했을 경우
실제로 Optimizer 가 만들어낸 실행계획은 다음과 같다.

먼저 처리대상 부서의 사원들만 ACCESS 한 후
전 사원의 급여를 먼저 GROUP BY 한 결과와 NESTED JOIN 하고
전 사원의 가족을 먼저 GROUP BY 한 결과와 NESTED JOIN 한다.

불필요한 범위를 처리함으로써
결과적으로 SORT_MERGE JOIN과 거의 유사하다.

더군다나 SORT_MERGE 에 비해 Randm Access가 많아져서 더 느릴 가망성이 높다.

똑똑하지 못한 Optimizer가 비효율적인 실행계획을 수립한 것이다.

그럼 M:M JOIN을 반드시 NESTED LOOPS JOIN으로 해결하고자 한다면 어떻게 해야할까 ?

SELECT MIN(DEPT_NAME), X.EMP_CODE,
       AVG(SALARY) * MIN(DECODE(CLASS,'A1',0.12,0.11) * FAMILY)
  FROM (SELECT B.EMP_CODE, MIN(DEPT_NAME) DEPT_NAME,
               MIN(B.CLASS) CLASS
               COUNT(*) FAMILY
          FROM DEPT A, EMP B, FAMILY C
         WHERE B.DEPT_CODE = A.DEPT_CODE
           AND C.EMP_CODE(+) = B.EMP_CODE
           AND A.DEPT_CODE = :DEPT_CD
           AND C.LIVING_WITH = 'Y'
         GROUP BY B.EMP_CODE) X,
       SALARY Y
 WHERE Y.EMP_CODE(+) = X.EMP_CODE
   AND Y.DATE BETWEEN :ST_DATE AND :END_DATE
 GROUP BY X.EMP_CODE;

단계별로 GROUP BY 후 JOIN 하는 방법이 있다.

1. 부서와 사원 1 : 1 JOIN
2. 그것과 가족을 1 : M JOIN 후 GROUP BY 로 1 생성
3. 그것과 급여를 1 : M JOIN 후 GROUP BY 로 1 생성

먼저 수행한 결과를 받아서 처리범위를 줄일 수 있다.