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

7. Stored Function 사용시 활용

SELECT EMP_CODE, AVG_AMT_FUNC(EMP_CODE), AVG_AMT_FUNC(EMP_CODE) * 100, SALARY / AVG_AMT_FUNC(EMP_CODE) FROM EMP WHERE DEPT_CODE = '1110';	동일한 함수가 3번 실행 되었다.
SELECT EMP_CODE, AVG_AMT, AVG_AMT * 100, SALARY / AVG_AMT FROM (SELECT EMP_CODE, AVG_AMT_FUNC(EMP_CODE) AVG_AMT FROM EMP WHERE DEPT_CODE = '1110');	단순히 INLINE VIEW를 사용했다고 해서 함수 실행횟수가 줄어들진 않는다. 비록 INLINE VIEW에서는 함수를 1번만 사용했더라도 실제 실행시에는 3번 수행된다.
SELECT EMP_CODE, AVG_AMT, AVG_AMT * 100, SALARY / AVG_AMT FROM (SELECT EMP_CODE, AVG_AMT_FUNC(EMP_CODE) AVG_AMT FROM EMP WHERE DEPT_CODE = '1110' GROUP BY EMP_CODE);	GROUP BY를 했으니 일단 가공된 DATA라고 판단하여 그 결과를 MEMORY에 저장하게 된다. 이렇게 했을 때는 함수를 1번만 수행하고 그 결과를 계속해서 사용하게 된다.

Stored Function은 Return 값이 하나 밖에 없다.
앞에서 배웠듯 하나 이상의 Column을 추출하기 위해서는 모두 고정길이로 만들어서 콩깍지 ( || )를 이용하여 결합하면 된다. 그리고 그 결과를 SQL에서 분할하여 사용하면 된다.
만약 2개의 결과값을 콩깍지 씌워서 받아와서 SUBSTR 을 이용해서 분리를 하게 되는데, 2개를 분리하기 위해서는 SUBSTR을 2번 호출해야 한다. 그러면 함수 또한 2번 호출되기 때문에 주의해야 한다.

CREATE or REPLACE FUNCTION
AVG_MAX_AMT_FUNC
  (V_EMPNO VARCHAR2)
  RETURN VARCHAR2 IS
  V_AVG_AMT VARCHAR2(30);
BEGIN
  SELECT RPAD(AVG(SALARY),15)
       ||RPAD(MAX(SALARY),15)
    INTO V_AVG_AMT
    FROM SALARY
   WHERE EMPNO = V_EMPNO
     AND YM BETWEEN '201301'
                AND '201312';
  RETURN V_AVG_AMT;
END AVG_MAX_AMT_FUNC;

하나 이상의 Column 추출시 고정길이로 만들어 결합해야 Return 받은 곳에서 SQL로 분할할때 편리하다.

SELECT EMPNO, ENAME, SUBSTR(AMT,1,15), SUBSTR(AMT,16,15)
  FROM (SELECT EMPNO, ENAME, AVG_MAX_AMT_FUNC(EMPNO) AMT
          FROM EMP            -- 2번 수행
         WHERE DEPTCODE = '1120');

SELECT EMPNO, ENAME, SUBSTR(AMT,1,15), SUBSTR(AMT,16,15)
  FROM (SELECT EMPNO, ENAME, AVG_MAX_AMT_FUNC(EMPNO) AMT
          FROM EMP             -- 1번수행
         WHERE DEPTCODE = '1120'
         GROUP BY EMPNO);

SELECT EMPNO, ENAME, AVG_MAX_AMT_FUNC(EMPNO)
  INTO :E_NO, :E_NAME, :E_AMT
  FROM EMP            -- FETCH 후 가공함수 1번 수행
 WHERE DEPTCODE = '1120';

:AVG_AMT = SUBSTR(:E_AMT,1,15);
:TOT_AMT = SUBSTR(:E_AMT,16,15);
...

8. SQL 기능확장을 위한 중간집합 생성

TAB1 으로 Data가 저장되어 있을 때 오른쪽 Report와 같은 결과를 바로 출력 할 수가 없다. 그래서 현업에서는 TAB2 를 만들어서 Trigger를 이용하여 일정시간마다 TAB1 에서 Data를 읽어서 가공하여 저장하는 경우가 많다. 하지만 Trigger의 경우 잘못된 Data로 인하여 이상한 값이 들어가거나, 간혹 과거에 이미 TAB2로 집계된 RAW Data 중 하나가 수정되는 경우는 그 값이 서로 틀어지게 된다.
이럴때는 TAB2를 물리적으로 만들지 말고, INLINE VIEW를 활용하여 중간 가공 Table을 생성하여 내가 원하는 모양으로 SQL로 만드는 방법을 이용하는 것이 더 좋다.

SELECT MIN(DECODE(NO, 1, ITEM)),                                          -- 첫줄만 ITEM 나머지는 NULL
       MIN(DECODE(NO, 1, 'TOT', SEQ))
       SUM(DECODE(SW, 0, AMT))
       SUM(DECODE(SW, 2, AMT))
       SUM((1-SW) * DECODE(NO, 1, AMT))
  FROM (SELECT ITEM, NO, DECODE(NO,1,'TOT',SEQ) SEQ, SUM(AMT) AMT, 0 SW   -- 복제한 중간 집합
          FROM (SELECT ITEM, SEQ, AMT
                  FROM TAB1
                 WHERE YMD LIKE '201407%') X, COPY_T Y
         WHERE Y.NO <= 2
         GROUP BY ITEM, NO, DECODE(NO, 1, 'TOT', SEQ)
         UNION ALL
         SELECT ITEM, 1, NO, 'TOT' SEQ, AMT, 2 SW                         -- M:M 처리를 위한 중간집합
           FROM TAB2
          WHERE YM = '201407')
 GROUP BY ITEM, NO, SEQ;

9. 유사한 집합의 통일

* 서로 다른 집합을 -> 내가 원하는 집합으로
* Column 이름이 다른것을 -> 같은 이름의 Column으로

단 주의사항들이 있다.

1. 통합집합에 조건을 부여한다면 INLINE VIEW Column 통합시 함부로 Column을 가공시키지 말자. (예, 일자 와 년월의 통합)
만약 꼭 해야만 한다면 상위로 말고 하위로 통합을 해라.

2. UNION ALL 이 아닌 UNION 을 사용하면 중복된 값은 DISTINCT 되며, 전체범위에서 SORT가 발생한다. 대부분의 경우 UNION ALL을 사용하지 UNION을 사용하는 경우는 잘 없을 것이다.

3. 안에서도 GROUP BY, 밖에서도 GROUP BY 이렇게 중복적으로 사용하지 말자. GROUP BY를 사용할때마다 SORT가 발생한다는 것을 명심하자.

SELECT 구분, 일자, 상품, 고객, SUM(원화)
  FROM (SELECT '상환' 구분,
               B.상환일자 일자,
               A.차입상품 상품,
               A.차입처 고객
               (B.원금외화 + B.이자외화) * C.환율 원화
          FROM 차입금원장 A, 상환내역 B, 일별환율 C
         WHERE A.MANAGEMENTCODE = B.MANAGEMENTCODE
           AND C.환율일자 = B.상환일자
           AND C.환율종류 = 'TTB'
       UNION ALL
        SELECT '예금' 구분,
               B.예입일자 일자
               A.예금종류 상품,
               A.예입처 고객,
               B.원화
          FROM 예적금원장 A, 예입내역 B
         WHERE B.MANAGEMENTCODE = A.MANAGEMENTCODE)
 GROUP BY 구분, 일자, 상품, 거래처;

10. 기타 특이한 형태의 활용

10.1 실행계획의 분리

계약번호와 관리번호 중에 최소한 한가지만 입력받는 Form의 경우

SELECT 계약NUM, 관리부서, 계약일, 구분, 고객주소
  FROM 계약 X, 부서 Y
 WHERE Y.부서NUM = X.관리부서
   AND 계약NUM LIKE :계약NUM||'%'
   AND 관리부서 LIKE :관리부서||'%'
   AND 계약일 LIKE 기준일||'%'
   AND NVL(구분,'X') = NVL(:구분,'X'); -- NULL 허용 비교

이런식으로 SQL문을 만드는 경우가 많다.
Optimizer는 한 SQL문에 대해서 하나의 실행계획을 만든다.
만약 계약번호 INDEX를 먼저 Driving 하는 것으로 실행계획이 잡혔는데, 계약번호에 값을 주지 않고, 관리번호만 입력되었다면, 계약번호는 FULL-SCAN하게 된다.

이런 경우라면 각 입력값에 따라 실행계획을 분리해줘야 한다.
각각의 경우를 UNION ALL 로 나누고, 절대 동시에 2개가 실행되는 일이 없게 조건을 주면 된다. (참집합은 1개)

SELECT 계약NUM, 관리부서, 계약일, 구분, 고객주소
  FROM (SELECT *
          FROM 계약
         WHERE 계약NUM IS NOT NULL    -- 계약번호가 들어올때
           AND 계약NUM = :계약NUM
           AND 관리부서 LIKE :관리부서||'%'
           AND 계약일 LIKE 기준일||'%'
           AND NVL(구분,'X') = NVL(:구분,'X')
       UNION ALL
        SELECT *
          FROM 계약
         WHERE 계약NUM IS NULL        -- 관리부서가 들어올때
           AND 관리부서 = 관리부서
           AND 계약일 LIKE 기준일||'%'
           AND NVL(구분,'X') = NVL(:구분,'X')
           AND ROWNUM <= 300) X, 부서 Y
 WHERE Y.부서NUM = X.관리부서;

단 주의해야 할 것이 하나있다.
실행계획을 분리할때 Column 단위로 나누는게 아니라 INDEX 단위로 나누어야 한다.
구분 되어져야 할 Column이 4개라고 16가지 실행계획을 나누는게 아니라, 그중 A+B INDEX, C+D INDEX 이렇게 2개의 INDEX가 있다면 2개의 INDEX중 어떤 조건에서 뭐를 먼저 타는것이 유리한지를 판단해 2개의 실행계획으로 나누면 된다.

10.2 BATCH Summary 처리로 ONLINE ACCESS

현업에서 Summary Data 들이 필요한 경우 별도의 Summary Table을 만들어서 일정 기간마다 등록한다.예를 들어서 1달 단위로 Raw Data들을 Summary Table로 계산해서 넣을 경우. 지난달까지의 집계는 조회가 가능하나, 당월 조회가 안된다. 그러나 경영진들의 요구는 끝이 없다. 그래도 어제꺼까지는 보고 싶으시단다. 그럼 당월꺼는 매일 Summary를 계산해서 갱신하도록 변경시켰다. 그런데 더 높으신 분이 오시더니, 1시간전에 생산한 것이 왜 Summary에 없냐고 하신다. ;;; 결국 30분 단위로 갱신하도록 수정했다. 아니나 다를까 그 뒤 다시 5초전... 3초전...Real Time으로 하잰다.
그래 차라리 Real Time이 더 쉽겠다. 하루 단위로 Summary Table을 갱신하고 그 이후꺼는 바로 계산해서 UNION ALL 로 보여 주는 것이 가장 괜찮은 해법인듯하다.

10.2.1 INLINE VIEW 활용

SELECT ITEM, SUM(AMT)
  FROM (SELECT ITEM, AMT
          FROM SUMMARY
         WHERE REG_DATE BETWEEN '20140101' AND '20140409'
      UNION ALL
        SELECT FLD1 AS ITEM, FLD2 AS AMT
          FROM RAW
         WHERE REG_DATE BETWEEN '20140408' AND '20140409')
 GROUP BY ITEM;

10.2.2 VIEW 활용

CREATE VIEW A_VIEW
  (ITEM, DATE1, DATE2, AMT)
AS SELECT ITEM, SUM_DATE, '99991231', AMT
     FROM SUMMARY
 UNION ALL
   SELECT ITEM, REG_DATE, REG_DATE, AMT
     FROM RAW;

SELECT ITEM, SUM(AMT)
  FROM A_VIEW
 WHERE DATE2 >= '20140408'
   AND DATE1 BETWEEN '20140101' AND '20140409'
 GROUP BY ITEM;

SELECT ITEM, SUM(AMT)
  FROM (SELECT ITEM, AMT
          FROM SUMMARY
         WHERE '99991231' >= '20140408'
           AND SUM_DATE BETWEEN '20140101' AND '20140409'
      UNION ALL
        SELECT ITEM, ANT
          FROM RAW
         WHERE REG_DATE >= '20140408'
           AND REG_DATE BETWEEN '20140101' AND '20140409')
 GROUP BY ITEM;