4-1 Database Management - Materialized Views to improve rewrite and refresh performance

/* Materialized View는 local 혹은 마스터 테이블이라고 불리는 remote 테이블, 뷰 혹은 Materialized View를 이용한 쿼리의 결과값을 포함하는 오브젝트이다. Read Only, Updatable, Writable 타입이 존재한다. Read Only인 Materialized View는 update되지 않으며 이 뷰에 대해 어떤 DML도 수행되지 않는다. Materialized View를 Updatable하도록 하려면, FOR UPDATE 구문이 사용된다. 그러면 refresh가 발생될 때마다 Materialized View의 변화가 마스터 테이블에 적용될 것이다. 그러나, Writable Materialized View를 생성할 때, 모든 DML문들은 View가 refresh될 때 마스터 테이블에 대해 데이터 변화를 적용하지 않으며, 모든 변화는 lost된다. Updatable모드를 사용하려면 Materialized View Log를 사용해야 한다. */

 

/* UNIQUE 혹은 DISTINCT 키워드를 사용한다면, MINUS, INTERSECT, UNION ALL, 집계(aggregate) 함수, Materialized View는 Complex Materialized View로 여겨진다. 마스터 테이블이 변하면, Materialized View도 refreshed updated될 필요가 있다. 이 프로세스를 Materialized View Refresh라고 하는데, Oracle에서는 두 가지 Refresh Mode를 지원한다. ON COMMIT 혹은 ON DEMAND 모드를 지원한다. 만약 Materialzied View를 up-to-date 방식으로 유지하고 싶다면, ON COMMIT 옵션을 사용하면 각각 트랜잭션이 완료될 때마다 자동적으로 Materialized View에 데이터 변경이 적용될 것이다. ON DEMAND 옵션(default)를 사용하면 DBMS_MVIEW 패키지 사용에 따라 Materialized View가 Refresh될 것이다.

- DBMS_MVIEW.REFRESH는 하나 이상의 Materialized View를 refresh하기 위해 사용된다.

- DBMS_MVIEW.REFRESH_DEPENDENT 프로시저는 같은 마스터 테이블에 의존하는 모든 Materialized View를 refresh한다.

- DBMS_MVIEW.REFRESH_ALL_MVIEWS 프로시저는 모든 Materialized View를 refresh한다. */

 

/* Materialized View의 Refresh에는, 4가지 사용 가능한 옵션이 있다. 

1. FAST 옵션은 Materialized View Log가 미리 수집해 놓은 마스터 테이블의 변화를 모아 Materialized View에 적용한다. Materialized View Log가 활성화 되어있지 않다면, 이 옵션은 사용할 수 없다. 

2. COMPLETE 옵션은 현재 Materialized View를 일단 삭제하고, 다시 마스터 테이블로부터 Materialized View를 생성한다.

3. FORCE 옵션은 우선 FAST 옵션을 시도해보고, FAST 옵션을 사용할 수 없다면 COMPLETE 옵션으로 refresh를 진행한다.

4. NEVER 옵션은 Materialized View가 refresh되지 않도록 한다. */

 

/* BUILD 파라미터를 사용함으로써 Materialized View에 데이터를 채워 놓을 때를 정의할 수 있다. BUILD DEFFERED가 사용되었다면, Materialized View는 데이터를 포함하지 않고, 다음 refresh 프로세스가 실행될 때 데이터가 포함될 것이다. 한편 BUILD IMMEDIATE(default)를 사용했다면 Materialized View가 생성되자 마자 데이터가 추가될 것이다. */

 

/* Materialzied View Log는 마스터 테이블의 변화 복제 cost를 줄이기 위해 사용된다. Materialized View Log를 사용하면, FAST 모드의 Refresh를 할 수 있고, Materialized View의 변화를 마스터 테이블에 적용할 수도 있다. Fast Refresh Materialized View를 생성하려면, ROWID 구문이 사용되어야 한다. /

 

CREATE MATERIALIZED VIEW myview
AS
SELECT * FROM dba_objects WHERE rownum <= 100;

ALTER TABLE myview ADD PRIMARY KEY (OBJECT_NAME);

CREATE MATERIALIZED VIEW LOG ON myview
TABLESPACE users PURGE REPEAT INTERVAL '10' DAY;

 

CREATE MATERIALIZED VIEW mv_dba_objects2
REFRESH WITH PRIMARY KEY
AS SELECT * FROM dba_objects;

CREATE TABLE tbl_pr_mview (id NUMBER);


/* tbl_pr_mview 테이블에는 현재 PK 값이 없으므로, refresh with primary key for update를 사용하면 에러가 발생 */
CREATE MATERIALIZED VIEW mv_dba_objects3
REFRESH WITH PRIMARY KEY FOR UPDATE
AS SELECT * FROM tbl_pr_mview;

ALTER TABLE tbl_pr_mview ADD PRIMARY KEY (id);

CREATE MATERIALIZED VIEW mv_dba_objects3
REFRESH WITH PRIMARY KEY FOR UPDATE
AS SELECT * FROM tbl_pr_mview;

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 1:  간단한 형식의 Materialized View를 만들고 DBA_mview로 이것을 나타내어라. (5분)

 

CREATE MATERIALIZED VIEW mv_dba_objects
AS
SELECT * FROM dba_objects;

SELECT	count(1)
FROM	mv_dba_objects;

SELECT	owner, mview_name, query, updatable
FROM	dba_mviews
WHERE	mview_name = 'MV_DBA_OBJECTS';

 

 

 

 

 

난이도: ★★☆☆☆

문제 2:  Materialized View를 생성하고, View가 생성된 후 모든 row가 채워졌는지, 또 refresh 될 때마다 View가 truncate 되었는지 확인한다. (8분)

 

CREATE TABLE ryutable (id NUMBER);

CREATE MATERIALIZED VIEW ryuview
AS SELECT * FROM ryutable;
INSERT INTO ryutable VALUES (1);

SELECT * FROM ryuview;

EXEC DBMS_MVIEW.REFRESH('ryuview');
SELECT * FROM ryuview;

TRUNCATE TABLE ryuview;
SELECT * FROM ryuview;


/* 마스터 테이블 추가 시에는 Materialized View에 바로 적용이 되지 않으나, 삭제 시에는 바로 적용이 된다. */

 

 

 

 

 

난이도: ★★★★☆

문제 3:  Materialized View를 생성하고, refresh 후 마스터 테이블에 변화가 적용되었는지 확인한다. (8분)

CREAET TABLE ryutable1 (id NUMBER);
INSERT INTO ryutable1 VALUES (1);

ALTER TABLE ryutable1 ADD PRIMARY KEY (id);

CREATE MATERIALIZED VIEW LOG ON ryutable1;

CREATE MATERIALIZED VIEW ryuview
FOR UPDATE
AS
SELECT * FROM ryutable1;

COMMIT;

SELECT * FROM ryuview;
SELECT * FROM ryutable1;

EXEC DBMS_MVIEW_REFRESH('ryuview');
SELECT * FROM ryutable1;


/* master 테이블과 동기화(refresh) 시 Materialized View에 추가한 row가 사라진다. */

 

 

 

 

 

 

난이도: ★★☆☆☆

문제 4:  Materialized View Log를 생성하고, 3시간마다 purge할 수 있도록 한다. (6분)

 

/* sys유저로는 MATERIALIZED VIEW LOG를 생성할 수 없다. */ 
CONN c##ryu/asdf0930k;

CREATE TABLE ryudataa
AS
SELECT * FROM dba_objects WHERE rownum <= 100;

ALTER TABLE ryudataa ADD PRIMARY KEY (object_name);

CREATE MATERIALIZED VIEW LOG ON ryudataa
TABLESPACE users PURGE REPEAT INTERVAL '3' HOUR;

 

 

 

 

 

난이도: ★★☆☆☆

문제 5:  Materialized View를 생성하고, 병렬도 6으로 refresh 하도록 지정하여라. (6분)

 

CREATE MATERIALIZED VIEW ryudata
AS
SELECT * FROM dba_objects WHERE rownum <= 10;

EXEC DBMS_MVIEW.REFRESH(LIST=>'ryudata', PARALLELISM=> 6);

 

 

 

 

 

난이도: ★★★☆☆

문제 6:  Materialized View를 생성하고, 마스터 테이블에 변화가 생길 때마다 update 되는지 확인한다. (7분)

 

CREATE TABLE ryutable (id NUMBER);
INSERT INTO ryutable VALUES (1);
ALTER TABLE ryutable ADD PRIMARY KEY (id);

CREATE MATERIALIZED VIEW LOG ON ryutable;

CREATE MATERIALIZED VIEW ryusview
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT * FROM ryutable;

INSERT INTO ryutable VALUES (3);
COMMIT;

SELECT * FROM ryusview;

 

 

 

 

 

 

난이도: ★★★☆☆

문제 7: Materialized View를 생성하고, 7분마다 refresh하도록 한다. Refresh Job의 상태를 체크한다. 그리고 Auto Refresh Job을 비활성화시킨다. (10분)

 

CREATE TABLE mytable (id NUMBER);
INSERT INTO mytable VALUES (1);
ALTER TABLE mytable ADD PRIMARY KEY (id);

CREATE MATERIALIZED VIEW LOG ON mytable;

CREATE MATERIALIZED VIEW myview1
REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + INTERVAL '7' MINUTE
AS
SELECT * FROM mytable;

SELECT	owner, job_name, start_date, end_date
FROM	dba_scheduler_jobs
WHERE	owner = 'C##RYU';

ALTER MATERIALIZED VIEW myview1
REFRESH FAST NEXT SYSDATE + 1;

SELECT	owner, job_name, start_date, end_date
FROM	dba_scheduler_jobs
WHERE	owner = 'C##RYU';

EXEC DBMS_SCHEDULER.DISABLE('MV_RF$J_0_S_42');

 

 

 

 

 

난이도: ★★★★☆

문제 8: DBA_OBJECTS와 DBA_EXTENTS 뷰를 토대로 두 테이블을 작성한다. 이 두 테이블을 조인하고 실행계획을 확인해라. 그리고, 같은 쿼리에 기반한 Materialized View를 생성하고 다음 쿼리 실행 시에는 옵티마이저가 Materialized View만을 이용하여 쿼리를 수행하는 것을 확인하라. 실행계획과 실행 시간을 확인하라. (12분)

 

CREATE TABLE tbl_mv_objects
AS
SELECT	*
FROM	dba_objects;

CREATE TABLE tbl_mv_extents
AS
SELECT	*
FROM	dba_extents;

SET AUTOTRACE TRACEONLY EXP
SET LINESIZE 150

SELECT	a.object_name, COUNT(extent_id)
FROM	tbl_mv_objects a, tbl_mv_extents b
WHERE	a.object_name = b.segment_name
GROUP BY a.object_name;

CREATE MATERIALIZED VIEW LOG ON tbl_mv_objects
WITH ROWID, SEQUENCE(object_name) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON tbl_mv_extents
WITH ROWID, SEQUENCE(extent_id, segment_name) INCLUDING NEW VALUES;


/* 쿼리 재작성 (내부 실행계획 변화) 허용 */
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;


CREATE MATERIALIZED VIEW mv_seg_ext
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS
SELECT	a.object_name, COUNT(extent_id)
FROM	tbl_mv_objects a, tbl_mv_extents b
WHERE	a.object_name = b.segment_name
GROUP BY a.object_name;

SELECT	a.object_name, COUNT(extent_id)
FROM	tbl_mv_objects a, tbl_mv_extents b
WHERE	a.object_name = b.segment_name
GROUP BY a.object_name;

 

 

 

 

 

 

난이도: ★★★★★

문제 9: DBMS_VIEW 패키지를 이용하여 쿼리 rewrite가 특정한 쿼리를 수행할 때 활성화 되는것을 확인하여라. (10분)

 

/* QUERY_REWRITE_ENABLED 파라미터가 켜져 있는지 확인한다. 꺼져 있으면, 기깔난 Materialized View를 만들어도 쿼리의 실행 계획 변화, 즉 해당 쿼리의 최적화를 진행할 수 없게 된다. */

 

 

/* DBMS_MVIEW.EXPLAIN_REWRITE 프로시저를 활용하면 query rewrite가 왜 실패했는지까지도 확인이 가능하다. */

ALTER SYSTEM SET QUERY_REWRITE_ENABLED=FALSE;

/* query rewrite 내역을 저장할 rewrite_table 생성 */
@/u01/app/oracle/product/21.3.0/dbhome_1/rdbms/admin/utlxrw.sql

EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE('SELECT a.object_name, COUNT(extent_id) FROM tbl_mv_objects a, tbl_mv_extents b WHERE a.object_name = b.segment_name GROUP BY a.object_name');

SELECT message FROM rewrite_table ORDER BY sequence;


TRUNCATE rewrite_table;
ALTER SYSTEM SET QUERY_REWRITE_ENABLED_TRUE;

EXECUTE DBMS_MVIEW.EXPLAIN_REWRITE('SELECT a.object_name, COUNT(extent_id) FROM tbl_mv_objects a, tbl_mv_extents b WHERE a.object_name = b.segment_name GROUP BY a.object_name');
SELECT message FROM rewrite_table ORDER BY sequence;