oracle - [Hint]규칙 기반 옵티마이저(Rule-Based Optimizer)

[Hint]규칙 기반 옵티마이저(Rule-Based Optimizer)
 
 
규칙 기반 옵티마이저(Rule-Based Optimizer)

오라클이 규칙 기반 옵티마이저로 동작하는 경우는 initSID.ora 파일에 OPTIMIZER_MODE=RULE or OPTIMIZER_MODE=CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우 인데 DW(Data WareHouse)에서는 비용 기반 옵티마이저(Cost-Based Optimizer)가 스타쿼리, 파티션테이블, 구체화 뷰 등을 지원하므로 규칙기반 보다는 비용 기반 옵티마이저가 더 효율적 입니다.

물론 OPTIMIZER_MODE=FIRST_ROWS or FIRST_ROWS_n or ALL_ROWS라고 한다면 통계 정보의 유무와 관계없이 비용 기반 옵티마이저를 이용하며 OPTIMIZER_MODE=CHOOSE인 경우 테이블이나 인덱스의 통계 정보가 있다면 default로 ALL_ROWS로 동적 합니다.

-----------------------------------------------------------
규칙 기반 옵티마이저의 우선 순위
-----------------------------------------------------------

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를 많이 사용하고 있는 추세 입니다.