출처 : http://www.okprogramming.com/1931  

 

위 출처로 가면 자료가 참 많네요.


 


◈  Materialized View란 ?

Materialized View(이하 MView로 표시) 이것은 제목 그대로 View 입니다.
 
하지만 일반 View는 논리적인 테이블이고, MView는 물리적으로 존재하는 테이블 입니다.
물리적으로 존재한다는 것은 Data가 일정 공간을 차지하고 있다는 거죠.. 
 
MView는 어떤 결과를 뽑아 내는 쿼리가 너무나도 빈번히 사용 될 경우, Query 실행 시간의 수행속도
향상을위하여 , 여러 가지의 Aggregate View를 두어, 미리 비용이 많이 드는 조인이나,
Aggregate Operation 을 처리하여야 하는 SQL을 위해, 데이터베이스의 한 테이블로 저장 하며,
그 테이블을 조회 하도록 하는 것 입니다.


간단하게 설명하면 대용량의 데이터를 SUM, MIN, MAX, AVG, COUNT(*)이런 명령어를 사용해
너무나도 자주 조회하는 Query를 수행속도를 향상을 위해서,  Query의 결과 만큼의 새로운 테이블을
생성해 놓는 벙법 입니다.

자주사용되는 View의 결과를 디스크에 저장해서 Query 속도를 향상시키는 개념 이죠.







◈ Materialized View의 특징

 - MView를 만들어두면 QUERY의 수행속도를 증가 시킬 수 있습니다.

 - SQL 응용프로그램에서 MView 사용시 DBA는 프로그램에 영향을 끼치지 않고 언제든지 생성 및
    제거가 가능 합니다.

 - MView는 실행의 결과 행과 뷰 정의 모두 저장이 되고, 실행 결과 행으로 만들어진 테이블은 일정
   공간을 차지 합니다.

 - MView관련 기초 테이블을 변경하면, MView로 생성된 Summary 테이블도 변경 되어 집니다.







◈ Materialized View와 일반 View의 차이점

 - 가장 큰 차이점은 MView의 결과값은 물리적으로 존재하는 것이고,
   일반 View의 결과값은 물리적으로 존재하지 않습니다.

   즉 SELECT * FROM USER_SEGMENTS 하면 MView는 나오지만 일반 View는 나오지 않습니다.

 - MView는 MView를 생성할때의 Query로 물리적으로 이미 데이타가 생성되어 있기 때문에
   조회 속도가 빠릅니다.   
   하지만 View는 단지 쿼리정보가 딕셔너리에 저장되어 있고 사용될때 그 SQL이 다시 실행되는
   것이기 때문에 MView보다 느립니다.

   MView로 생성된 결과값이 일반 View로 조회하는 Data의 결과값 보다 훨씬 적은 Row를 조회하게 되죠.







◈  MView 관련 파라미터

   - OPTIMIZER_MODE
     MView를 사용하기 위해서는 Cost-Based 옵티마이져 여야 하므로 ALL_ROWS, CHOOSE,
     혹은 FIRST_ROWS 중의 어느 하나를 사용 합니다.
    "CHOOSE"인 상태에서는 모든 테이블을 ANALYZE 시켜 줘야 합니다.

   - QUERY_REWRITE_ENABLED :  Query Rewrite 사용을 위해서는 TRUE로 설정하면 됩니다.

   - QUERY_REWRITE_INTEGRITY : 오라클이 Query Rewrite의 정확성을 제어하는 파라미터로,
     "STALE_TOLERATED", "TRUSTED", "ENFORCED" 로 지정할 수 있습니다.

      STALE_TOLERATED : 사용되어진 기초테이블과 Consistent 하지 않은 View를 이용한
                                      Query Rewrite를 허용 합니다

     TRUSTED : Optimizer에서 MView의 데이터가 정확하다고 간주하고 질의 수행.
                      Integrity 확인을 하지 않습니다.

     ENFORCED: QUERY_REWRITE_INTEGRITY 의 기본값으로, 사용자가 Integrity Constraint를
                       확인하여야 합니다.

   - COMPATIBLE :  사용할 수 있는 오라클 함수들의 Compatibility를 결정하는 값으로 8.1.0 또는
      그 이상으로 설정 해야 합니다.


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







◈ MView의 생성

MView를 생성하고 테스트 하기 위해서는,  sysdba에서 Query Rewrite권한과  
CREATE MATERIALIZED VIEW 권한을 MView를 생성하는 유저에게 부여해야 합니다.


-- sysdba 권한으로 접속 합니다.
SQL> conn sys/manager as sysdba
연결되었습니다.
 
 
-- QUERY REWRITE 권한을 부여 합니다.
SQL> GRANT QUERY REWRITE TO SCOTT;
권한이 부여되었습니다.
 
 
-- CREATE MATERIALIZED VIEW 권한을 부여 합니다.
SQL> GRANT CREATE MATERIALIZED VIEW TO SCOTT;
권한이 부여되었습니다.
 
 
-- MATERIALIZED VIEW를 생성할 유저로 접속 합니다.
SQL> conn scott/tiger
연결되었습니다.
 
 
-- MATERIALIZED VIEW 생성
SQL> CREATE MATERIALIZED VIEW dept_sal
     -- PCTFREE 0 TABLESPACE mviews
     -- STORAGE (initial 16k next 16k pctincrease 0)
     BUILD IMMEDIATE -- BUILD IMMEDIATE, BUILD DEFERRED 선택.
     REFRESH
     COMPLETE       -- FORCE, COMPLETE, FAST, NEVER 선택.
     ON DEMAND      -- ON DEMAND, ON COMMIT 선택.
     ENABLE QUERY REWRITE
     AS
     SELECT SUM(a.sal), a.deptno
     FROM emp a, dept b
     WHERE a.deptno = b.deptno
     GROUP BY a.deptno;
구체화된 뷰가 생성되었습니다.
 
 
-- MATERIALIZED VIEW 조회
SQL> SELECT * FROM DEPT_SAL;
 
SUM(A.SAL)     DEPTNO
---------- ----------
      8750         10
     10875         20
      9400         30







 ◈ 위에서 생성한 CREATE MATERIALIZED VIEW 구문을 살펴 보겠습니다.


 - BUILD IMMEDIATE : MView 생성과 동시에 데이터들도 생성되는 옵션입니다.. 

 - BUILD DEFERRED : MView를 생성은 하지만, 그 안의 데이터는 추후에 생성하도록 하는 기능 입니다.
   .위에 MView 생성시 BUILD IMMEDIATE 대신 BUILD DEFERRED 옵션을 사용하면 조회된
     데이터가 없겠죠.. 


 - REFRESH 절은 오라클이 MView의 데이터를 언제, 어떻게 Refresh 하는지를 결정 하는 방법입니다.
   .Refresh 방법에는 ON COMMIT 방법과, ON DEMAND 방법 2 가지가 있습니다.
 
   .ON COMMIT 은 기초 테이블에 Commit 이 일어날 때 Refresh 가 일어나는 방안이며,
    이는 1 개의 테이블에 COUNT(*), SUM(*)과 같은 집합 함수를 사용하거나, MView에 조인만이
    있는 경우,  Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만
    사용이 가능 합니다.
 
   .ON DEMAND는 사용자가 DBMS_MVIEW 패키지 (REFRESH, REFRESH_ALL_MVIEWS,
    REFRESH_DEPENDENT) 를 실행 한 경우 Refresh 되는 경우 입니다.

 
 - Refresh를 하는 방법에는 FORCE, COMPLETE, FAST, NEVER의 4가지가 존재 합니다.

   .COMPLETE : MView의 정의에 따라 MView의 데이터 전체가 Refresh 되는 것으로
                          ATOMIC_REFRESH=TRUE와 COMPLETE으로 설정한 경우 입니다.

   .FAST : 새로운 데이터가 삽입될 때마다 점진적으로 Refresh 되는 방안으로 Direct Path나
                Mview log를 이용 합니다.

   .FORCE : 이 경우 먼저 Fast Refresh가 가능한지 점검 후 가능하면 이를 적용하고,
                  아니면 Complete Refresh를 적용 합니다.(디폴트)

   .NEVER : MView의 Refresh를 발생시키지 않습니다
 
 
 - ENABLE QUERY REWRITE : MView 생성시 이 옵션을 주어야만 임의의 SQL문장을을 처리시
        Query Rewrite를 고려 합니다.
   만일 MView 생성시 이를 지정하지 않은 경우는 ALTER MATERIALIZED VIEW를 이용하여
   수정하면 됩니다.
 
 - 마지막으로 일반 View나 Snapshot처럼 AS 구문 뒤에 필요한 컬럼과 조건들을 기술 하면 됩니다



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


 







◈ 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 정식 교재