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

6. 부분범위처리로의 유도
1. 전체 집합 정렬처리를 특정부분 정렬처리로 유도
2. 사용자지정 저장형 함수를 사용하는하는 방법 & SELECT-LIST에 인라인뷰를 사용하는 방법을 통한 부분범위처리 유도

6. 부분범위처리로의 유도

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

전체 집합 정렬처리를 특정부분 정렬처리로 유도

예시 테이블

문제제시

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

쿼리문

1) 일반적인 SQL구문

부분범위처리를 방해하는 정렬처리를 해야하는 컬럼은 세 테이블에 각각 분산되어있음
추출하려는 로우의 단위는 최하위 집합인 '급여' 테이블이므로 모든 집합을 조인한 후 ORDER BY를 사용하지 않을수 없음
서울에서 근무하는 과장들은 많지 않은 집합임은 분명함
'급여' 테이블과 조인하는 순간 로우는 약 100배 증가하여 증가된 집합을 정렬한 후 결과를 추출한다면 좋은 수행속도를 얻을수 없음


 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.년월 ;

2) 가장 이상적인 실행방안 SQL구문

적은 집합이 되는 '부서' 와 '사원' 테이블의 조인 결과만 먼저 정렬시키고 다량의 집합이 되는 '급여' 테이블은 기본키인 '사원번호+년원' 인덱스를 경유함
이 SQL은 인라인뷰 내에서 ORDER BY를 사용할수 없기 때문에 정상적으로 수행되지 않음


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' ;

3) GROUP BY사용 실행방안 SQL구문

힌트를 사용한 것은 반드시 인라인 뷰를 먼저 수행되고, 그 결과와 '급여' 테이블을 NESTED LOOPS조인을 시키기 위해서 사용함
ORDER BY 대신 GROUP 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' ;

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

문제제시

부분범위처리가 곤란함


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';

쿼리문

1) 사용자지정 저장형 함수를 사용하는하는 방법

인라인뷰를 사용했을 때와는 달리 저장형 함수 내의 SQL에 직접 조건을 기술할수가 있으므로 집합의 로우마다 모든 해당조건을 받아서 처리할수 있음
저장형 함수의 단점은 반드시 하나의 상수값만 리턴하도록 되어 있기 때문에 만약 여러 개의 값이 리턴되어야 한다면 곤란함


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';

2) SELECT-LIST에 인라인뷰를 사용하는 방법

'사용자 저장형 함수'와 유사한 효과를 낼 수 있을 뿐만 아니라 하나 이상의 값을 리턴할 수 도 있음
기타 SQL내에서 복잡한 가공을 하는데 매우 큰 기여를 할수 있음


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';