Mview, (이전에는 snapshot으로 불린)는 로컬 또는 원격 테이블에 대해 쿼리를 기반으로 내용이 주기적으로 고쳐지는 테이블 세그먼트입니다. 쿼리형태로 저장된 뷰와는 달리 데이터가 직접 저장되는 뷰의 형태입니다.
데이터 웨어하우스(DW)환경에서 유용하게 사용할 수 있습니다.
그림1. MVIEW 기본 매커니즘
출처 : https://oracle-base.com/articles/misc/materialized-views
위의 그림은 MVIEW에 대해서 잘 설명해 준 그림입니다.
구문
-- Normal
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;
-- Pre-Built
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;
구문에 대한 설명은 아래와 같습니다.
BUILD
- IMMEDIATE : MVIEW가 즉시 데이터가 채워집니다.
- DEFFERED : 처음 요청된 refresh 될때 MVIEW가 데이터가 채워집니다.
REFRESH
-FAST : 빠른 새로고침을 시도합니다. 미리 원본테이블에 대해 MVIEW LOG가 없으면 생성에 실패합니다.
- COMPLETE : MVIEW를 지원하는 테이블세그먼트는 truncate되고 연결된 쿼리로 완전히 새롭게 데이터가 채워집니다.
- FORCE : 빠른 새로고침을 시도합니다. 가능하지 않으면 complete refresh를 시도합니다.
REFRESH는 두가지 중 하나로 트리거 할 수 있습니다
- ON COMMIT : 종속된 테이블 중 하나에서 데이터 변경 후 commit에 의해서 refresh가 트리거로 됩니다.
- ON DEMAND : refresh는 수동요청 or 예약된 작업에 의해 시작됩니다.
QUERY REWRITE 절은 옵티마이저에 쿼리재작성 작업을 위해 MVIEW가 고려되어야 하는지를 알려줍니다. 쿼리 재작성은 그림2를 참고하여 매커니즘을 확인합니다.
ON PREBUILT TABLE절은 데이터베이스가 기존 테이블 세그먼트를 사용하도록 알려줍니다.기존 테이블 세그먼트는 MVIEW와 이름이 동일해야 하며 쿼리와 동일한 열구조를 지원해야합니다.
그림2. 쿼리 재작성(Transparent Query Rewrite)
1.먼저 사용자가 쿼리를 입력합니다.
2.그런 다음 오라클이 쿼리에 대한 플랜을 생성합니다.
3.그런 다음 오라클이 쿼리를 재작성 후 플랜을 생성합니다.
4.그런 다음 Oracle은 두 문장의 비용을 비교합니다.
5.오라클은 최선의 결과를 선택합니다.
테스트
1) 같은 DB의 다른스키마의 테이블(DEV.EMP) DB LINK를 이용하여 YSBAE 스키마에 MVIEW를 생성합니다.
SQL> SHOW USER
USER is "YSBAE"
SQL> CREATE DATABASE LINK YSBAE.DEV CONNECT TO DEV IDENTIFIED BY "비밀번호" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.33.3.161)(PORT=1530)) (CONNECT_DATA=(SERVICE_NAME=dbarac)))';
SQL> CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
select * From emp@YSBAE.DEV;
Materialized view created.
생성했을때 관련 뷰 확인(DBA_OBJECTS,DBA_MVIEWS,DBA_MVIEW_LOGS,DBA_SUMMARIES)
###
SQL> select owner,object_name,object_id,object_type From dba_objects where object_name='EMP_MV';
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------ --------------- --------- -----------------------
YSBAE EMP_MV 257794 TABLE
YSBAE EMP_MV 257795 MATERIALIZED VIEW
###
SQL> select * from dba_mviews
where owner='YSBAE';
OWNER MVIEW_NAME CONTAINER_NAME QUERY MASTER_LINK REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE LAST_REFRESH_TYPE LAST_REFRESH_DATE LAST_REFRESH_END_TIME STALENESS AFTER_FAST_REFRESH UNKNOWN_PREBUILT UNKNOWN_PLSQL_FUNC UNKNOWN_EXTERNAL_TABLE UNKNOWN_CONSIDER_FRESH UNKNOWN_IMPORT UNKNOWN_TRUSTED_FD COMPILE_STATE USE_NO_INDEX STALE_SINCE NUM_PCT_TABLES NUM_FRESH_PCT_REGIONS NUM_STALE_PCT_REGIONS SEGMENT_CREATED EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING DEFAULT_COLLATION ON_QUERY_COMPUTATION
------- ----------- --------------- -------- -------------- --------------- ------------------ ------------ -------------- ---------- ------------------ ----------------- -------------------- --------------------- ------------------- ------------------- ---------------- ------------------ ---------------------- ---------------------- -------------- ------------------ ------------------- ------------ -------------------- -------------- --------------------- --------------------- --------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------------------
YSBAE EMP_MV EMP_MV @"YSBAE.DEV" N GENERAL DEMAND FORCE IMMEDIATE NO COMPLETE 2024/01/26 15:33:45 2024/01/26 15:33:45 UNDEFINED UNDEFINED N N N N N N VALID N 0 YES USING_NLS_COMP N
1 rows selected.
###
SQL> select * From dba_mview_logs
where log_owner='YSBAE';
0 rows selected.
###
SQL> select * From dba_summaries
where owner='YSBAE';
OWNER SUMMARY_NAME CONTAINER_OWNER CONTAINER_NAME LAST_REFRESH_SCN LAST_REFRESH_DATE REFRESH_METHOD SUMMARY FULLREFRESHTIM INCREFRESHTIM CONTAINS_VIEWS UNUSABLE RESTRICTED_SYNTAX INC_REFRESHABLE KNOWN_STALE QUERY_LEN
------- --------------- ----------------- ----------------- ---------------- -------------------- -------------- ------- -------------- ------------- -------------- -------- ----------------- --------------- ----------- ---------
YSBAE EMP_MV YSBAE EMP_MV 9.4e+012 2024/01/26 15:33:45 ANY N 0 0 N N Y Y N 27
1 rows selected.또는 아래와 같이 prebuilt 테이블을 생성할 수 있습니다.
##컬럼구조와 이름이 MV와 동일하게 테이블 생성.
SQL> CREATE TABLE emp_mv AS
select * From emp@YSBAE.DEV;
Table created.
##MV 생성
SQL> CREATE MATERIALIZED VIEW emp_mv
ON PREBUILT TABLE
REFRESH FORCE
ON DEMAND
AS
select * From emp@YSBAE.DEV;
Materialized view created.
생성했을때 관련 뷰 확인(DBA_OBJECTS,DBA_MVIEWS,DBA_MVIEW_LOGS,DBA_SUMMARIES)
###
SQL> select * from dba_mviews
where owner='YSBAE';
OWNER MVIEW_NAME CONTAINER_NAME QUERY_LEN UPDATABLE UPDATE_LOG MASTER_LINK REWRITE_ENABLED REWRITE_CAPABILITY REFRESH_MODE REFRESH_METHOD BUILD_MODE FAST_REFRESHABLE LAST_REFRESH_TYPE LAST_REFRESH_DATE LAST_REFRESH_END_TIME STALENESS AFTER_FAST_REFRESH UNKNOWN_PREBUILT UNKNOWN_PLSQL_FUNC UNKNOWN_EXTERNAL_TABLE UNKNOWN_CONSIDER_FRESH UNKNOWN_IMPORT UNKNOWN_TRUSTED_FD COMPILE_STATE USE_NO_INDEX STALE_SINCE NUM_PCT_TABLES NUM_FRESH_PCT_REGIONS NUM_STALE_PCT_REGIONS SEGMENT_CREATED EVALUATION_EDITION UNUSABLE_BEFORE UNUSABLE_BEGINNING DEFAULT_COLLATION ON_QUERY_COMPUTATION
--------- ------------ ----------------- --------- --------- ----------- -------------- --------------- ------------------ ------------ -------------- ---------- ------------------ ----------------- -------------------- --------------------- ------------------- ------------------- ---------------- ------------------ ---------------------- ---------------------- -------------- ------------------ ------------------- ------------ -------------------- -------------- --------------------- --------------------- --------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- --------------------
YSBAE EMP_MV EMP_MV 27 N @"YSBAE.DEV" N GENERAL DEMAND FORCE PREBUILT NO NA 2024/01/26 15:44:56 UNDEFINED UNDEFINED Y N N N N N VALID N 0 YES USING_NLS_COMP N
1 rows selected.
###
SQL> select * From dba_mview_logs
where log_owner='YSBAE';
0 rows selected.
###
SQL> select * From dba_summaries
where owner='YSBAE';
OWNER SUMMARY_NAME CONTAINER_OWNER CONTAINER_NAME LAST_REFRESH_SCN LAST_REFRESH_DATE REFRESH_METHOD SUMMARY FULLREFRESHTIM INCREFRESHTIM CONTAINS_VIEWS UNUSABLE RESTRICTED_SYNTAX INC_REFRESHABLE KNOWN_STALE QUERY_LEN
----------- ---------------- ------------------- ----------------- ---------------- -------------------- -------------- ------- -------------- ------------- -------------- -------- ----------------- --------------- ----------- ---------
YSBAE EMP_MV YSBAE EMP_MV 9.4e+012 2024/01/26 15:44:56 ANY N 0 0 N N Y Y N 27
1 rows selected.
###
SQL> select owner,object_name,object_id,object_type From dba_objects where object_name='EMP_MV';
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------ --------------- --------- -----------------------
YSBAE EMP_MV 257794 TABLE
YSBAE EMP_MV 257795 MATERIALIZED VIEW
2 rows selected.
2) MVIEW log 생성
COMPLETE refresh는 MVIEW 세그먼트를 truncate하고 관련 쿼리를 사용하여 다시 데이터를 채우는 작업이 필요하므로 시간과 자원이 많이 소모될 수 있습니다. 해당 비용을 줄이기 위해 MVIEW LOG를 생성하여 마지막 refresh 이후 기본 테이블에 대한 모든 변경사항을 캡처할 수 있습니다. 이 정보를 사용하면 빠르게 refresh할 수 있으며 MVIEW의 Complete refresh보다 변경사항만 적용하면 됩니다. fast refresh를 적용하려면 마스터 인스턴스에 연결하여 MVIEW LOG를 생성합니다.
다른 스키마에 있는 경우 그 스키마의 user로 생성. 왜냐하면 해당 테이블의 변경사항을 refresh로 적용해야 하기 때문입니다.
SQL> CREATE MATERIALIZED VIEW LOG ON dev.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;
Statement Processed.
SQL> select log_owner,master,log_table,rowids,primary_key,include_new_values From dba_mview_logs
where log_owner='DEV';
LOG_OWNER MASTER LOG_TABLE ROWIDS PRIMARY_KEY INCLUDE_NEW_VALUES
-------------- --------- ------------ ------ ----------- ------------------
DEV EMP MLOG$_EMP NO YES YES
1 rows selected.
3)MVIEW Refresh 하기
MVIEW가 commit시 refresh되도록 구성된 경우 수동으로 refresh하게 할 필요는 없습니다.
commit 시 refresh는 기본테이블이 휘발성의 데이터인 경우 매우 강력한 작업입니다. 가능하다면 fast refresh를 사용하는 것이 좋습니다.
On demand refresh의 경우 MVIEW를 수동으로 refresh하거나 refresh group의 일부로 그것을 refresh하는것 중 선택할 수 있습니다.
매 분마다 refresh하도록 정의된 refresh group을 만들고 MVIEW를 할당합니다.
SQL>
BEGIN
DBMS_REFRESH.make(
name => 'YSBAE.MINUTE_REFRESH',
list => '',
next_date => SYSDATE,
interval => '/*1:Mins*/ SYSDATE + 1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/
PL/SQL procedure successfully completed.
SQL>
BEGIN
DBMS_REFRESH.add(
name => 'YSBAE.MINUTE_REFRESH',
list => 'YSBAE.EMP_MV',
lax => TRUE);
END;
/
PL/SQL procedure successfully completed.
해당 정보를 DBA_RGROUP 및 DBA_RCILD에서 조회할 수 있습니다.
SQL> select * FROM DBA_RGROUP;
REFGROUP OWNER NAME I P R ROLLBACK_S JOB PURGE_OPTION PARALLELISM HEAP_SIZE
-------- -------------------- ---------- - - - ---------- ---------- ------------ ----------- ----------
1 YSBAE MINUTE_REF N Y Y 41
RESH
SQL> select * from DBA_RCHILD;
REFGROUP OWNER NAME TYPE#
-------- -------------------- ----------
1 YSBAE EMP_MV SNAPSHOT
refresh group을 사용하는 대신 Oracle scheduler를 사용하여 DBMS_MVIEW.refresh 를 예약할 수 있습니다.
SQL> EXEC DBMS_MVIEW.refresh('EMP_MV');
PL/SQL procedure successfully completed.
4) MVIEW 삭제
##MVIEW 삭제
DROP MATERIALIZED VIEW ysbae.emp_mv;
DROP DATABASE LINK ysbae.dev;
##Refresh Group 삭제
BEGIN
DBMS_REFRESH.destroy(name => 'YSBAE.MINUTE_REFRESH');
END;
/
##MVIEW LOG 삭제
DROP MATERIALIZED VIEW LOG ON DEV.EMP;
5)집계함수와 변환
MVIEW를 사용하여 집계 및 변환을 수행하는 쿼리를 포함한 다양한 쿼리의 성능을 향상시킬 수 있습니다.
이를 통해 작업을 한번 생성하고 여러 세션에서 반복적으로 사용하여 서버의 총 부하를 줄일 수 있습니다.
EMP의 집계함수 쿼리의 실행계획을 확인합니다.
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL>
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
Execution Plan
----------------------------------------------------------
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)|
| 1 | HASH GROUP BY | | 3 | 21 | 4 (25)|
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)|
----------------------------------------------------------------
MVIEW를 만들고 "ENABLE QUERY REWRITE" 구문을 추가합니다.
그리고 동일한 쿼리가 rewrite되어서 세션이 직접 작업을 수행하는 대신 MVIEW를 활용하게 됩니다.
SQL>
CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT deptno, SUM(sal) AS sal_by_dept
FROM emp
GROUP BY deptno;
SQL>
EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');
SQL>
SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno;
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 24 | 3 (0)|
| 1 | MAT_VIEW REWRITE ACCESS FULL| EMP_AGGR_MV | 3 | 24 | 3 (0)|
---------------------------------------------------------------------------------
고려사항
- MVIEW의 데이터를 채우는것은 원본 테이블과 MVIEW쪽 대상 테이블 모두에 부하를 추가하게 됩니다.
원본쪽은 데이터를 캡처하기 위해서 쿼리되고 이는 대상 테이블에 삽입됩니다. 추가부하를 잘 확인해야 합니다
- MVIEW LOG는 refresh 성능을 향상시키지만 원본 테이블의 DML 수행에 필요한 작업을 증가시킵니다.
- 정기적으로 refresh하지않으면 MVIEW LOG가 매우 크게 증가하게 됩니다.
- Oracle 버전 및 관련쿼리의 복잡성에 따라 fast refresh사용이 불가할 수 있습니다.
- 변환 및 집계의 성능을 향상시키기 위해 MVIEW를 사용하는 경우QUERY_REWRITE_INTEGRITY 와 QUERY_REWRITE_ENABLED 파라미터를 반드시 설정하지 않으면 서버가 자동으로 쿼리 재작성의 이점을 활용할 수 없습니다.
--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
실제 운영에서는 한번도 못본 MVIEW에 대한 정리입니다.
잘만 활용한다면 좋은 오브젝트일거 같네요.
'Oracle > 운영' 카테고리의 다른 글
| DBMS_LOB (LOB 관련 정보 추출 패키지) (0) | 2024.05.21 |
|---|---|
| MView - 12c New features (Realtime MView) (0) | 2024.01.30 |
| [통계정보 export/import]다른 테이블에 통계정보 복사 (0) | 2023.12.14 |
| 통계정보 restore 하기 (0) | 2023.10.05 |
| ddl_lock_timeout 과 ORA-00054 : resource busy (0) | 2023.07.24 |
댓글