오라클 규칙 기반 옵티마이저(RBO, Rule-Based Optimizer)
최근에는 거의 사용되지 않는다.
오라클이 규칙 기반 옵티마이저로 동작하는 경우는 initSID.ora 파일에 OPTIMIZER_MODE=RULE or OPTIMIZER_MODE=CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우(11g이후 CBO로 동작) 인데 DW(Data WareHouse)에서는 비용 기반 옵티마이저(Cost-Based Optimizer)가 파티션테이블, 구체화 뷰 등을 지원하므로 규칙기반 보다는 비용 기반 옵티마이저가 더 효율적 입니다.
물론 OPTIMIZER_MODE=FIRST_ROWS or FIRST_ROWS_n or ALL_ROWS라고 한다면 통계 정보의 유무와 관계없이 비용 기반 옵티마이저를 이용하며 OPTIMIZER_MODE=CHOOSE인 경우 테이블이나 인덱스의 통계 정보가 있다면(Oracle11g이후에는 DBO로 동작) default로 ALL_ROWS로 동적 합니다.
참고로 Oracle11g 이후 RULE, CHOOSE Mode는 더 이상 지원하지 않으며 RULE BASED로 동작하기 위해서는 쿼리 문에 직접 RULE 힌트를 사용해야 합니다.
규칙 기반 옵티마이저의 우선 순위
rowid에 의한 단일 행 검색이 우선 순위가 가장 높습니다.
1. rowid에 의한 single row access
SQL>select empno, ename from emp where rowed = ‘AAA78BBBBBBBBB’;
2. cluster join에 의한 single row access
같은 클러스터 내에 있는 테이블을 조인할 때 이용, 아래 예는 EMP 테이블과 DEPT 테이블이 dept_no에 의해 cluster 되어 있으며 empno 컬럼이 EMP 테이블의 pk 인 경우의 예입니다.
SQL> select ename, dname, sal
from emp e, dept d
where e.deptno = d.deptno
and e.empno = 8888;
Execution Plan
------------------------------------------
SELECT STATEMENT
NESTED LOOP
TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
INDEX (UNIQUE SCAN) OF ‘pk_emp’ (UNIQUE)
TABLE ACCESS (BY CLUSTER) OF ‘DEPT’
3. unique key를 가지는 hash cluster에 의한 single row access
where절이 = 비교이며 hash cluster key를 가지며 단일 로우를 추출하는 경우에
사용되는 됩니다.
4. unique index에 의한 single row access
아래에서 EMP 테이블의 PK는 EMP_NO
SQL> select ename, sal
from emp
where empno = 8000;
5. cluster join
6. hash cluster key
7. indexed cluster key
8. composite index
EMP 테이블에서 JOB과 SAL에 복합 인덱스가 있는 경우
SQL>select ename
from emp
where job = ‘SALES’
and sal = 8000;
9. non unique index (single column)
EMP 테이블에서 JOB에 인덱스가 있는 경우
SQL>select ename
from emp
where job = ‘SALES’
10. bounded range search on indexed column(인덱스 컬럼을 이용한 제한된 범위 검색)
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select ename, sal
from emp
where sal > 1000
and sal < 5000;
11. unbounded range search on indexed column(인덱스 컬럼에 의한 제한되지 않은 범위 검색)
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select ename, sal
from emp
where sal > 1000;
12. sort merge join
13. index column의 MIN or MAX
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select MAX(SAL)
from emp;
14. index column에 대한 order by
15. full table scan
이상 규칙 기반 옵티마이저의 우선 순위에 대해 살펴 보았는데 규칙 기반 옵티마이저인 경우엔 실행 계획 수립시 번호가 낮은 것을 먼저 검토하여 적용가능한지 파악하며 한 단계식 높은 번호를 수행 합니다. 즉 낮은 번호 일수록 높은 우선 순위를 가지는데 예를 들어 다음과 같은 Query가 있다고 가정할 때….
SQL>select ename, job from emp where ename = ‘홍길동’;
(ename에 인덱스가 걸려 있다고 할 때)
데이터가 몇건 되지 않는다고 할 때 규칙 기반 옵티마이저인 경우 인덱스를 이용하는 것이 FULL SCAN하는 것보다 우선 순위가 높으므로 무조건 인덱스를 경유하여 EMP TABLE을 rowid에 의해 access 하는 실행계획을 세울 겁니다, 그러나 사실 이 테이블의 경우엔 데이터가 몇건 안되니까 한번에 FULL SCAN하여 가지고 오는 것이 훨씬 빠른데도 말입니다.
이 규칙 기반 옵티마이저의 경우 무조건 규칙에 위해 실행 계획을 만들어 내므로 융통성이 없는 것이 단점이며 그래서 최근엔 CBO를 많이 사용하고 있는 추세 입니다.
최근에는 거의 사용되지 않는다.
오라클이 규칙 기반 옵티마이저로 동작하는 경우는 initSID.ora 파일에 OPTIMIZER_MODE=RULE or OPTIMIZER_MODE=CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우(11g이후 CBO로 동작) 인데 DW(Data WareHouse)에서는 비용 기반 옵티마이저(Cost-Based Optimizer)가 파티션테이블, 구체화 뷰 등을 지원하므로 규칙기반 보다는 비용 기반 옵티마이저가 더 효율적 입니다.
물론 OPTIMIZER_MODE=FIRST_ROWS or FIRST_ROWS_n or ALL_ROWS라고 한다면 통계 정보의 유무와 관계없이 비용 기반 옵티마이저를 이용하며 OPTIMIZER_MODE=CHOOSE인 경우 테이블이나 인덱스의 통계 정보가 있다면(Oracle11g이후에는 DBO로 동작) default로 ALL_ROWS로 동적 합니다.
참고로 Oracle11g 이후 RULE, CHOOSE Mode는 더 이상 지원하지 않으며 RULE BASED로 동작하기 위해서는 쿼리 문에 직접 RULE 힌트를 사용해야 합니다.
규칙 기반 옵티마이저의 우선 순위
rowid에 의한 단일 행 검색이 우선 순위가 가장 높습니다.
1. rowid에 의한 single row access
SQL>select empno, ename from emp where rowed = ‘AAA78BBBBBBBBB’;
2. cluster join에 의한 single row access
같은 클러스터 내에 있는 테이블을 조인할 때 이용, 아래 예는 EMP 테이블과 DEPT 테이블이 dept_no에 의해 cluster 되어 있으며 empno 컬럼이 EMP 테이블의 pk 인 경우의 예입니다.
SQL> select ename, dname, sal
from emp e, dept d
where e.deptno = d.deptno
and e.empno = 8888;
Execution Plan
------------------------------------------
SELECT STATEMENT
NESTED LOOP
TABLE ACCESS (BY INDEX ROWID) OF ‘EMP’
INDEX (UNIQUE SCAN) OF ‘pk_emp’ (UNIQUE)
TABLE ACCESS (BY CLUSTER) OF ‘DEPT’
3. unique key를 가지는 hash cluster에 의한 single row access
where절이 = 비교이며 hash cluster key를 가지며 단일 로우를 추출하는 경우에
사용되는 됩니다.
4. unique index에 의한 single row access
아래에서 EMP 테이블의 PK는 EMP_NO
SQL> select ename, sal
from emp
where empno = 8000;
5. cluster join
6. hash cluster key
7. indexed cluster key
8. composite index
EMP 테이블에서 JOB과 SAL에 복합 인덱스가 있는 경우
SQL>select ename
from emp
where job = ‘SALES’
and sal = 8000;
9. non unique index (single column)
EMP 테이블에서 JOB에 인덱스가 있는 경우
SQL>select ename
from emp
where job = ‘SALES’
10. bounded range search on indexed column(인덱스 컬럼을 이용한 제한된 범위 검색)
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select ename, sal
from emp
where sal > 1000
and sal < 5000;
11. unbounded range search on indexed column(인덱스 컬럼에 의한 제한되지 않은 범위 검색)
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select ename, sal
from emp
where sal > 1000;
12. sort merge join
13. index column의 MIN or MAX
EMP 테이블의 sal 컬럼에 인덱스가 걸려 있는 경우
SQL>select MAX(SAL)
from emp;
14. index column에 대한 order by
15. full table scan
이상 규칙 기반 옵티마이저의 우선 순위에 대해 살펴 보았는데 규칙 기반 옵티마이저인 경우엔 실행 계획 수립시 번호가 낮은 것을 먼저 검토하여 적용가능한지 파악하며 한 단계식 높은 번호를 수행 합니다. 즉 낮은 번호 일수록 높은 우선 순위를 가지는데 예를 들어 다음과 같은 Query가 있다고 가정할 때….
SQL>select ename, job from emp where ename = ‘홍길동’;
(ename에 인덱스가 걸려 있다고 할 때)
데이터가 몇건 되지 않는다고 할 때 규칙 기반 옵티마이저인 경우 인덱스를 이용하는 것이 FULL SCAN하는 것보다 우선 순위가 높으므로 무조건 인덱스를 경유하여 EMP TABLE을 rowid에 의해 access 하는 실행계획을 세울 겁니다, 그러나 사실 이 테이블의 경우엔 데이터가 몇건 안되니까 한번에 FULL SCAN하여 가지고 오는 것이 훨씬 빠른데도 말입니다.
이 규칙 기반 옵티마이저의 경우 무조건 규칙에 위해 실행 계획을 만들어 내므로 융통성이 없는 것이 단점이며 그래서 최근엔 CBO를 많이 사용하고 있는 추세 입니다.
댓글 없음:
댓글 쓰기