꿈꾸는 개발자, DBA 커뮤니티 구루비

부분범위처리(Partial Range Scan)는 SQL에서 주어진 조건의 전체 범위를 모두 처리한 후에 결과를 추출하는 것이 아니라, 차례대로 처리해 가다가 결과의 로우 수가 운반단위(Array Size)를 채우게 되면 일단 결과는 추출되고, 사용자의 다음 요구가 있을 때까지 더 이상의 처리를 멈추게 되는 처리방법을 말한다.

서울에 근무하는 과장들의 입사일 순으로 8년간의 급여총액을 조회한다.


SELECT a.부서명, b.사원번호, b.성명, c.년월, c.급여총액
FROM  부서 a, 사원 b, 급여 c
WHERE b.부서코드 = a.부서코드
        and  c.사원번호 = b.사원번호
        and  a.위치     = '서울' 
        and  b.직책     = '과장' 
        and  c.년월 between  '199001' and '199712'
ORDER BY a.부서명, b.입사일, c.년월 ;

- 서울에 근무하는 과장들은 많지 않은 집합이다.
- '급여' 테이블과 조인하는 순간 로우는 약 100배 가량 증가한다. 증가된 집합을 정렬한 후 결과를 추출하면 좋은 속도를 얻을 수 없다.


SELECT /*+ ORDERED USE_NL(x y) */
                x.부서명, x.입사일, x.사원번호,  x.성명, y.년월, y.급여총액
FROM  (SELECT a.부서명, b.입사일, b.사원번호, b.성명)
               FROM   부서 a, 사원 b
               WHERE b.부서코드 = a.부서코드
                       and  a.위치     = '서울' 
                       and  b.직책     = '과장' 
               ORDER BY a.부서명, b.입사일, b.사원번호) x, 급여 y
WHERE y.사원번호 = x.사원번호
        and  y.년월 BETWEEN '199001' AND '199712' ;

- 적은 집합이 되는 '부서'와 '사원' 테이블의 조인 결과만 먼저 정렬시키고 다량의 집합이 되는 '급여' 테이블은 기본키인 '사원번호+년월' 인덱스를 경유함으로써 동일한 결과를 얻을 수 있다면 이것이 가장 이상적인 실행방법이다.
- 위의 쿼리는 이상적인 쿼리이나 인라인뷰 내에서 ORDER BY를 사용할 수 없기 때문에 실행되지 않는다.


SELECT /*+ ORDERED USE_NL(x y) */
                x.부서명, x.입사일, x.사원번호, x.성명, y.년월, y.급여총액
FROM  (SELECT a.부서명, b.입사일,  b.사원번호, MAX(b.성명)
               FROM   부서 a, 사원 b
               WHERE b.부서코드 = a.부서코드
                       and  a.위치     = '서울' 
                       and  b.직책     = '과장' 
               GROUP BY a.부서명, b.입사일, b.사원번호) x, 급여 y
WHERE y.사원번호 = x.사원번호
        and  y.년월 BETWEEN '199001' AND '199712' ;

- ORDERED USE_NL(x y) 힌트를 사용한 것은 반드시 인라인 뷰를 먼저 실행시키고, 그 결과를 '급여' 테이블과 NESTED LOOPS JOIN을 실행하기 위해서 사용한 것이다.
- ORDER BY 대신에 GROUP BY를 사용하여 데이터를 정렬하였다.
  - 먼저 실행된 인라인 뷰는 GROUP BY가 있으므로 부분범위처리를 할 수 없다. 그러나 이 집합은 매우 적으므로 전체범위처리(Full Range Scan)를 하더라도 문제가 되지 않는다.

사용자지정 저장형 함수를 사용하는하는 방법 & SELECT-LIST에 인라인뷰를 사용하는 방법을 통한 부분범위처리 유도

아래의 예제는 GROUP BY 때문에 부분범위처리가 어렵다.


SELECT a.사원번호, AVG_AMT * DECODE(a.직무, 'A1', 0.12, 0.11)
FROM 사원 a,
        (SELECT 사원번호, AVG(급여총액) AVG_AMT
         FROM 급여
         WHERE 년월 between '199801' and '199803'
         GROUP BY 사원번호) b
WHERE b.사원번호 = a.사원번호
and a.부서코드 = '1110';

아래와 같이 함수를 이용한다.


CREATE or REPLACE FUNCTION AVG_AMT_FUNC
    (v_empno varchar2)
     RETURN number IS
     V_avg_amt number;
BEGIN
     SELECT avg(급여총액) into v_avg_amt
     FROM 급여
     WHERE 사원번호 = V_EMPNO
          and 년월 between '199801' and '199803';
     RETURN v_avg_amt;
END AVG_AMT_FUNC;

SELECT 사원번호, AVG_AMT_FUNC(사원번호) * DECODE(직무, 'A1', 0.12, 0.11)
FROM 사원
WHERE 부서코드 = '1110';

위와 같이 함수를 작성하여 정렬작업을 하지 않게 함으로써 부분범위처리가 가능하다.
주의할 점은 함수는 반드시 데이터 한 건을 리턴해야 한다.


SELECT 사원번호,
            CURSOR( SELECT AVG(급여총액) * DECODE(a.직무. 'A1', 0.12, 0.11), MIN(급여총액), MAX(급여총액)
                          FROM 급여 b
                          WHERE b.사원번호 = a.사원번호
                              and b.년월 BETWEEN ;199801' AND '199803')
FROM 사원 a
WHERE 부서코드 = '1110';

위와 같이 SELECT-LIST를 사용하면 저장형 함수와 유사한 효과를 얻을 수 있고, 하나 이상의 값을 리턴할 수 있다.
기타 SQL 내에서 복잡한 가공을 하는데 매우 큰 기여를 할 수 있다.

About Doc.

최초작성자 : 안종식
최초작성일 : 2009년 5월 15일
이 문서는 오라클클럽 01.대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 이화식님의 '대용량 데이터베이스 솔루션2'을 참고했습니다.*