[Oracle] Materialized View 정리

Materialized View란?

• 관계형 데이터베이스에서 view를 가상의 테이블이라고 한다. 따라서 인덱스를 잡을 수 없다.
• MVIEW라고 불리는 물리 뷰(구체화 뷰)의 용도는 그룹함수 min, max, sum, avg 등의 값을 미리 만들어 놓을 때 유용하며, user_segments에서 확인이 가능하다.
• 구체화 뷰는 기존 뷰와 비슷하지만, 차이가 나는 부분은 실제 데이터를 자신이 가지고 있으며, 원본 테이블에 INSERT, UPDATE, DELETE가 발생하면 새로운 데이터를 구체화된 뷰에 반영한다.
• REWRITE 힌트 구문에 구체화뷰가 인자로 와도 되고 안 와도 된다. 인자로 뷰 리스트를 주지 않는 경우 적절한 materialized view를 찾고 항상 비용과 관계없이 사용한다.
• Materialized views를 DW에서 집계 데이터등을 추출할 때 쿼리 수행속도를 빠르게 해주기 위해 데이터를 뷰에서 미리 가지고 오는 것인데, 주로 그룹함수 튜닝에 이용된다.

CREATE MATERIALIZED VIEW VIEW_NAME
BUILD IMMIDIATE[DEFERRED] REFRESH [on demand | on commit] [FAST | COMPLTE | FORCE | NEVER]
ENABLE QUERY REWRITE
AS
[SELECT 문장];

• Materialized View는 데이터를 갖고 있는 View이다. 마찬가지로 위의 문제를 해결하기 위해 View에 대한 결과를 갖고 있어, 쿼리문을 실행했을 때 빠른 속도로 결과를 받을 수 있다.
• Summary Table을 이용하는 것과 다르게, **View Log** 라는 오브젝트를 이용해서 동기화 작업을 한다.

문법 설명

• BUILD IMMEDIATE - MView 생성과 동시에 데이터들도 생성되는 옵션
• BUILD DEFERRED - MView 생성은 하지만, 그 안의 데이터는 추후에 생성하도록 하는 기능
• 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된다.
• [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로 부터 권한을 받아야한다.

CREATE MATERIALIZED VIEW dept_sal
	BUILD IMMEDIATE
	REFRESH COMPLTE
	ON DEMADN
		START WITH SYSDATE NEXT SYSDATE + 1/24
	ENABLE QUERY REWRITE
	AS
	SELECT SUM(a.sal), a.deptno
	FROM emp a, dept b
	WHERE a.deptno = b.deptno
	GROUP BY a.deptno;

참고 자료

---

[Oracle] Materialized View 정리 및 Advisor 사용하여 쉽게 생성

[Oracle] MView (Materialized Views)