1.4. 부분범위처리로의 유도 (by rnsl) [2009.04.30]
1.4. 부분범위처리로의 유도
대부분의 부분범위처리는 현재 실행환경이 부분범위처리를 지원해야 의미가 있다.
즉 운반단위까지만 패치가 일어나고 사용자가 다음 요구를 할 때까지 뒷부분의 수행이 멈추어져야만 의미가 있는 경우가 많다는 것이다.
VLDB:테스트 데이터생성
1.4.1 액세스경로를 이용한 SORT의 대체
- ORDER BY는 대개의 경우 전체범위처리가 되지만, 정렬하고자 하는 순서와 액세스를 주관하는 인덱스컬럼의 앞부분이 같다면 인덱스를 사용하여 부분범위처리를 할 수 있도록 유도할 수 있다.
- CBO인경우는 실행계획이 고정되지 않을 수 있으므로 힌트를 사용하여 부분범위처리로 유도할 수 있다.
- 부분범위처리로 유도하기 위해서 인덱스를 구성하고 있는 컬럼의 순서가 매우 중요하다.
- 경우에 따라 액세스의 조건으로 사용되지 않아도 ORDER BY를 없애기 위한 목적으로 인덱스에 필요한 컬럼을 추가할 수 있다.
(해당컬럼으로 정렬되는 액세스가 아주 빈번하고 처리범위가 넓은 경우)
P429 그림2-1-7
1.4.2 인덱스만 처리하는 부분범위처리
처리범위가 넓어지면 인덱스를 통해 테이블을 엑세스하는 랜덤 처리량이 크게 증가된다.
수행속도에 가장 많은 영향을 주는 테이블 랜덤액세스를 하지 않고 인덱스로만 처리할 수 있다면 처리범위가 넓더라도 매우 효율적인 처리를 할 수 있다.
인덱스만 사용하는 실행계획이 수립되기 전제 조건
- 쿼리에 사용된 모든 컬럼들이 하나의 인덱스에 결합되어 있거나
- 인덱스머지가 되는 실행계획이 되려면 머지되는 두 개의 인덱스 내에 모든 컬럼들이 모두 사용되어야 한다.
- 쿼리에 사용된 전체 컬럼들이 몇 개의 인덱스에 모두 포함될 수 있다면 인덱스조인이 성립된다.
VLDB:결합인덱스
VLDB:인덱스머지
VLDB:인덱스조인
조건절에 사용되지 않은 컬럼이라도 인덱스만 사용하는 처리로 유도할 목적으로 후보컬럼을 선정하여 결합 인덱스에 추가할 수 있다.
그렇다고 해서 인덱스에 함부로 컬럼을 추가하면 나쁜 실행계획이 나타날 수도 있을뿐 아니라 INDEX관리비용도 증가한다.
결합인덱스를 구성할 때 어떤 컬럼들과 결합하느냐에 따라 액세스에 미치는 영향은 아주 다르게 나타나므로 인덱스를 구성할 때는 반드시 액세스 형태를 조사하여 전략적 결정을 하여야한다. 
P430 그림2-1-8
1.4.3 MIN, MAX의 처리
최대값이나 최소값 또는 기본키의 일련번호 증가를 위해 최대값을 찾는 경우가 자주 발생한다.
기본키의 일련번호 증가방법
1. 각 분류별로 하나의 로우를 가진 별도 테이블을 생성하여 트랜젝션마다 +1의 처리를 하는 방법
방문테이블
| 구분 | 회수 |
| 사장 | 1 |
| 회원 | 32 |
| 비회원 | 3454 |
2. 시퀀스 생성기(Sequence Generator)를 사용하는 방법
시퀀스의 사용
어떤 세션에서 NEXTVAL을 하지 않은 채 CURRVAL을 요구하면 에러가 발생한다.
CURRVAL을 현재 가지고 있는 최종 시퀀스값을 의미하므로 한번도 NEXTVAL을 요구한 적이 없다면 최종보유하고 있는 CURRVAL이 없기 때문이다.
시퀀스를 잘 사용하지 않는 이유
1. 하나의 컬럼으로 인조 식별자를 만들 때 임의의 값으로 생성되는 것을 매우 싫어하는 경향때문이다.
실제 의미있는 코드로 실별자를 만들었어도 업무변화에 의해 규칙을 지킬 수 없는 경우가 발생
2. 중간에 누락이 되는 번호가 발생되기 때문이다.
어차피 업무에 의해 삭제가 되는 경우가 발생
3. 특정 분류단위로 일련번호가 증가하는 경우에는 처리 할 수 없기때문이다.
그래서 분류별로 하나의 로우를 가진 별도 테이블을 생성하는 경우가 실제로 많다. 이경우 심각한 LOCK이 발생할 수 있으므로 함부로 사용해서는 안된다.
VLDB:3번의 해결방법
1.4.4 FILTER형 부분범위처리
값이 존재하는지 여부만을 확인하는 작업은 처리된 결과가 한 개만 있거나 수백, 수천 개가 있거나 결과는 다를게 없다. 그렇다면 조건을 만족하는 첫 번째를 만나는 순간 실행을 종료하여도 충분하다
1.4.5 ROWNUM을 이용한 부분범위처리
ROWNUM 이란 SQL이 실행되는 과정에서 발생하는 일련번호이며 일종의 가상(Pseudo)컬럼이다.
ROWNUM 은 액세스되는 로우의 번호가 아니라 조건을 만족한 '결과에 대한 일련번호'이다.
SELECT ROWNUM, item_cd, category_cd, ...
FROM order
WHERE deptno like '120%'
AND qty > 0
AND ROWNUM <= 10
ORDER BY item_cd
SELECT ROWNUM, item_cd, category_cd, ...
FROM (SELECT *
FROM product
WHERE deptno like '120%'
AND qty > 0
ORDER BY item_cd )
WHERE ROWNUM <= 10
논리적으로 보면 단위 SELECT마다 하나씩의 ROWNUM을 가질 수 있다.
선행집합에서 생성된 ROWNUM을 활용하고 싶다면 다른 이름으로 치환을 해두면 계속 유효하다.
SELECT DEPTNO, TOTQTY
FROM ( SELECT ROWNUM AS RNUM, DEPTNO, TOTQTY
FROM ( SELECT DEPTNO, SUM(QTY) AS TOTQTY
FROM PRODUCT
WHERE DEPTNO LIKE '120%'
AND ROWNUM <= 100
GROUP BY DEPTNO)
)
WHERE RNUM = 5
AND ROWNUM = 1;
1.4.6 인라인뷰를 이용한 부분범위처리
인라인뷰를 이용하여 부분범위 처리를 하는 원리는 전체범위처리를 하는 부분을 인라인뷰로 묶어서 다른부분은 부분범위로 처리하겠다는 의미이다.
<인라인뷰를 이용하여 정렬을 조금 더 효과적으로 처리>
select a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot
from deprtment a, employee b, salary c
where b.deptno = a.deptno
and c.empno = b.empno
and a.location = 'SEOUL'
and b.job = 'MANAGER'
and c.sal_ym = '200512'
order by a.dept_name, b.hire_date, c.sal_ym;
select /*+ ordered, use_nl(x y) */
a.dept_name, b.empno, b.emp_name, c.sal_ym, c.sal_tot
from ( select a.dept_name, b.hire_date, b.empno, b.emp_name
from dept a, employee b
where d.deptno = a.deptno
and a.location = 'SEOUL'
and b.job = 'MANAGER'
order by a.dept_name, b.hire_date ) x, salary y
where y.empno = x.empno
and y.sal_ym = '200512';
<인라인뷰를 이용하여 그룹을 조금 더 효과적으로 처리>
select a.product_cd, product_name, avg_stock
from product a,
(select product_cd, sum(stock_qty) / (:b2 - :b1) avg_stock
from rpod_stock
where stock_date between :b1 and :b2
group by product_cd ) b
where b.product_cd = a.product_cd
and a.category_cd = '20';
처리시나리오
1) 대량의 처리 대상 범위를 가진 category_cd가 20인 product_cd를 부분범위 처리 방식으로 첫 번째 로우를 읽는다.
2) 읽혀서 상수값을 가지게 된 product_cd와 이미 상수값을 가진 stock_date로 인덱스(product_cd + stock_date)를 이용해 prod_stock테이블을 엑세스하여 평균 재고수량을 구한다
3) 두 번째 product_cd에 대해 위의 작업을 반복하다가 운반단위가 채워지면 일단 멈춘다.
select a.product_cd, product_name,
(select sum(stock_qty) / (:b2 - :b1)
from prod_stock b
where b.product_cd = a.product_cd
and b.stock_date between :b1 and :b2
) avg_stock
from product a
where category_cd = '20';
운반단위처리를 하지 않는 상황이라면 나쁜 처리가 될 수 있다.
?
1.4.7 저장형 함수를 이용한 부분범위처리
저장형 함수가 가지고 있는 가장 큰 특징은 sql내로 절차형 처리를 끌여들였다는 점이다.
복잡한 처리가 sql에서 처리될수록 전체범위로 처리되는 경우가 증가할 수 밖에 없다. 이러한 부분을 저장형 함수로 만들어서 숨겨버린다면 부분범위처리로 유도할 수 있다.
저장형 함수를 이용할 때 주의사항
- 단일값만 리턴하므로 여러개의 값을 얻기위해 반복사용하는 것을 주의해야 한다.
- 반환되는 집합의 로우마다 실행된다는 것이 처리속도에 문제가 될 소지가 있으므로 주의해야 한다.
create or replace function get_avg_stock
( v_start_date in date,
v_end_date in date,
v_product_cd in varchar2 )
return number is ret_val number(14);
begin
select sum(stock_qty) / (v_start_date - v_end_date)
into ret_val
from prod_stock b
where b.product_cd = v_product_cd
and b.stock_date between v_start_date and v_end_date;
return ret_val;
end get_avg_stock;
select product_cd, product_name,
get_avg_stock(product_cd, :b1, :b2) avg_stock
from product
where category_cd = '20';
확인자역할의 M집합처리를 위한 부분범위처리
select y.cust_no, y.cust_name, x.bill_tot, ...
from (select a.cust_no, sum(bill_amt) bill_tot
from account a, charge b
where a.acct_no = b.acct_no
and b.bill_cd = 'FEE'
and b.bill_ym between :b1 and :b2
group by a.cuat_no
having sum(bill_amt) >= 1000000 ) x, customer y
where y.cust_no = x.cust_no
and y.cust_status = 'ARR'
and rownum <= 30;
보고자하는 집합은 customer레벨인데 엑세스해야할 데이터는 하위 테이블이며 전체 고객을 대상으로 group by를 하여 having으로 체크하고 있다.
이와같은 비효율을 없애기 위해 exists서브쿼리를 활용하는 방법이 있으나 exists를 사용하면 bill_tot값을 얻어올수 없으므로 적용이 불가하다
결과를 리턴받으면서 부분범위를 가능하게 하려면 아래와 같이 저장형 함수를 사용함로써 해결할 수 있다.
create or replace fuction cust_arr_fee_func
(v_custno in varchar2,
v_start_ym in varchar2,
v_end_ym in varchar2)
return number is ret_val number(14);
begin
select sum(bill_amt)
into ret_val
from account a, charge b
where a.acct_no = b.acct_no
and a.cust_no = v_cust_no
and b.bill_cd = 'FEE'
and b.bill_ym between v_start_ym and v_end_ym;
return ret_val
end cust_arr_fee_fucn;
select cust_no, cust_name, cust_arr_fee_func(cust_no, :b1, :b2), ...
from customer
where cust_status = 'ARR'
and cust_arr_fee_func(cust_no, :b1, :b2) >= 1000000
and rownum <= 30;
조건절과 select-list절의 저장형함수각 각각 별도로 수행되기 때문 종복수행의 부담이 있다.
select cust_no, cust_name, bill_tot, ...
from (select rownum, custno, cust_name,cust_arr_fee_func(cust_no, :b1, :b2) bill_tot, ...
from customer
where cust_status >= 'ARR')
where bill_tot >= 1000000
and rownum <= 30;
rownum에 의해 서브쿼리의 결과를 임시 저장공간에 보관
<rownum을 제거하고 테스트, order by를 추가해서 테스트>
특정부분만 부분범위처리로 유도
처리범위는 넓지만 수행속도는 보장되어야하는 상황에서 집계성 테이블을 만들어 사용하는 경우가 많지만 그 중 많은 경우들은 부분범위처리로 유도함으로써 쉽게 해결할 수 있을 때가 생각보다 아주 많다.
1.4.8 쿼리의 이원화를 이용한 부분범위처리
옵티마이져가 최적화를 수행하는 단위는 sql이기 때문에 가능하면 sql을 통합해주는 것이 좋은 활용법이라 할 수 있으나 드물게 sql을 분리함으로써 보다 유리해지는 경우도 존재한다고 한다.
(인덱스는 deptno+sale_date로 구성)
select deptno, y.empno, y.empno, y.job, sal_tot, comm_tot
from (select empno,
sum(sal_amt) sal_tot,
sum(comm) comm_tot
from salary s
where s.deptno like '12%'
and s.sal_date between '20050101' and '20051231'
group by empno ) x, employee y
where y.empno = x.empno;
결합인덱스의 선행컬럼이 범위로 비교되어 액세스효율이 떨어진 경우
select deptno into :v_deptno
from dept
where deptno like '12%';
select deptno, y.empno, y.empno, y.job, sal_tot, comm_tot
from (select empno,
sum(sal_amt) sal_tot,
sum(comm) comm_tot
from salary s
where s.deptno = :v_deptno
and s.sal_date between '20050101' and '20051231'
group by empno ) x, employee y
where y.empno = x.empno;
-----------------------------------------------------------
select deptno, y.empno, y.empno, y.job, sal_tot, comm_tot
from (select empno,
sum(sal_amt) sal_tot,
sum(comm) comm_tot
from salary s
where s.deptno in (select deptno
from dept
where deptno like '12%')
and s.sal_date between '20050101' and '20051231'
group by empno ) x, employee y
where y.empno = x.empno;
문서에 대하여
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- {*}이 문서의 내용은 이화식님의 새로쓴 대용량 데이터베이스 솔루션을 참고했습니다.*
- 이 문서를 다른 블로그나 홈페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^\^