MView (Materialized View)

게시일: Dec 06, 2011 11:39:59 PM

- 실제 테이블에 있는 데이터를 조회한 결과를 독립된 스키마 오브젝트에 저장하여 이를 참조 가능하게 하는 뷰

- 기준 데이터가 날마자 계속 변경되어도 오라클이 특정 시점에 자동으로 새로운 데이터들을 집계해서 MView에 다시 저장해 줌.

- MView 생성을 위한 필요 조건

▷ 생성 권한

- 기준 테이블에 대한 SELECT 권한

- CREATE MATERIALIZED VIEW 권한 (테이블 생성하기 위해 CREATE TABLE 권한이 있어야 하듯)

- CREATE ANY MATERIALIZED VIEW 권한 (만약 자신의 스키마가 아닌 다른 스키마에 MView를 생성할 경우)

- QUERY REWRITE 권한 (주기적으로 새로운 데이터를 갱신해서 보관하기 위해)

☞ QUERY REWRITE ?

만약 옵티마이저가 MView를 쿼리하는데 있어 더 나은 방법을 찾기 되면, 옵티마이저는 SQL 쿼리를 다시 작성하게 된다.

바로 이를 ☞ QUERY REWRITE라 한다.

물론 QUERY REWRITE는 사용자가 작성한 SQL 문을 변경하는 것이 아니라 옵티마이저가 내부적으로 수행하는 쿼리를 변경하는 것이다.

▷ 파라미터

- QUERY_REWRITE_ENABLED : QUERY REWRITE 기능을 상요하기 위해 TRUE로 설정

- QUERY_REWRITE_INTEGRITY : QUERY REWRITE의 정확성을 제어하는 파라미터로 ENFORCED, TRUSTED, STALE TOLERATED 세 가지 중 한 가지 값을 가질 수 있다.

- MView 생성

사용자 : HR, 기준 테이블 : MY_MASTER_TABLE, MView : my_test_mv

▷ 권한 할당

GRANT CREATE MATERIALIZED VIEW TO HR;

GRANT QUERY REWRITE TO HR;

▷ 기준 테이블 생성

-- 임의로 건수가 많은 테이블을 만들기 위해 딕셔너리인 user_tab_columns 를 union all 해서 테이블 생성함. (그래도 건수가 얼마 안되네...)

CREATE TABLE my_master_table AS

SELECT '1' flag, table_name, data_type, data_length

FROM user_tab_columns

UNION ALL

SELECT '2' flag, table_name, data_type, data_length

FROM user_tab_columns

UNION ALL

SELECT '3' flag, table_name, data_type, data_length

FROM user_tab_columns;

▷ MView 생성

CREATE MATERIALIZED VIEW my_test_mv

BUILD IMMEDIATE

REFRESH ON COMMIT

ENABLE QUERY REWRITE

AS

SELECT flag flag

, COUNT(*) date_cnt

, SUM(data_length) data_len

FROM my_master_table

GROUP BY flag

ORDER BY flag;

☞ 참고

• • BUILD IMMEDIATE : MView 생성 시점에 데이터 수집

  • REFRESH ON COMMIT : COMMIT 이 실해오디는 시점에 MView에 신규 데이터 반영 (REFRESH 구분 : 새로운 데이터가 MView에 반영되는 시점 명시)

  • ENABLE QUERY REWRITE : 이 옵션을 주어야만 QUERY REWRITE 기능을 사용할 수 있다.

☞ 기준 테이블인 my_master_table 와 MView인 my_test_mv 의 SQL을 실행하여 수행 속도를 비교해보자

☞ 참고 : SQL*PLUS를 사용해서 수행시간 확인하기

SQL> SET TIMING ON;

SQL> SELECT 문장;

결과 뒤에 수행시간이 조회된다.