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

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

이 힌트는 CBO에서 Matreriakized views에 대해 Query Rewrite가 일어나도록 하는 힌트인데 8i 이상부터 가능 합니다. REWRITE 힌트 구문에 VIEW가 인자로 와도 되고 안 와도 되는데 인자로 뷰 리스트를 주지 않는 경우 적절한 materialized view를 찾고 항상 비용(COST)과 관계없이 사용 합니다.

Materialized views라는 것이 DW(Data WareHouse)에서 집계 데이터 등을 추출할 때 쿼리 수행속도를 빠르게 해주는 것인데 Oracle에서 Query Rewrite가 일어나기 위해서는 다음과 같은 조건이 만족 되어야 합니다.

OPTIMIZER_MODE = ALL_ROWS or FIRST_ROWS or CHOOSE
QUERY_REWRITE_ENABLED = TRUE
COMPITABLE = 8.1.0 이상

사용 형식은 다음과 같습니다.

[형식]
/*+ REWRITE [( view [view]... )] */

실습을 위해 먼저 Materialized views를 하나 만듭니다.

create materialized view  sum_sales
build immediate
refresh complete
enable query rewrite
as
select deptno,
       sum(sal) sum_sales
from   emp
group by deptno

만약 위 Query 실행 시 권한이 없다면서… 에러가 난다면 관리자로 로그인 하여 다음과 같이 실습 계정에 권한을 주시기 바랍니다.

grant create any materialized view to scott;
grant global query rewrite to scott;
grant query rewrite to scott;
grant alter any materialized view to scott;

MVIEW를 사용하지 않는 모양의 질의를 한번 볼까여…

위와 같이 한 후 다음과 같은 SQL문을 실행 합니다.

select deptno, sum(sal)
from   emp
group by deptno

--------------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost       
-----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS                3                   4
  HASH GROUP BY                3          15          4                                 
    TABLE ACCESS FULL        SCOTT.EMP        15          75          3                                                     

우선 세션 레벨에서 query_rewrite_enabled를 true로 바꿉니다.

alter session set query_rewrite_enabled=true;

select /*+ REWITE(sim_sales */
       deptno, sum(sal)
from   emp
group by deptno

----------------------------------------------------------------
Operation        Object Name        Rows        Bytes        Cost       
----------------------------------------------------------------
SELECT STATEMENT Optimizer Mode=ALL_ROWS                4                   3
MAT_VIEW REWRITE ACCESS FULL        SCOTT.SUM_SALES        4          104          3 

위 실행 계획을 보면 EMP 테이블을 이용하지 않고 SUM_SALES라는 MVIEW를 이용하여 실행 계획이 만들어 짐을 알 수 있습니다.