[Oracle] MView (Materialized Views)

[현상 확인]

 

1. tab_a와 tab_b 테이블로 view를 생성한다.

create view join_tabs as
(
    select col_x as col_z from tab_a
    union
    select col_y as col_z from tab_b
);

select * from join_tabs where col_z = 'BLAH';

[해결 방법]

 

-> Materialized Views를 사용한다.

 

Materialized Views란?

 

- 관계형 데이터 베이스에서 view를 가상의 테이블이라고 한다. 따라서 인덱스를 잡을 수 없다.

- MVIEW라고 불리는 물리 뷰(구체화 뷰)의 용도는 그룹함수 min, max, sum, avg 등의 값을 미리 만들어 놓을 때 유용하며 user_segments에서 확인 가능하다.

- 오라클 8i 이후 새롭게 추가된 구체화 뷰 (MATERIALIZED VIEW) 는 기존 뷰와 비슷하지만 차이가 나는 부분은 실제 데이터를 자신이 가지고 있으며 원본 테이블에 INSERT, UPDATE, DELETE가 발생하면 새로운 데이터를 구체화된 뷰에 반영된다.

- REWRITE 힌트 구문에 구체화뷰가 인자로 와도 되고 안 와도 된다. 인자로 뷰 리스트를 주지 않는 경우 적절한 materialized view를 찾고 항상 비용(COST)과 관계없이 사용 한다.

-  Materialized views는 DW(Data Warehouse)에서 집계 데이터 등을 추출할 때 쿼리 수행속도를 빠르게 해주기 위해 데이터를 뷰에서 미리 가지고 있는 것인데 주로 그룹함수 튜닝에 이용된다.

 

[기본 문법]

CREATE MATERIALIZED VIEW VIEW_NAME
BUILD IMMEDIATE[DEFERRED] REFRESH [ FAST ]
                                          [ COMPLETE ]
                                          [ FORCE ]
                                          [ NEVER ]
ENABLE QUERY REWRITE
AS
[SELECT문장];
 

 

[문법 설명]

 

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

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

- BUILD IMMEDIATE REFRESH : 구체화된 뷰가 생성되자마자 바로 실행 가능한 상태로 된다. REFRESH 절은 아래에 정의된 AS절에서 사용된 SELECT문 내의 원본 테이블의 데이터가 변경되면 구체화된 뷰를 언제 변경할 것인지에 대한 일정을 결정한다.

 

    - ON COMMIT : 기초 테이블에 Commit 이 일어날 때 Refresh 가 일어나는 방안이며, 

                  이는 1 개의 테이블에 COUNT(*), SUM(*)과 같은 집합 함수를 사용하거나, 

                  MView에 조인만이 있는 경우,  Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만 사용 가능하다.

    - ON DEMAND : DBMS_MVIEW 패키지 (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)를 실행 한 경우 

                  Refresh 되는 경우 or start_with next구에 의해 REFRESH된다.

 

 

    -- MVIEW이름이 MV_EMP라고 할 때, C는 COMPLETE REFRESH를 의미한다.

 

    예) execute dbms_mview.refresh( list =>'MV_EMP', method =>'C');

 

-  [FAST] : 원본 테이블에 변경된 데이터만 구체화 뷰에 갱신한다. 

            FAST REFRESH가 동작하기 위해서는 뷰의 마스터 테이블이 materialized view log 가 있어야 한다.

 

   CREATE MATERIALIZED VIEW LOG ON emp  WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES;

 

- [COMPLETE] : 원본 테이블이 변경되면 전부 갱신한다.

- [FORCE] : FAST와 동일한 기능을 한다.

- [NEVER] : 원본테이블이 갱신되어도 뷰에 반영하지 않는다.

- ENABLE QUERY REWRITE : 일반 USER가 작성한 SQL 문이 구체화 된 뷰를 통해 데이터를 검색하는 것이 더 빨리 데이터를 찾을 수 있다고 분석되면 

                         사용자의 SQL문을 구체화 뷰를 통해 검색하게 하는 기능이다.

- 마지막으로 일반 View나 Snapshot처럼 AS 구문 뒤에 필요한 컬럼과 조건들로 SELECT문을 기술 하면 된다

 

MVIEW 를 만들려먼 먼저 DBA로 부터 권한을 받아야 한다.

 

[뷰 생성 권한 받기]

SQL>CONNECT / AS SYSDBA

SQL>GRANT CREATE MATERIALIZED VIEW TO SCOTT;  //scott사용자에게 mview만드는 권한을 준다.

SQL>GRANT ALTER ANY MATERIALIZED VIEW TO scott;

SQL>GRANT QUERY REWRITE TO scott;

 

[예]

 

MView를 생성하고 테스트 하기 위해서는,  sysdba에서 Query Rewrite권한과  

CREATE MATERIALIZED VIEW 권한을 MView를 생성하는 유저에게 부여해야 합니다.

 

 

-- sysdba 접속 

SQL> conn / as sysdba

연결되었습니다.

 

 

 

SQL> GRANT QUERY REWRITE TO SCOTT;

권한이 부여되었습니다.

 

 

SQL> GRANT CREATE MATERIALIZED VIEW TO SCOTT;

권한이 부여되었습니다.

 

SQL> conn scott/tiger

연결되었습니다.

 

 

-- MATERIALIZED VIEW 생성

CREATE MATERIALIZED VIEW dept_sal
 
     BUILD IMMEDIATE                       -- MVIEW만들때 데이터 생성하라
 
     REFRESH
 
     COMPLETE                              -- 갱신시 전부 갱신 
 
     ON DEMAND                             -- DBMS_MVIEW패키지에서 REFRESH명령시 또는주기마다 갱신
 
       START WITH SYSDATE NEXT SYSDATE + 1/24  -- 1시간마다 전부 갱신
 
     ENABLE QUERY REWRITE
 
     AS
 
     SELECT SUM(a.sal), a.deptno
 
     FROM emp a, dept b
 
     WHERE a.deptno = b.deptno
 
     GROUP BY a.deptno;

 

구체화된 뷰가 생성되었습니다.

 

SQL> SELECT * FROM DEPT_SAL;

 

SUM(A.SAL)     DEPTNO

---------- ----------

      8850         10

     13875         20

      9334         30

 

 

참고문서

 

https://stackoverflow.com/questions/6531564/index-on-view-oracle

http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecOracle&wr_id=220

http://ojc.asia/bbs/board.php?bo_table=LecOracle&wr_id=231