Optimizer Mode를 변경하는 힌트 (RULE)
규칙 기반 옵티마이저(Rule-Based Optimizer)로 동작하여 실행 계획을 세우도록 하는 힌트인데 이 경우 테이블이나 인덱스의 통계 정보가 있다고 하더라도 무시하고 사용하지 않으며 규칙에 기반한 실행 계획을 세우게 됩니다.
옵티마이저는 순위가 매겨진 오퍼레이션에 근거하여 실행 계획을 세우며 순위가 높은 것이 우선 적용 됩니다. (어떤 규칙이 있는지 이전 강좌 “규칙 기반 옵티마이저”를 참고 하세요)
만약 SQL 문장에서 /*+ RULE INDEX(emp idx_ename */ 과 같이 RULE 힌트와 다른 힌트가 같이 사용된다면 RULE 힌트만 적용되니 이점 유념 하세요…
select /*+ RULE */
e.empno,
e.ename,
d.dname
from scott.emp e, scott.dept d
where e.deptno = d.deptno
Execution Plan
---------------------------------------------------------------
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE NESTED LOOPS
TABLE ACCESS FULL SCOTT.EMP
TABLE ACCESS BY INDEX ROWID SCOTT.DEPT
INDEX UNIQUE SCAN SCOTT.PK_DEPT
이번엔 Rule Based 이면서 인덱스를 사용하지 못하도록 to_char를 붙였습니다. 다들 아시죠? 인덱스 컬럼에 변형을 가하면 인덱스를 사용하지 못한다는 사실을! 그래서 FULL SCAN해서 정렬 후 MERGE JOIN을 하는 실행 계획으로 바뀌었습니다. 그리 좋은 방법은 되지 못하는 것 같습니다. 그리고 RBO를 이용하는 경우엔 철저히 원칙을 지켜야 합니다.
select /*+ RULE */
e.empno,
e.ename,
d.dname
from scott.emp e, scott.dept d
where to_char(e.deptno) = to_char(d.deptno)
Execution Plan
-------------------------------------------------------------------
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL SCOTT.DEPT
SORT JOIN
TABLE ACCESS FULL SCOTT.EMP
이번엔 똑 같은 Query이지만 ALL_ROWS 힌트로 이용해 봅니다. 현재 EMP, DEPT 테이블의 통계 정보는 생성되어 있습니다. 10g에서는 EMP, DEPT의 데이터 건수가 얼마 안되니 FULL SCAN해서 HASH JOIN을 하는 방법으로 실행계획을 만들었습니다.
select /*+ ALL_ROWS */
e.empno,
e.ename,
d.dname
from scott.emp e, scott.dept d
where to_char(e.deptno) = to_char(d.deptno)
Execution Plan
-----------------------------------------------------------------
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=HINT: ALL_ROWS 14 7
HASH JOIN 14 294 7
TABLE ACCESS FULL SCOTT.DEPT 4 44 3
TABLE ACCESS FULL SCOTT.EMP 14 140 3
규칙 기반 옵티마이저에서 조인시 적용되는 원칙은 다음과 같습니다.
조인되는 두 컬럼 모두에 인덱스가 있는 경우 FROM절에서 나중에 나타나는 테이블이 드라이빙 테이블이 되어 먼저 SCAN하고 그 건건이 LOOP를 돌면서 비드라이빙 테이블을 인덱스 UYNIQUE SCAN을 하게 될겁니다. 비용 기반 옵티마이저에서는 FROM절에 나타나는 테이블의 순서와 관계없이 통계 정보에 따라 드라이빙 테이블이 결정 됩니다. 물론 ORDERED 힌트를 사용하는 경우는 예외인데 이 경우 FROM 절에 나오는 테이블의 순서대로 테이블들이 조인 됩니다.
[조인되는 두 테이블 모두에 인덱스가 있는 경우]
create index idx_emp_deptno on emp(deptno)
select /*+ RULE */
e.empno,
e.ename,
d.dname
from emp e, dept d
where e.deptno = d.deptno;
Execution Plan
-------------------------------------------------------------------
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
TABLE ACCESS BY INDEX ROWID SCOTT.EMP
NESTED LOOPS
TABLE ACCESS FULL SCOTT.DEPT 드라이빙 테이블
INDEX RANGE SCAN SCOTT.IDX_EMP_DEPTNO
select /*+ RULE */
e.empno,
e.ename,
d.dname
from dept d, emp e
where e.deptno = d.deptno;
Execution Plan
-------------------------------------------------------------------
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
NESTED LOOPS
TABLE ACCESS FULL SCOTT.EMP 드라이빙 테이블
TABLE ACCESS BY INDEX ROWID SCOTT.DEPT
INDEX UNIQUE SCAN SCOTT.PK_DEPT
조인 컬럼중 한 테이블에만 인덱스가 있는 경우 FROM절 이후 테이블의 순서 보다 인덱스가 생성되어 있지 않은 테이블이 드라이빙 테이블이 되는 실행 계획을 세우게 됩니다. 아래에서 DEPT 테이블의 DEPTNO는 PK로서 인덱스가 있지만 EMP 테이블의 DEPTNO는 인덱스가 없는 경우 입니다.
아래에서는 EMP 테이블이 드라이빙 테이블 입니다.
[조인되는 두 테이블중 한 테이블에만 인덱스가 있는 경우]
select /*+ RULE */
e.empno,
e.ename,
d.dname
from emp e, dept d
where e.deptno = d.deptno;
Execution Plan
------------------------------------------------------------------
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
NESTED LOOPS
TABLE ACCESS FULL SCOTT.EMP
TABLE ACCESS BY INDEX ROWID SCOTT.DEPT
INDEX UNIQUE SCAN SCOTT.PK_DEPT
select /*+ RULE */
e.empno,
e.ename,
d.dname
from dept d, emp e
where e.deptno = d.deptno;
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=HINT: RULE
NESTED LOOPS
TABLE ACCESS FULL SCOTT.EMP
TABLE ACCESS BY INDEX ROWID SCOTT.DEPT
INDEX UNIQUE SCAN SCOTT.PK_DEPT
댓글 없음:
댓글 쓰기