Oracle Hint, Tuning 강좌1.14 규칙 기반 옵티마이저 (Rule-Based Opti-mizer)

탑크리에듀 교육센터

1.14 규칙 기반 옵티마이저 (Rule-Based Optimizer)

오라클 데이터베이스가 RBO(Rule Based Optimizer, 규칙 기반 옵티마이저 ) 로 동작하는 경우는 옵티마이저 모드가 RULE 또는 CHOOSE 이면서 테이블이나 인덱스에 대한 통계 정보가 없는 경우인데 DW(Data WareHouse) 에서는 비용 CBO(Cost Based Opti-

mizer, 비용 기반 옵티마이저 ) 가 파티션테이블 , 구체화 뷰 등을 지원하므로 규칙기반 옵티마이저보다 더 효율적이다 .

물론 옵티마이저 모드가 FIRST_ROWS, FIRST_ROWS_N, ALL_ROWS 라면 통계 정보의 유무와 관계없이 CBO 를 사용하며 , CHOOSE

이면서 하나의 테이블이라도 통계정보가 있다면 역시 CBO 로 동작한다 . Oracle11g 이후 기본값은 ALL_ROWS 이며 RULE,

CHOOSE 모드는 더 이상 지원하지 않지만 사용은 가능하다 .

규칙 기반 옵티마이저의 우선 순위rowid 에 의한 단일 행 검색이 우선 순위가 가장 높다 .

1. rowid 에 의한 single row access

SQL>select empno, ename from emp where rowid = ‘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 는 EMPNO

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 가 있다고 가정하면

SELECT ename, job FROM emp WHERE ename = ‘SCOTT’;

(ENAME 칼럼에 인덱스가 생성되어 있다고 할 때 )

WHERE 절에 출현하는 칼럼에 인덱스가 있으면 무조건 인덱스를 이용한다 . 인덱스를 이용하는 것이 FULL SCAN 하는 것보다 우선 순위가 높으므로 먼저 ENAME 인덱스를 이용하여 ‘ SCOTT’ 을 랜덤 액세스 한 후 원본 테이블 레코드의 주소값인 ROWID 를 찾는다 . 다시 그 주소값 (ROWID) 을 이용하여 원본 테이블 레코드에 접근 후 JOB 칼럼값을 추출하는 실행계획을 세운다 . 사실 이 테이블의 경우 데이터가 몇 건 안되니까 한번에 FULL SCAN 하여 가지고 오는 것이 훨씬 빠른데도 인덱스를 이용한다

규칙 기반 옵티마이저의 경우 무조건 규칙에 의해 실행 계획을 만들어 내므로 융통성이 없는 것이 단점이며 최근에는 CBO 를 많이 사용하고 있는 추세이다 .

감사합니다 .

탑크리에듀 교육센터02-851-4790

www.topcredu.co.kr