Query Rewrite와 MView Refresh.

◈ Query Rewrite(질의 재작성)  1) Query Rewrite(질의 재작성)란?   A란 사용자가 자주사용되는 복잡한 Query 문장을 MView로 생성해 놓았을 경우..   그 사실을 모르는 B가 A가 만든 뷰와 똑같은 결과를 조회하는 Query문을 MView가 아닌 일반 SQL문장으로 실행했을 경우 B는 SQL문장을 수행했지만. 같은 문장에 MView가 존재하면 B가 실행한 문장이 A가 생성해놓은 MView를 실행하는 것으로 자동 전환이 됩니다. 이러한 기능을 Query Rewrite라고 합니다. SQL문장을 수행하였어도 미리 정의된 MView가 존재한다면, MView를 조회하도록 Query가 다시 쓰여지는 거죠..  2) Query Rewrite 예제 Query Rewrite 기능을 사용하기 위해서는 다음의 기능이 필요 필요합니다    - 인스턴스 파라미터인 OPTIMIZER_MODE, QUERY_REWRITE_ENABLED,     QUERY_REWRITE_INTEGRITY, COMPATIBLE이 설정되어 있어야 합니다.  - 또한 MView생성시 ENABLE QUERY REWRITE 옵션을 추가되어 있어야 합니다.  - MView를 생성한 유저는 반드시 QUERY REWRITE의 시스템 권한이 있어야 합니다. -- sysdba 권한으로 접속 합니다. SQL>sys/manager@oracle as sysdba -- QUERY_REWRITE_ENABLED를 TRUE로 변경 합니다. SQL>ALTER SYSTEM SET QUERY_REWRITE_ENABLED=’TRUE’; 시스템이 변경되었습니다. -- 테스트 유저로 접속 합니다. SQL>conn scott/tiger@oracle -- 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 정식 교재