oracle - [Hint]Optimizer Mode를 변경하는 힌트 (RULE)

[Hint]Optimizer Mode를 변경하는 힌트 (RULE)
 
 

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