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

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

Optimizer_MODE 값인 ALL_ROWS, FIRST_ROWS, CHOOSE, RULE등을 initSID.ora or spfileSID.ora에 설정을 하게되면 전체 DB 시스템에 영향을 미치지만 단일 SQL문장에서 힌트 형태로 사용된다면 해당 쿼리 내에서만 영향을 줍니다.

먼저 ALL_ROWS에 대해 알아보도록 하죠^^

ALL_ROWS는 CBO에서의 default이며 전체 데이터를 가지고 오는데 최적의 경로를 찾으므로 FULL SCAN을 할 가능성이 많이 있습니다. 그러므로 OLTP성 업무보다 Batch성 업무에 적합하다고 할 수 있습니다.

Oracle 9i, 10g의 경우 scott 계정의 emp, dept를 가지고 test 한다면 ALL_ROWS 힌트에 의해 아래의 Query는 Nested Loop Join을 수행하는 형태로 되는데 상황(데이터 건수나 인덱스 또는 hash_area_size값)에 따라 nested loop join을할건지 hash join을 할건지는 다양한 형태로 나타날 수 있지만 분명한 것은 ALL_ROWS 힌트를 사용한다면 반드시 통계정보를 생성해야 한다는 것입니다.

아래는 10g에서 수행한 예 입니다. 여러분들은 대량의 데이터를 가지고 있는 테이블에서 test 해서 ALL_ROWS를 사용 했을 때와 사용하지 않았을 때를 비교하여 보시기 바랍니다.

analyze table scott.emp compute statistics
analyze table scott.dept compute statistics

select /*+ALL_ROWS */
       e.empno,
           e.ename,
           d.dname
from   scott.emp e, scott.dept d
where  e.deptno = d.deptno       

 

Operation    Object Name Rows  Bytes  Cost  Object Node In/Out PStart PStop
-------------------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=HINT: ALL_ROWS                14                   4                                                      
  NESTED LOOPS                   14          294          4                                                      
    TABLE ACCESS FULL        SCOTT.EMP        14          140          3                                                      
    TABLE ACCESS BY INDEX ROWID        SCOTT.DEPT        1          11          1                                                      
      INDEX UNIQUE SCAN        SCOTT.PK_DEPT        1                   0