Oracle Admin 강좌 (Oracle9i)
Query Rewrite와 MView Refresh. 8 0 18,268

by 김정식 MVIEW QUERY REWRITE MVIEW REFRESH 질의 재작성 REFRESH REFRESH_ALL_MVIEWS DBMS_MVIEW [2005.03.12]


Query Rewrite(질의 재작성)

Query Rewrite(질의 재작성)란?

  A란 사용자가 자주사용되는 복잡한 Query 문장을 MView로 생성해 놓았을 경우.. 그 사실을 모르는 B가 A가 만든 뷰와 똑같은 결과를 조회하는 Query문을 MView가 아닌 일반 SQL문장으로 실행했을 경우 B는 SQL문장을 수행했지만. 같은 문장에 MView가 존재하면 B가 실행한 문장이 A가 생성해놓은 MView를 실행하는 것으로 자동 전환이 됩니다. 이러한 기능을 Query Rewrite라고 합니다.

  SQL문장을 수행하였어도 미리 정의된 MView가 존재한다면, MView를 조회하도록 Query가 다시 쓰여지는 됩니다.

Query Rewrite 예제

  Query Rewrite 기능을 사용하기 위해서는 다음의 기능이 필요 필요합니다

  - 인스턴스 파라미터인 OPTIMIZER_MODE, QUERY_REWRITE_ENABLED, QUERY_REWRITE_INTEGRITY, COMPATIBLE이 설정되어 있어야 합니다.

  - 또한 MView생성시 ENABLE QUERY REWRITE 옵션을 추가되어 있어야 합니다.

  - MView를 생성한 유저는 반드시 QUERY REWRITE의 시스템 권한이 있어야 합니다.

 
-- sysdba 권한으로 접속 합니다.
SQL> CONN / AS SYSDBA


-- QUERY_REWRITE_ENABLED를 TRUE로 변경 합니다.
SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED='TRUE';
시스템이 변경되었습니다.


-- 테스트 유저로 접속 합니다.
SQL> CONN scott/tiger


-- Query Rewrite가 정상적으로 실행되는지 확인하기 위해서 autotrace를 실행합니다.
-- AUTOTRACE 관련해서는 "SQL*Plus AUTOTRACE" 강좌를 참고해 보세요
SQL> SET AUTOTRACE ON


-- MView를 생성했던 SQL문장 실행
SQL> SELECT SUM(a.sal), a.deptno
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY a.deptno;


-- OPTIMIZER_MODE가 CHOOSE인 상태에서 Analyze를 실행하지 않아 QueryRewrite가
    실행되지 않았습니다.
-- OPTIMIZER_MODE가 CHOOSE일 경우는 모든 테이블을 ANALYZE 시켜 줘야 합니다.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (GROUP BY)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'EMP'
   4    2       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)


-- OPTIMIZER_MODE를 변경해 봤습니다.
SQL> ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
세션이 변경되었습니다.
 
 
-- OPTIMIZER_MODE 변경후 다시 실행
SQL> SELECT SUM(a.sal), a.deptno
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY a.deptno;
 
SUM(A.SAL)     DEPTNO
---------- ----------
      8750         10
     10875         20
      9400         30


-- DEPT_SAL이라는 MView로 Query Rewrite를 실행한 것을 알 수 있습니다.
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=82 Bytes=2132)
   1    0   TABLE ACCESS (FULL) OF 'DEPT_SAL' (Cost=2 Card=82 Bytes=2132)
    

Query Rewrite와 Hint 사용

  Index 관련 Hint를 사용하는 것 처럼, query rewite 관련 Hint를 사용하여 제어할 수도 있습니다.

  • - NOREWRITE : SELECT /*+ NOREWRITE */...
  • - REWRITE : SELECT /*+ REWRITE(mv1) */...

 

MView를 수동으로 Refresh 하기

  DBMS_MVIEW 패키지를 이용해서 수동적으로 MView의 Data를 최근의 데이터로 변경할 수 있습니다.

  DBMS_MVIEW 패키지의 REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT를 call 하면 됩니다.

 
-- 아래와 같이 emp테이블에 임이의 데이터를 INSERT한 후 MView를 갱신해 보세요..
SQL> INSERT INTO EMP 
        ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO )
     VALUES 
         ( 7935, 'KIM', 'MANAGER', 7839,  
           TO_Date( '12/17/1980 12:00:00 오전', 'MM/DD/YYYY HH:MI:SS AM'), 
           3000, NULL, 20);
1 개의 행이 만들어졌습니다.


SQL> COMMIT;
커밋이 완료되었습니다.


-- DEPT_SAL은 이전 MView생성 강좌에서 생성한 MView입니다.
-- DEPT_SAL MView는 ON DEMAND로 생성을 했기 때문에 데이타가 
-- 변경되지 않은 것을 확인 할 수 있습니다.
-- DEPT_SAL MView를 ON COMMIT로 생성했을 경우 위에 COMMIT시점에서 
-- dept_sal이 변경이 됩니다.
SQL> SELECT * FROM DEPT_SAL;


-- 수동으로  dept_sal 하나의 MView만 갱신 한 후 다시 조회하면 
-- 변경된 것을 확인 할 수 있습니다.
SQL> BEGIN
       DBMS_MVIEW.REFRESH('DEPT_SAL');    
     END;
     /
PL/SQL 처리가 정상적으로 완료되었습니다.



-- BASE 테이블에 EMP테이블이 들어간 모든 MView를 갱신
BEGIN
    DBMS_MVIEW.REFRESH_DEPENDENT('EMP');    
END;



-- 모든 MView를 모두 갱신
BEGIN
    DBMS_MVIEW.REFRESH_ALL_MVIEWS;    
END;
    

 

참고문헌

  • - Oracle Technical Note Materialized View 글 / 박경희
  • - Oracle Technical Bulletins No.12181 MATERIALIZED VIEW 활용방법
  • - Oracle 8i Tuning 정식 교재

- 강좌 URL : http://www.gurubee.net/lecture/1859

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입