4. 부분범위처리(Partial Range Scan)

4.1 부분범위 처리의 개념

• 어떤 SQL에서 Where절에 주어진 조건을 만족하는 전체범위를 처리하지 않고 운반단위(Array Size)까지만 먼저 처리하여
  그 결과를 추출시킨 뒤 다음 작업을 게속하겠다는 사용자의 요구가 있을 때까지 잠정적으로 수행을 멈추는 처리방식
• DBMS나 개발 툴(Tool)에 따라 기능을 제공하지 못하는 것들도 있음
• 주어진 모든 데이터를 처리하지 않고 일부만 처리하여 결과를 추출하므로 사용자가 아무리 넓은 범위의 처리를 요구하더라도
  아주 빠르 수행속도를 보장받을 수 있음

4.2 부분범위 처리의 적용 원칙

{section}{column:width=50%}

• 논리적으로 전체범위를 읽어 추가적인 가공을 하지 않고도 동일한 결과를 추출할 수 있는 경우
  {column}{section}

{section}{column:width=33%}

SELECT SUM(ordqty)
FROM   order
WHERE  ord_date like '9503%'

{column}{column:width=33%}

SELECT ord_dept, COUNT(*)
FROM   order
WHERE  ord_date like '9503%'
GROUP BY ord_dept

{column}{section}

• 위의 두개의 SQL은 SELECT List에 SUM, COUNT가 있으므로 조건의 일부만 액세스해서 결과를 추출하는 것은 도저히 불가
  즉, SELECT List나 WHERE절에 GROUP 함수를 사용하였다면 부분범위처리를 할 수 없음

SELECT ord_dept, ordqty * 1000
FROM   order
WHERE  ord_date like '9503%;
ORDER BY ord_dept

• 위의 SQL처럼{color:red}*ORDER BY가 사용*{color}되었다면 전체 범위 처리를 해야함
  만약 옵티마이저에 의해 선택된 인덱스와 ORDER BY 에 사용된 컬럼이 동일하다면 인덱스의 순서가 곧 ORDER BY 순서가 되므로
  옵티마이져는 order by를 무시하고 인덱스로 처리되는 실행계획을 수립하여 부분범위처리를 하게 됨
• ORDER BY에 사용된 컬럼의 순서와 개수가 생성된 인덱스와 동일해야만 'ORDER BY'가 무시되므로 굳이 ORDERBY를 사용하지 않더라도
  원하는 결과가 인덱스의 순서를 따른다면 가능한 ORDER BY를 사용하지 않는것이 좋음

order by 실행계획 예제

• UNION, MINUS, INTERSECT를사용한 SQL은 부분범위처리를 할 수 없음

SELECT deptno, empno
FROM   emp1
WHERE  sal > 1000000
UNION  deptno, empno
FROM   emp2
WHERE  hiredate between '01-jan-90' and sysdate

• 수립된 실행계획에 반드시 전체범위를 모두 액세스 하여야만 처리할 수 있는 SORT나 FILTER와 같은 처리 단계가 있기 때문이다

UNION실행계획예제

• UNION 처리방식
  • UNION을 해결하기 위해서는 먼저 두 개의 집합에 있는 모든 요소들을 액세스하여 단순결합을 한 후 정렬하여 한가씩의 요소들을 골라내는 방식으로 처리.
  • UNION처리에서 가장 먼저 수행되는 작업은 두 개의 집합에 있는 모든 요소들을 액세스하는 자업이므로 전체 처리 범위를 모두 액세스한 후 다음 처리를 수행하므로 무조건 부분범위처리를 할 수 없게 됨
  • 어느 한쪽의 집합이 공집합이거나 중복되어 있는 부분이 없더라도 역시 부분범위 처리를 할 수 없음
  • 사용자가 이미 두개의 집합에 중복되어 있는 요소가 없다는 것을 알고 굳이 중복여부를 확인할 필요가 없는 겨우든지, 중복이 되더라도 ㅡ대로 사용하고자 하는경우 ==> UNION ALL사용

UNION ALL실행계획예제

• INTERSECT 처리방식
  • 각각의 전체범위를 액세스하여 정렬(Sort)한 후 머지(Merge)하는 소트머지(Sort Merge)방식으로 처리되므로 부분범위 처리를 할 수 없다.
• MINUS 실행계획도 이와 거의 유사 ==> 전체범위 처리

INTERSECT, MINUS 실행계획 예제

• EXISTS, NOT EXISTS의 사용
  • 교집합이나 차집합은 경우에 따라 EXISTS, NOT EXITS를 사용함로써 부분범위 처리가 가능

{section}{column:width=50%}

• RBO, CBO의 'FIRST_ROWS'로 지정 ==> 부분범위 처리
• CBO의 'ALL_ROWS'로 지정되어 있다면 ==> 대부분이 전체 범위 처리
  {column}{section}

SELECT ord_dept, ordqty
FROM   order
WHERE  ord_dept > '1000'

• 위의 SQL문은 옵티마이져에 따라 실행게획의 차이가 있다
  • 옵티마이져가 'ALL_ROWS'였다면, 'ord_dept' 인덱스가 있다고 하더라도 손익분기점을 초과한다면 옵티망져는 인덱스를 사?하지 않고 전체 테이블을 스캔하는 실행계획을 수립 ==> 힌트절을 사용하여 부분범위 처리가 되도록 실행

4.3 부분범위 처리의 적용 원칙

SELECT *
FROM   order

• 위의 SQL은 where 절이 없으므로 테이블의 첫번째 블럭을 스캔한 모든 로우들을 그대로 운반단위로 보내 추출시키므로 부분범위 처리를 한다

SELECT *
FROM   order
ORDER BY item;

• 위의 SQL은 전체범위처리를 하므로 전체 테이블을 액세스하여 임시 저장공간에 저장하여 전체를 정렬시킨 후 한 운반단위를 추출하고 멈춘다. ==> 정렬 작업으로 결과가 늦게 나오는것이 아님

SELECT *
FROM   order
WHERE  ITEM>' ';

• 위의 SQL은 옵티마이져 목표가 'ALL_ROWS'가 아니라면 ITEM인데스를 사용하는 실행계획을 수립하며 부분범위로 처리되고, ORDER BY ITEM을 사용하지 않더라도 동일한 결과를 낼 수 있다

SELECT /*+ INDEX(ORDER ITEM_INDEX_NAME) */*
FROM   order
WHERE  ITEM>' ';

• 옵티마이져가 'ALL_ROWS' 였다면 전체 테이블을 액세스하는 실행게획을 수립하여 부분범위 처리를 하지 않으므로 위 처럼 힌트를 사용하여 인덱스를 사용하게 한다

SELECT *
FROM   order
WHERE  ordno BETWEEN 1 and 1000
AND    custno like 'DN%';

h3.4.4 부분범위처리로의 유도

• 'ORDER BY'는 전체 범위를 처리하므로 인덱스를 이용하여 정렬하는 방법
  
  출처 : 이화식(대용량데이터베이스솔루션) PPT자료
  
  출처 : 이화식(대용량데이터베이스솔루션) PPT자료

• 데이터를 처리할 범위가 넓어지면 인덱스를 통해 테이블을 액세스하는 랜덤 처리량이 증가하기 때문에 인덱스만을 이용하는 방법
  
  출처 : 이화식(대용량데이터베이스솔루션) PPT자료

• 기본키의 마지막 일련번호를 찾아 새로운 번호를 부여하는 방법
  1.각 분류단위마다 하나의 로우를 가진 테이블을 생성하여 트랜잭션마다 읽고 수정하는 방법 => 트랜잭션의 병목현상 및 LOCK을 발생시키는 원인
  2.오라클 SEQUENCE를 이용하는 방법
  3.현재 생성된 최대값을 찾아 1을 더하는 방법 ==> LOCK을 피할 수 있으나 넓은 범위에서 어떻게 효율적으로 최대값을 찾느냐가 관건
  
  출처 : 이화식(대용량데이터베이스솔루션) PPT자료

{CODE:SQL}
SELECT /*+ INDEX_DESC(A INDEX1) */
SEQ+1
FROM PRODUCT A
WHERE DEPT='12300'
AND JOB='부장'
AND ROWNUM = 1;
{CODE}

• 인덱스는 'DEPT+SEQ'로만 결합되어 있고 'JOB'은 인덱스에 결합되어 있지 않는 경우
  1. INDEX1을 역순으로 읽어 DEPT와 SEQ의 최대값을 가진 인덱스 로우를 찾는다
  2. 인덱스 로우에 있는 ROWID로 테이블의 로우를 랜덤으로 액세스 한다
  3. 읽혀진 로우에 있는 JOB이 '부장'이라면 ROWNUM=1을 만족했으므로 SEQ+1을 운반단위로 보내고 작업을 종료한다
  4. JOB이 '부장'이 아니라면 ROWNUM=1이 아직 만족되지 않으므로 INDEX1의 다음 로우를 찾아 2~3의 작업을 반복한다.
  ==> 액세스를 주관하는 컬럼과 MAX를 구하고자 하는 컬럼만 결합 인덱스로 되어 있으면 다른 추가적인 조건들은 영향을 미치지 못한다는 것을 알 수 없음
  
  출처 : 이화식(대용량데이터베이스솔루션) PPT자료

{section}{column:width=33%}

출처 : 이화식(대용량데이터베이스솔루션) PPT자료
{column}{column:width=33%}

* 왼쪽은 조건을 where절에 기술하고 그 결과를 count하여 
  변수에 저장한 후에 'IF CNT>0'과 같은 조건으로 다음 처리를 진행. 
  SQL은 원하는 집합을 요구하는 것이므로 그 결과가 한 건이든지 수천 건이든지 
  모두 처리하여 COUNT를 한 후 최종적으로 하나의 결과를 추출
* 오른쪽은 EXISTS를 사용하면 뒤에 있는 서브쿼리가 부분범위처리로 수행하다가 
   조건을 만족하는 첫번째 로우를 만나는 순간 서브쿼리의 수행을 멈추고 메인쿼리를 수행

{column}{section}

{section}{column:width=33%}

SELECT ord_dept, ord_date, custno
FROM   prodcut
WHERE  ord_date like '95%'
MINUS
SELECT ord_dept, ord_date, 'DN01'
FROM   sale
WHERE  custno = 'DN01'
=> 위의 SQL은 다량의 처리범위를 각각의 로우마다 랜덤 액세스로 대응되는 로우를 찾아 확인하는 것을
피하기 위해 전체 처리가 최적화(ALL_ROWS)되도록 Merge 방법으로 유도한 경우이다. 전체범위로 처리되므로 
온라인에서는 첫번째 운반단위가 추출되는 시간이 많이 소요

{column:width=33%}

SELECT ord_dept, ord_date, custno
FROM   product x
WHERE  ord_date like '95%'
AND    NOT EXISTS(SELECT * FROM SALE y
                  WHERE  y.ord_dept = x.ord_dept
                  AND    y.ord_date = x.ord_date
                  AND    y.custno = 'DNO1')
==> 서브쿼리가 부분범위 처리로 바뀌어서 처리속도가 개선 되었다

{column}{section}

• ROWNUM
• 테이블 내에 물리적으로 저장되어 있는 컬럼이 아니라 SQL에 그대로 삽입해서 사용할 수 있는 가상의 컬럼이다
• 인덱스를 통해 액세스한 테이블의 로우들 중에 다른 조건을 확인하여 만족하는 로우들만 ROWNUM이 부여되어 운반단위로 보내짐
• ROWNUM은 액세스되는 로우의 번호가 아니라 조건을 만족한 결과에 대한 일려번호 이므로 10건만 처리하고자 하더라도 내부적으로는 그것보다 훨씬 많은 로우가 액세스될 수 있음
• 추출되는 로우 중에 10번째 로우를 찾기 위해 'WHERE ROWNUM=10'을 요구했다면 이 조건을 만족하는 로우는 겨코 추출될 수가 없다

출처 : 이화식(대용량데이터베이스솔루션1)

 SELECT *
 FROM   product
 WHERE  dept like '12%'
 AND    qty > 0
 AND    ROWNUM <= 10
 ORDRE BY item;

출처 : 이화식(대용량데이터베이스솔루션1)

• ROWNUM이 'ORDER BY'가 수행되기 전에 이미 WHERE절에 있는 조건을 만족하는 로우마다 부여되어 임시공간에 저장. 'ORDER BY'를
  수행하기 전에 이미 'ROWNUM<=10'이 적용되어 단 10건만 정렬하여 운반단위로 보낸다. 그러므로 결과는 ROWNUM은 순차적으로 나타나지 않는다.

출처 : 이화식(대용량데이터베이스솔루션1)
{section}{column:width=33%}

 CREATE VIEW prod_view(dept, totqty)
 AS SELECT dept, sum(qty)
    FROM   product
    GROUP BY dept;

{column}{column:width=33%}

 SELECT rownum, dept, totqty
 FROM   prod_view
 WHERE  dept like '123%'
 AND    rownum <= 10;

{column}{section}

출처 : 이화식(대용량데이터베이스솔루션1)

• DEPT LIKE '123%'가 뷰의 조건절에 파고 들어 해당 범위만 액세스하여 GROUP BY를 수행
• GROUP BY를 수행하기 전에 임시공간에 저장된 ROWNUM은 조건에 만족되는것들만 ROWNUM이 부여
• GROUP BY는 임시공간에 저장된 데이터를 정렬하여 종류별로 집계한 후 다시 종류별로 한 로우씩 생성되어 다른 임시공간에 저장되고 ROWNUM은 다시 부여되며 운반단위를 통해 추출되는 ROWNUM은 순차적으로 나타나게 됨

• 버전 7.1 이상 부터는 인라인(Inline)뷰를 이용
  {section}{column:width=33%}

 SELECT rownu, dept, totqty
 FROM   (SELECT DEPT, SUM(QTY) as totqty
         FROM   PRODUCT
         WHERE  dept like '123%'
         GROUP BY dept)
 WHERE   rownum <= 10;

{column}{column:width=33%}

 SELECT dept, totqty
 FROM   (SELECT ROWNUM as RNUM, dept, totqty
         FROM   (SELECT dept, SUM(qty) as totqty
                 FROM   product
                 WHERE  dept like '123%' GROUP BY dept))
WHERE   RNUM=5
AND     ROWNUM=1
 ==> 조건절 ROWNUM=1조건을 추가해야만 불필요한 처리를 없앴을 수 있다. 만약 이 조건이 없다면 RNUM=5인 로우를 찾았더라도 멈추지 않고 FROM뒤에 기술한 인라인 뷰의 모든 로우들이 끝날때까 수행하게 된다

{column}{section}

 * 부서코드가 '11'로 시작하는 모든 사원들의 년간 급여액을 추출
 SELECT 부서코드, 사번, SUM(본봉), NVL(SUM(수당),0)
 FROM   급여테이블
 WHERE  부서코드 LIKE '11%'
 AND    급여일 BETWEEN '940101' AND '941231'
 GROUP BY 부서코드, 사번
 ==> *11로 시작하는 부서를 가진 사람들이 아주 많다면 온라인 어플리케이션에서 원하는 수행속도를 얻을 수 없음*

{section}{column:width=33%}

 SELECT 부서코드 ITNO :DEPTNO
 FROM   부서테이블
 WHERE  부서코드 LIKE '11%'
 ==> 일반적으로 급여테이블에 비해서 부서테이블은 로우가 작으로 11로 시작하는 부서코드를 먼저 찾는다

{column}{column:width=33%}
SELECT 사번, SUM(본봉), NVL(SUM(수당),0)
FROM 급여테이블
WHERE 부서코드 = :deptno
AND 급여일 BETWEEN '940101' AND '941231'
GRUOP BY 사번;

 * 위의 방법은 처리가 넓은 급여테이블에서 '11'로 시작하는 부서코드를 가진 모든 데이터를 읽지 않고 특정부서만 '='로 읽게 함으로써
    실제로는 처리범위의 일부분만 액세슷하도록 하여 부분범위처리로 유도한 것

{column}{section}

문서에 대하여

• 최초작성자 : 이가혜
• 최초작성일 : 2007년 10월 3일
• 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
• 이 문서의 내용은 이화식님의 대용량 데이터베이스 솔루션1 을 참고했습니다.