MView의 생성 예제

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

 

-- sysdba 권한으로 접속 합니다.
SQL> CONN / 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
    

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