대용량 데이터베이스 솔루션 2권 #10 Stored Function 1/2

Stored Function을 이용한 Data Link

* 특징

- SQL 내에 Programming process를 삽입할 수 있음
- 별도의 독립적인 Object로써 공유가능
- 1개의 값만 Return할 수 있음
- 해당 집합의 Row 단위마다 수행
- 반복 사용시 개별적으로 수행 (DB Execution 발생)
  -> 생각보다 많은 Overhead 발생
       -> 사용 형태에 따라 수행횟수의 차이가 있으므로 사용에 주의
- 같은 SELECT 집합내에서 수행했어도 다른 Column에서 사용불가
- JOIN의 관계형태를 변경시킬 수 있음 ( M:M, M:1 등에서 한쪽을 무조건 1로 변경 가능)

* 활용

- M:M, M:1 을 M:1, 1:1 로 변경
- 전체범위처리를 부분범위처리로 유도
- 배타적 논리합 관계의 JOIN

DB Call을 줄이자

SELECT item,
       AMT_FUNC(item,sysdate) 당일매출
       AMT_FUNC(item,sysdate-1) 전일매출
       (AMT_FUNC(item,sysdate) - AMT_FUNC(item,sysdate-1)) * 100 / AMT_FUNC(item,sysdate) 증감율
  FROM 재고자산
 WHERE 분류Code = '110';

위 SQL문는 Record 하나당 AMT_FUNC를 5번 Call한다.
만약 분류Code 가 110인게 100개 있다면 500번의 DB Call이 추가로 필요하다.
어떻게 줄일수 없나 ?
INLINE VIEW를 이용하는 방법이 있다.

SELECT item,
       당일매출
       전일매출
       (당일매출 - 전일매출) * 100 / 당일매출 증감율
  FROM (SELECT item,
               AMT_FUNC(item,sysdate) 당일매출
               AMT_FUNC(item,sysdate-1) 전일매출
          FROM 재고자산
         WHERE 분류Code = '110');

이제 해결이 됬다고 보이나 ?
무조건 Optimizer가 INLINE VIEW를 한번만 수행한다고 착각하면 안된다.
INLINE IVEW를 사용했더라도 Stored Function은 반복 수행된다.
한번만 수행되게 하고싶으면 INLINE VIEW 결과를 가공하면 내부적으로 저장되었다가 제공되므로 한번만 수행되는걸 보장받을 수 있다.

SELECT item,
       당일매출
       전일매출
       (당일매출 - 전일매출) * 100 / 당일매출 증감율
  FROM (SELECT item,
               AMT_FUNC(item,sysdate) 당일매출
               AMT_FUNC(item,sysdate-1) 전일매출
          FROM 재고자산
         WHERE 분류Code = '110'
         GROUP BY ITEM);

 Stored Function의 수행회수

SELECT 사번,
       FUNC_6(사번,col3)                                      -- GROUP BY 결과 ROW마다 수행
  FROM (SELECT x.사번,
               FUNC_1(x.부서) col1,                           -- 수행하지 않음
               MAX(FUNC_2(y.col2)) col2,                      -- WHERE 절에서 성공한 모든 ROW마다 수행
               SUM(y.본봉) col3
          FROM 사원 x, 급여 y
         WHERE x.사번 = y.사번
           AND x.부서 = '1100'
           AND y.col4 = FUNC_5(sysdate)                       -- JOIN방행과 Driving 여부에 따라 1번,
                                                                 혹은 사원 Table에서 성공한 횟수,
                                                                 혹은 급여 Table Access ROW마다 수행
           AND y.년월 BETWEEN '201401' AND '201406'
           AND FUNC_3(x.사번) > 1000                          -- 사원Table Access ROW마다 수행
           AND FUNC_4(y.항목) = 'ABC'                         -- 급여Table Access ROW마다 수행
           AND y.col5 > 100
         GROUP BY 사번, FUNC_1(x.부서));                      -- WHERE 절에서 성공한 모든 ROW마다 수행

1:M JOIN을 1:1JOIN 으로

SELECT X.사번, X.성명, X.직급, X.직책,
       AVG(Y.급여총액) 평균급여
  FROM 사원.X, 급여.Y
 WHERE X.사번 = Y.사번
   AND X.부서 = '1110'
   AND Y.급여년월 BETWEEN '201301' AND '201312'
 GROUP BY X.사번, X.성명, X.직급, X.직책

위 SQL문은 1:M JOIN 관계로 GROUP BY 때문에 전체범위로 처리된다.
모든 사원들의 값을 모두 JOIN 해서 계산하는것보다는
사원 1명별로 각각 평균급여를 뽑는게 더 빠르다.
그리고 부분범위처리로 변경되어 결과도 빠르게 볼 수 있다.

CREATE or REPLACE FUNCTION AVG_SAL_F
  (v_empno  varchar2)
  RETURN    varchar2 is
  V_avg_amt varchar2(30);
BEGIN
  SELECT avg(급여총액) INTO v_avg_amt
    FROM 급여
   WHERE 사번 = v_empno
     AND 년월 BETWEEN '201301' AND '201312'
  RETURN v_avg_amt;
END AVG_SAL_F;

SELECT X.사번, X.성명, X.직급, X.직책,
       AVG_SAL_F(empno) 평균급여
  FROM 사원
 WHERE 부서 = '1110';

M측에서 1 ROW만 JOIN

어떤 주문 ITEM에 대하여 최고가로 판매된 판매가격을 찾는 SQL이다.

SELECT x.item_cd, MAX(price)
  FROM ITEM_T x, SALE_T y
 WHERE x.item_cd = y.item_cd
   AND x.item_cd LIKE 'ABC%'
 GROUP BY x.item_cd

관련된 모든 SALE_T와 JOIN하게 된다.
M 측을 단 하나의 ROW와의 JOIN하게 하기 위해 Stored Function을 이용하면 된다.

CREATE or REPLACE FUNCTION ONEROW
  (v_item in varchar2)
  RETURN CHAR is
  max_prc number;
BEGIN
  SELECT /*+index_desc(x price_index */
         price
    INTO max_prc
    FROM SALE_T
   WHERE item = v_item
     AND ROWNUM = 1;
  RETURN max_prc;
END ONEROW;

SELECT item, ONEROW(item)
  FROM ITEM_T
 WHERE item_cd LIKE 'ABC%';

각 item_cd 마다 1 ROW만 연결이 된다.

다른 예제를 하나 보자.
어떤 주문 ITEM에 대하여 1년간 입금실적이 3회 이상인지를 추출하는 SQL이다.

SELECT x.거래처코드
       DECODE(COUNT(*),0,'X',1,'X',2,'X','O')
  FROM 거래처_T x, 입금_T y
 WHERE x.거래처코드 = y.거래처코드
   AND x.업종 = '제조업'
   AND y.입금일자 LIKE '2013%'
 GROUP BY x.거래처코드;

위 SQL의 문제점이 뭔지 보이는가 ?
3회 이상인것을 찾으니깐 3번만 COUNT하고 그만하면 되는데 전부 다 COUNT해야만 답이 나온다.
1녀간 발생한 입금내역을 모두 JOIN한다.
이걸 3번만 읽고 STOP하게 할려면 따로 Stored Function을 만들어서 처리하면 쉽다.

CREATE or REPLACE FUNCTION INAMT_COUNT_FUNC
  (v_custno IN VARCHAR2)
  RETURN CHAR IS
  ret_val CHAR(1);
BEGIN
  SELECT DECODE(MAX(RNUM),NULL,'X','O')
    INTO ret_val
    FROM (SELECT /*+index_desc(x PK_입금) */
                 ROWNUM AS RNUM
            FROM 입금내역 x
           WHERE 거래처코드 = v_custno
             AND 입금일자 LIKE '2013%')
   WHERE RNUM = 3
     AND ROWNUM = 1;
  RETURN ret_val;
END INAMT_COUNT_FUNC;

SELECT 거래처코드, INAMT_COUNT_FUNC(거래처코드)
  FROM 거래처_T
 WHERE 업종 = '제조업';

M:M JOIN의 해결

작은Table끼리라면 굳이 M:M JOIN 그대로 처리해도 상관없다.
10건 x 10건 해봐야 100건밖에 안된다.
솔직히 DB Table에서 1만건이라면 정말 Data가 몇건 안되는 것에 해당하는데,
1만건 X 1만건 해도 1억건이 된다.

대리점이 10건밖에 안된다면 그냥해도 되겠지만, 대리점이 매우 많이 존재한다면
저장형함수를 이용하여 부분범위처리로 유도하는게 휠씬 유리하다.

CREATE or REPLACE FUNCTION SALE_AMT_F
  (v_agnet  IN VARCHAR2,
   v_indate IN VARCHAR2)
  RETURN NUMBER IS
  ret_val NUMBER(14);
BEGIN
  SELECT NVL(SUM(매출액),0)
    INTO ret_val
    FROM 매출
   WHERE agent_cd = v_agent
     AND sale_date LIKE v_indate||'%';
  RETURN ret_val;
END SALE_AMT_F;

SELECT agent_cd, agent_name,
       SALE_AMT_F(agent_cd,:yymm) 당월,
       SALE_AMT_F(agent_cd,:yymm-100) 전년
  FROM 대리점
 WHERE 사업장 = :saup;

대용량 데이터베이스솔루션 2

작가

이화식

출판

엔코아컨설팅

발매

1999.11.20

평점

리뷰보기