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

부분범위 처리로 유도

1. M집합 CHECK시의 부분범위 처리

미납고객 50만명 중에 미납금액이 부여한 조건 내에 있는 처리대산 2000명을 추출하는 SQL이다.

SELECT 고객번호, 고객명, 연락처
  FROM (SELECT X.고객번호,
               MAX(X.고객명) 고객명,
               MAX(X.연락처) 연락처
          FROM 고객 X, 청구 Y
         WHERE X.고객번호 = Y.고객번호
           AND X.고객상태 = '연체'
           AND Y.납입구분 = 'N'
         GROUP BY X.고객번호
         HAVING SUM(Y.미납금) BETWEEN :VAL1 AND :VAL2)
 WHERE ROWNUM <= 20000;

위 SQL은 모든 고객을 다 뽑고난 뒤에 그 중 2천건만을 보여준다.
즉, 전체범위 처리를 한다.
이거야 굳이 저장형함수를 안쓰더라도 간단하게 부분범위처리로 변경이 가능하다.

SELECT 고객번호, 고객명, 연락처
  FROM 고객 X
 WHERE X.고객상태 = '연체'
   AND EXISTS (SELECT ''
                 FROM 청구 Y
                WHERE X.고객번호 = Y.고객번호
                  AND Y.납입구분 = 'N'
                GROUP BY X.고객번호
               HAVING SUM(Y.미납금) BETWEEN :VAL1 AND :VAL2)
 WHERE ROWNUM <= 20000;

하지만 위의 INLINE VIEW는 한번만 실행되지만, 아래는 2천번 수행하게 된다.
이 문제에 대해서는 다음 단원인 Sub-query에서 제공자/확인자를 자세히 다룰 것이다.
그리고 다른 큰 문제는 미납금액을 추출할 수가 없다.
그럼 미납금액도 추출가능하면서 부분범위처리를 하려면 ? 저장형 함수로 해결이 가능하다.

CREATE or REPLACE FUNCTION
CUST_UNPAY_FUNC
  (v_custno IN VARHAR2)
  RETURN NUMBER IS
  ret_val NUMBER(14);
BEGIN
  SELECT SUM(UNPAY) INTO ret_val
    FROM 청구
   WHERE 고객번��� = v_custno
     AND 납입구분 = 'N'
   GROUP BY 고객번호;
  RETURN ret_val;
END CUST_UNPAY_FUNC;

SELECT 고객번호, 고객명, 연락처,
       CUST_UNPAY_FUNC(고객번호)
  FROM 고객
 WHERE 고객상태 = '연체'
   AND CUST_UNPAY_FUNC(고객번호) BETWEEN :VAL1 AND :VAL2
   AND ROWNUM <= 2000;

그러나 저장형함수가 2회 호출되었다.
1회 호출로 해결할수 없나 ? 물론 있지. 1번 불러서 저장해놓고 그걸 사용하면 된다.

DECLARE
  SW NUMDER := 0;
  CURSOR C1 IS
  SELECT 고객번호, 고객명,
         CUST_UNPAY_FUNC(고객번호)
    FROM 고객
   WHERE 고객상태 = '연체';
BEGIN
  OPEN C1;
  WHILE SW <= 2000 LOOP
    FETCH C1 INTO :고객번호, :고객명, :미납금
    EXIT WHEN C1%NOTFOUND;
    IF 미납금 >= :VAL1 AND 미납금 <= :VAL2 THEN
      SW := SW + 1;
    END IF;
  ...

2. 전체범위처리로 수행되는 필터처리 해결

SELECT ...
  FROM TAB1 X
 WHERE COL1 = '111'
   AND EXISTS (SELECT ''
                 FROM TAB2 Y
                WHERE Y.KEY = X.KEY
                  AND ... );

Sub-Query안에 Main-Query Column이 있으면 Main-Query가 돌면서 한번씩 Sub-Query가 호출된다.
(확인자 Sub-Query라고 하는데 다음 단원에서 자세히 다룰 것이다.)
Filtering이 전체범위 처리가 된다. (Sort-Merge Filter)

CREATE OR REPLACE FUNCTION
EXISTS_CHECK_FUNC
  (V_TAB_KEY IN VARCHAR2)
  RETURN CHAR IS
  RET_VAL CHAR(1);
BEGIN
  SELECT 1 INTO RET_VAL
    FROM TAB2
   WHERE KEY = V_TAB1_KEY
     AND ...
     AND ROWNUM = 1;
  RETURN RET_VAL;
END EXISTS_CHECK_FUNC;

SELECT ...
  FROM TAB1
 WHERE COL1 = '111'
   AND EXISTS_CHECK_FUNC = 1;

Optimizer를 속이는 용도로 Main-query 한번당 함수를 1번 호출하는 것으로 변경하였다.
하지만 이게 속도가 너무 늦다면 그냥 전체범위처리로 수행하는게 전체 실행속도는 더 빠르다.

3. 특정부분만 부분범위처리로 유도

각 년도별 수출실적을 출력해야 하는데,
기준년월의 금액순으로 Sort 해야한다.
Sort에 영향을 미치는 기준년도만 전체범위처리로 수행하고, 나머지는 저장형 함수를 이용하여 부분범위처리로 유도하는 것이 유리하다.

CREATE OR REPLACE FUNCTION
GET_YEAR_AMT
  (V_COUNTRY IN VARCHAR2,
   V_GOODS IN VARCHAR2,
   V_YYMM IN VARCHAR2)
  RETURN NUMBER IS
  RET_VAL NUMBER(14);
BEGIN
  SELECT NVL(SUM(매출액),0) INTO RET_VAL
    FROM 매출실적
   WHERE 국가 = V_COUNTRY
     AND 상품 = V_GOODS
     AND 년월 = V_YYMM;
  RETURN RET_VAL;
END GET_YEAR_AMT;

SELECT 상품, 당년금액 * -1,
       GET_YEAR_AMT(국가, 상품, (:indate - 100)||''),
       GET_YEAR_AMT(국가, 상품, (:indate - 200)||''),
       GET_YEAR_AMT(국가, 상품, (:indate - 300)||''),
       GET_YEAR_AMT(국가, 상품, (:indate - 400)||''),
       GET_YEAR_AMT(국가, 상품, (:indate - 500)||'')
  FROM (SELECT 상품, 당년금액 * -1 당년금액              -- 금액 Sort ( * -1 을 이용해서 Sort )
          FROM (SELECT SUM(매출액) 당년금액, 상품        -- 상품 Sort
                  FROM 매출실적
                 WHERE 국가 = :COUNTRY
                   AND 년월 = :indate
                 GROUP BY 상품)
         GROUP BY 당년금액 * -1, 상품)

베타적 관계의 JOIN

Exclusive OR 관계의 JOIN (일명 아크관계)에서는 2개의 Table중 상황에 따라 1개의 Table과 JOIN을 해야한다.
이때 저장형 함수를 이용하면 어떤 상황에서도 1개의 Table만 Access하는 것을 보장받을 수 있다.

CREATE OR REPLACE FUNCTION GET_NAME_SEL (V_IDNO IN VARCHAR2, V_TYPE IN VARCHAR2) RETURN VARCHAR2 I RET_VAL VARCHAR2(14); BEGIN IF V_TYPE = '1' THEN SELECT 성명 INT RET_VAL FROM 개인 WHERE ID = V_IDNO; ELSE SELECT 법인명 INTO RET_VAL FROM 법인 WHERE ID = V_IDNO; RETURN RET_VAL; END GET_NAME_SEL; SELECT 계좌번호, 개설일자, GET_NAME_SEL(ID, 구분) FROM 계좌 WHERE 개설일자 LIKE :IN_DATE||'%';

OR JOIN의 주의사항

베타관계 JOIN시 주의해야 하는 상황이 있다.

SELECT X.계좌번호, X.개설일자, NVL(Y.성명, Z.법인명)
  FROM 계좌 X, 개인 Y, 법인 Z
 WHERE (X.구분 = '1' AND X.ID = Y.ID
        OR X.구분 = '2' AND X.ID = Z.ID)
   AND X.개설일 LIKE :IN_DATE||'%';

SELECT X.계좌번호, X.개설일자, NVL(Y.성명, Z.법인명)
  FROM 계좌 X, 개인 Y, 법인 Z
 WHERE X.ID IN (Y.ID, Z.ID)
   AND X.개설일 LIKE :IN_DATE||'%';

위 2개의 SELECT 문은 실제로 아래 SQL같이 UNION으로 분리되어 처리된다.

SELECT X.계좌번호, X.개설일자, NVL(Y.성명, Z.법인명)
  FROM 계좌 X, 개인 Y, 법인 Z
 WHERE X.구분 = '1' AND X.ID = Y.ID
   AND X.개설일 LIKE :IN_DATE||'%';
 UINON ALL
SELECT X.계좌번호, X.개설일자, NVL(Y.성명, Z.법인명)
  FROM 계좌 X, 개인 Y, 법인 Z
 WHERE X.구분 = '2' AND X.ID = Z.ID
   AND X.개설일 LIKE :IN_DATE||'%';

UNION 으로 분리된 각 SELECT문은 3개의 Table이 JOIN 되지만 연결고리는 2개의 Table간만 존재하게되어
나머지 1개의 Table 과는 묻지마 JOIN이 되어 Cartesian Coodination이 일어나게 된다.
예를 들어 X JOIN Y 의 결과가 100개, X JOIN Z 의 결과가 10개, Y의 크기가 1천만개, Z의 크기가 1천개 라면,
결과가 110개만 나와야 하는데,
X JOIN Y 의 결과가 100 X 1천 = 10만개
X JOIN Z 의 결과가 10 X 1천만 = 1억개
로 총 1억10만개의 결과가 나온다. DISTINCT를 사용하면 결과는 110개로 줄여질테지만, DB에는 얼마나 많은 Overhead가 발생할까 ?

그럼 해결 가능한 차선책을 알아보자.

1. 모든 배타적 관계를 OUTER JOIN

SELECT X.계좌번호, X.개설일자, NVL(Y.성명, Z.법인명)
  FROM 계좌 X, 개인 Y, 법인 Z
 WHERE Y.ID(+) = DECODE(X.구분,'1',X.ID)
   AND Z.ID(+) = DECODE(X.구분,'2',X.ID)
   AND X.개설일 LIKE :IN_DATE||'%';

항상 3개의 Table의 JOIN이 수행된다.
비교값이 NULL인 경우도 JOIN은 수행된다.

2. UNION ALL로 분할하여 JOIN

SELECT X.계좌번호, X.개설일자, Y.성명
  FROM 계좌 X, 개인 Y
 WHERE X.구분 = 1
   AND X.ID = Y.ID
   AND X.개설일 LIKE :IN_DATE||'%';
 UNION ALL
SELECT X.계좌번호, X.개설일자, Z.법인명
  FROM 계좌 X, 법인 Z
 WHERE X.구분 = 2
   AND X.ID = Z.ID
   AND X.개설일 LIKE :IN_DATE||'%';

현업에서 가장 많이 사용하는 방법이기도 하다.
하지만 INDEX 구성에 따라 처리주관범위가 반복 수행 될 수 있는 위험도 있으며,
         (INDEX를 구분 + 개설일 로 만들어야 함)
위의 예제는 TABLE가 3개 뿐이었지만, 훨씬 더 많은 경우는 코딩량이 증가하게 된다.

가장 좋은건 Stored Function을 이용하는 방법인듯하다.
하지만 익숙해 질려면 많은 훈련이 필요할 것이다.

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

작가

이화식

출판

엔코아컨설팅

발매

1999.11.20

평점

리뷰보기