oracle - [Hint]ACCESS 경로를 변경하는 힌트(FULL)

[Hint]ACCESS 경로를 변경하는 힌트(FULL)
 
 
ACCESS 경로를 변경하는 힌트(FULL)

힌트문(FULL)의 인자로 주어지는 테이블에 대해 FULL SCAN할 것을 지시하는데 FROM절 다음에 테이블명과 Alias를 사용했다면 FULL 힌트에 Alias를 사용해야 하며 FROM절 다음 테이블명 앞에 스키마명이 사용되었다면(scott.emp등) 힌트안의 FULL에서는 스키마명은 생략을 해야 합니다.

[잘못된 경우] 

? 아래의 경우엔 FULL(e) 라고 해야 하는데 잘못 되었습니다.
select /*+ FULL(EMP) */
             ename, sal, job
from    emp e
where   job    = 'CLERK'

? 아래의 경우엔 FULL(EMP) 라고 해야 하는데 잘못 되었습니다.
select /*+ FULL(SCOTT.EMP) */
             ename, sal, job
from    scott.emp
where   job    = 'CLERK'

다음과 같은 SQL문장을 보도록 하죠~

[아래는 오라클 10gR2에서 테스트 했습니다]

SQL>create index idx_emp_job on emp(job)

SQL>analyze table emp compute statistics

SQL>select ename, sal, job
from   emp
where    job    = 'CLERK'

Execution Plan
---------------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost        Object Node        In/Out        PStart        PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS                3                   2                                                      
  TABLE ACCESS BY INDEX ROWID        SCOTT.EMP        3          45          2                                                      
    INDEX RANGE SCAN        SCOTT.IDX_EMP_JOB        3                   1                                                     

만약 JOB 컬럼에 인덱스가 존재하고 데이터의 분포도가 좋다면 인덱스를 타는 실행 계획이 세워질 수도 있으며 물론 통계정보가 있고 FULL SCAN하는 것이 훨씬 효율적이라고 판단이 된다면 TABLE을 FULL SCAN하게 하는 실행 계획을 세울 겁니다. 물론 통계정보가 생성 되어 있지 않다면 RBO(RULE-BASED OPTIMIZER)로 동작을 하여 인덱스를 경유하는 실행 계획을 세울 것 입니다.

그런데 위SQL을 실행하는 사람이 현재 EMP TABLE에 JOB이 CLERK인 데이터가 전체에서 90% 정도 있다는 것을 알고 있다면(이 경우 분포도는 상당히 낮다고 볼 수 있습니다.) 인덱스를 경유하여 실제 데이터를 추출하는 것보다 테이블을 FULL SCAN 하는 것이 성능에 도움이 된다고 판단할 수도 있겠죠…

이러한 경우에 명시적으로 옵티마이저에게 테이블 ACCESS시 전체 스캔을 하도록 알릴수 있는데 바로 FULL 이라는 Hint를 사용하는 것입니다.

예를 보도록 하죠~

SQL>create index idx_emp_job on emp(job)

SQL>analyze table emp compute statistics

SQL>select /*+ FULL(EMP) */
             ename, sal, job
from    emp
where   job    = 'CLERK'

그런데 만약 옵티마이저 모드가 RULE이라면 어떻게 JOB이라는 인덱스를 사용하지 않고 FULL SCAN 하도록 할까요? 답은 인덱스 컬럼에 고의로 변형을 가하는 것입니다.

예를 보죠^^

SQL> alter session set optimizer_mode=RULE

SQL> select ename, sal, job
from   emp
where    job    = 'CLERK'

Execution Plan
---------------------------------------------------------------
Operation        Object Name       
SELECT STATEMENT Optimizer Mode=RULE                                   
  TABLE ACCESS BY INDEX ROWID        SCOTT.EMP                           
    INDEX RANGE SCAN        SCOTT.IDX_EMP_JOB                                                                               

이번엔 인덱스 컬럼에 변형을 가합니다.

SQL> select ename, sal, job
from   emp
where  substr(job, 1, 5)    = 'CLERK'

Execution Plan
---------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost       
SELECT STATEMENT Optimizer Mode=RULE                             
  TABLE ACCESS FULL        SCOTT.EMP