오라클 구체화 뷰(Materialized View), MVIEW

 

오라클 구체화 뷰(Materialized View), MVIEW

오라클 8i 이후부터 새롭게 추가된 구체화 뷰materialized view(MVIEW)는 기존 뷰와 비슷하지만 실제 데이터를 뷰 자신이 가지고 있으면서 원본 테이블에 INSERT, UPDATE, DELETE가 발생하면 새로운 데이터를 구체화된 뷰에도 반영 합니다.

집계함수(그룹함수, MIN, MAX, AVG, SUM, COUNT)는 모든 결과 데이터를 메모리에 올려 집계한 후 결과를 보이기 때문에 대용량 테이블에서는 성능이 좋지 않으며 별다른 쿼리튜닝 방법이 없습니다.

구체화 뷰는 DW(Data Warehouse)에서 집계 데이터 등을 추출할 때 쿼리 수행속도를 빠르게 해주기 위해 데이터를 뷰에서 미리 가지고 있는 것인데 주로 그룹함수 튜닝에 사용되며 USER_MVIEWS, USER_SEGMENTS, USER_OBJECTS 딕셔너리 뷰에서 확인 가능 합니다.

구체화뷰를 사용하기 위한 오라클 힌트 구문은 REWRITE인데 힌트 구문에 구체화뷰가 인자로 와도 되고 안 와도 된다. 인자로 뷰 리스트를 주지 않는 경우 적절한 MATERIALIZED VIEW를 찾고 항상 비용(COST)과 관계없이 사용 합니다.

오라클 13.2 이후에는 실시간 구체화 뷰real-time materialized view를 제공하는데, 이 실시간 구체화 뷰는 원본 데이터에 변경이일어나면 실제 MView가 갱신되지는 않았지만  MView의 로그를 이용하여 현재 SQL문에서 구체화 뷰가 갱신된 것 처럼 보이게 합니다. 이 방법은 MView의 기존 빠른 새로 고침과 유사하게 구체화 뷰 로그를 사용하여 수정된 변경 사항을 사용자가 작성하는 현재 SQL문에서 마치 뷰가 갱신된 것처럼 보이도록 합니다. 실제 변경사항이 구체화 뷰에는 반영되지 않았으므로 추후 구체화 뷰의 갱신은 필요합니다.

[기본형식]

CREATE MATERIALIZED VIEW view_name BUILD [IMMEDIATE | DEFERRED] REFRESH [ FAST | COMPLETE | FORCE | NEVER ] ON [COMMMT | DEMAND] [[ENABLE | DISABLE] QUERY REWRITE [ON PREBUILT TABLE]] [[ENABLE | DISABLE] ON QUERY COMPUTATION AS SELECT 문장; BUILD IMMEDIATE : MView 생성과 동시에 데이터들도 생성되는 옵션. BUILD DEFERRED : MView 생성은 하지만, 그 안의 데이터는 추후에 생성하도록 하는 기능. MView 생성시 BUILD IMMEDIATE 대신 BUILD DEFERRED 옵션을 사용하면 조회되는 데이터가 없습니다. BUILD IMMEDIATE REFRESH : 구체화된 뷰가 생성되자마자 바로 실행 가능한 상태로 됩니다. REFRESH 절은 아래에 정의된 AS절에서 사용된 SELECT문 내의 원본 테이블 데이터가 변경되면 구체화된 뷰를 언제 변경할 것인지에 대한 일정을 결정을 합니다. REFRESH하는 방법은 아래 4가지 입니다. FAST : 원본 테이블에 변경된 데이터만 구체화 뷰에 갱신한다. FAST REFRESH가 동작하기 위해서는 뷰의 마스터 테이블에MATERIALIZED VIEW LOG가 있어야 한다. CREATE MATERIALIZED VIEW LOG ON emp  WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES; COMPLETE : 원본테이블이 변경되면 전부 갱신 합니다. FORCE : FAST와 동일한 기능을 합니다. NEVER : 원본테이블이 갱신되어도 뷰에 반영하지 않습니다. ON COMMIT : 원본 테이블에 COMMIT이 일어날 때 REFRESH가 일어나는 것입니다. 이는 1 개의 테이블에 COUNT(*), SUM(*)과 같은 집합 함수를 사용하거나, MVIEW에 조인만이 있는 경우, Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우에만 사용 가능 합니다. ON DEMAND : MView가 자동으로 갱신되지 않으며 DBMS_MVIEW 패키지(REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)의 프로시저를 실행해서 MView를 갱신 합니다.  -- MVIEW이름이 MV_EMP라고 할 때, C는 COMPLETE REFRESH를 의미한다. 예) execute dbms_mview.refresh( list =>'MV_EMP', method =>'C'); ENABLE QUERY REWRITE : 일반 USER가 작성한 SQL 문이 구체화 된 뷰를 통해 데이터를 검색하는 것이 데이터를 더 빨리 찾을 수 있다고 분석되면 사용자의 SQL문을 구체화 뷰를 통해 검색하게 하는 기능 입니다. ENABLE ON QUERY COMPUTATION : 오라클 13.2 이상에서 사용되며 실시간 MView를 위해서는 반드시 사용해야 합니다. MView를 별도로 갱신하지 않더라도 사용자의 SQL에서 MView가 마치 갱신된 것 처럼 변경된 내용을 확인할 수 있습니다. ON PREBUILT TABLE : CREATE TABLE AS SELECT…(CTAS)로 이미 테이블이 생성된 경우 이를 QUERY REWRITE가 가능하도록 MVIEW로 만드는 옵션 입니다. 마지막으로 일반 View 처럼 AS 구문 뒤에 필요한 컬럼과 조건들로 SELECT문을 기술 합니다.

MYEMP 테이블에서 직무(job)별 사원수를 COUNT 하는 쿼리문을 job 인덱스를 사용하지 않고 실행을 한 후 인덱스를 사용하여 쿼리를 실행하여 성능을 확인해 보겠습니다. 

실습

실습에서 BASE TABLE로 사용될 MYEMP 테이블은 0.환경설정의 0.4 실습 데이터 설치편을 참조하여 생성 바랍니다. MYEMP 테이블의 전체 건수는 2천만건이며, empno 컬럼에 PK 인덱스만 생성되어 있습니다.

MYEMP 테이블의 컬럼에 생성되어 있는 있는 인덱스를 조회한 후 PK 인덱스를 제외하고 삭제하세요.

SELECT INDEX_NAME, INDEX_TYPE, VISIBILITY 

FROM    USER_INDEXES

WHERE TABLE_NAME = 'MYEMP';

<실행결과>

	INDEX_NAME	INDEX_TYPE	VISIBILITY
1	PK_MYEMP	EMPNO	VISIBLE

혹시 PK_MYEMP 인덱스외 다른 인덱스가 있으면 DROP INDEX 명령으로 삭제하세요.

MYEMP 테이블에서 직무(job)별로 사원수를 조회하세요.

SELECT JOB, COUNT(*) 

FROM    MYEMP

GROUP BY JOB;

<실행결과>

	JOB	COUNT(*)
1	PLANNER	2000000
2	TRAINEE	2000000
3	ANALYST	4000000
4	CLERK	4000000
5	SALESMAN	2000000
6	MANAGER	2000000
7	PROGRAMMER	2000000
8	DESIGNER	2000000

<실행계획>

MYEMP 테이블을 FULL SCAN하여 HASH GROUP BY 연산으로 8건의 데이터를 추출했습니다. 실행시간은 필자 노트북 기준으로 11초 정도 소요 되었습니다. HASH GROUP BY는 오라클 10.2 이전의 SORT GROUIP BY의 성능을 개선할 목적으로 도입되었습니다.

MYEMP 테이블의 job 컬럼에 인덱스를 생성하고 위 쿼리를 다시 실행해 보겠습니다.

MYEMP 테이블의 jon 컬럼에 대해 IDX_MYEMP_JOB 이름으로 인덱스를 생성하세요.

CREATE INDEX IDX_MYEMP_JOB ON MYEMP(JOB);

<실행결과>

Index IDX_MYEMP_JOB이(가) 생성되었습니다.

생성한 IDX_MYEMP_JOB 인덱스를 사용하기 위해 WHERE 절에 job 컬럼을 출현시켰습니다. 이 부분이 빠지게 되면 생성한 인덱스를 이용하지 않을 확률이 높습니다.

MYEMP 테이블에서 직무(job)별로 사원수를 조회하세요.

SELECT JOB, COUNT(*) 

FROM MYEMP

WHERE JOB > 'A'

GROUP BY JOB;

<실행결과>

결과는 앞 쿼리와 동일 합니다.

<실행계획>

IDX_MYEMP_JOB 인덱스를 경유했으며 수행 시간은 2.6초 정도 소요되었습니다. job 컬럼의 인덱스를 경유하도록 했지만 성능이 획기적으로 개선되지는 않았습니다. 

구체화뷰(Mview)를 위해 MView Log를 만들고,  M_COUNT MView를 만들고 실제 데이터를 입력해서 변동 사항이 조회되는 것을 확인하고 보겠습니다.

실습

구체화 뷰의 변경을 기록하는 MView Log를 생성 합니다.

MYEMP 테이블에서 job 컬럼에 대해 MView를 위한 로그를 생성하세요. 이미 생성되어 있다면 DROP MATERIALIZED VIEW LOG ON MYEMP 명령으로 삭제 후 생성하세요.

CREATE MATERIALIZED VIEW LOG ON MYEMP

WITH ROWID, SEQUENCE(JOB) INCLUDING NEW VALUES;

<실행결과>

Materialized view log MYEMP이(가) 생성되었습니다.

MView를 생성 합니다.

MYEMP 테이블에서 직무(job)별로 사원수를 구하는 SELECT문으로M_COUNT라는 이름의 MView를 생성하세요. 이미 생성되어 있다면 DROP MATERIALIZED VIEW M_COUNT 명령으로 삭제 후 생성하세요.

CREATE MATERIALIZED VIEW M_COUNT

REFRESH FAST ON DEMAND  

ENABLE QUERY REWRITE 

ENABLE ON QUERY COMPUTATION

AS 

SELECT JOB, COUNT(*) 

FROM   MYEMP

GROUP BY JOB;

<실행결과>

Materialized view M_COUNT이(가) 생성되었습니다.

REFRESH FAST ON DEMAND 옵션은 원본 MYEMP 테이블이 변경되면 MView가 자동갱신되지 않고 DBMS_MVIEW 패키지의 REFRESH 프로시저를 실행시켜 MView를 갱신해야 한다는 의미 입니다.  

ENABLE QUERY REWRITE 옵션은 일반 USER가 작성한 SQL문이 생성한 MView를 통해 검색하는 것이 데이터를 더 빨리 찾을 수 있다고 판단되면 사용자의 SQL문을 MView를 통해 검색되도록 합니다.  

ENABLE ON QUERY COMPUTATION은 실시간 구체화 뷰로 생성되기 위한 옵션으로 오라클 13.2 이상에서 실시간 구체화 뷰 기능을 위해 사용되는데 MView를 별도로 갱신하지 않더라도 SELECT문이 MView Log을 통해  최신 데이터를 직접 쿼리 할 수 ​​있습니다. 즉 사용자의 SQL에서 MView가 마치 갱신된것 처럼 변경된 내용을 확인할 수 있습니다. 실제 MView가 갱신된 것은 아니고 MView Log를 통해서 갱신된 것 처럼 보이는 것 입니다.

먼저 QUERY_REWRITE_INTEGRITY 파라미터의 값을 확인해 보겠습니다. ENFORCED (기본값) 또는 TRUSTED로 설정된 경우에만 실시간 구체화 뷰를 사용할 수 있고 STALE_TOLERATED로 설정된 경우 사용할 수 없습니다.

QUERY_REWRITE_INTEGRITY 파라미터 값을 확인 하세요.

SHOW PARAMETER QUERY_REWRITE_INTEGRITY;

<실행결과>

NAME                            TYPE   VALUE    

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

query_rewrite_integrity  string   enforced 

SHOW PARAMETER 명령은 오라클 데이터베이스의 매개 변수 값을 확인하는 명령입니다. 기본값인 enforced 이므로 실시간 구체화 뷰 기능을 이용할 수 있습니다.

생성한 MView의 상태를 확인해 보겠습니다. USER_MVIEWS 딕셔너리 뷰는 사용자가 생성한 MView 정보를 제공합니다.

M_COUNT MView의 상태를 조회 하세요.

SELECT MVIEW_NAME, STALENESS, ON_QUERY_COMPUTATION

FROM   USER_MVIEWS;

<실행결과>

	MVIEW_NAME	STALENESS	ON_QUERY_COMPUTATION
1	M_COUNT	FRESH	Y

STALENESS 값은 FRESH, STALE, NEEDS COMPILE, UNUSABLE 4가지 값이 올 수 있는데, FRESH는 구체화 뷰가 원본 데이터와 동기화가 잘되어있다는 것을 의미하고, STALE은 원본 테이블의 데이터가 변경 되었는데 뷰가 갱신되지 않아서 구체화 뷰를 사용할 수 없음을 나타냅니다. NEEDS COMPILE은 MView가 컴파일이 필요한 상태라는 것을 나타내는데 DBMS_MVIEW.REFRESH 명령으로 MView를 갱신해야 합니다. UNUSABLE은 MView를 읽을 수 없는 상태라는 것을 나타냅니다. 

ON_QUERY_COMPUTATION 값은 ON QUERY COMPUTATION 상태를 나타내며 오라클 13.2 이후의 실시간 MView를 위해서는 반드시 Y로 설정되어 있어야 합니다. 이 값을 ‘Y’로 만들기 위해서는 MView 생성시 ENABLE ON QUERY COMPUTATION 옵션을 사용해야 합니다.

이전에 실행 한 SELECT ~ GROUP BY 구문을 다시 실행해 보겠습니다.

쿼리 성능을 확인해 보세요.

MYEMP 테이블에서 직무(job)별로 사원수를 조회하세요.

SELECT JOB, COUNT(*) 

FROM    MYEMP

GROUP BY JOB;

<실행결과>

	JOB	COUNT(*)
1	PLANNER	2000000
2	TRAINEE	2000000
3	ANALYST	4000000
4	CLERK	4000000
5	SALESMAN	2000000
6	MANAGER	2000000
7	PROGRAMMER	2000000
8	DESIGNER	2000000

<실행계획>

생성한 MView를 이용하여 쿼리를 실행했으며 실행 시간은 0.002초 정도 걸렸습니다. 쿼리 성능이 상당히 개선되었습니다. 이 처럼 MView는 그룹함수 튜닝의 핵심 입니다.

데이터 한건을 입력하고 MView를 조회해 보겠습니다.

MYEMP 테이블에 한 건을 입력하세요.직무(job)는 “MANAGER”로 입력 합니다.

INSERT INTO MYEMP (EMPNO, ENAME, DEPTNO, PAY, JOB, GENDER) VALUES ('99999999','홍길동','1',9999999,'MANAGER','M');

COMMIT;

<실행결과>

1 행 이(가) 삽입되었습니다.

INSERT 후 Mview는 갱신되지 않았고 MView Log는 입력에 대한 로깅을 한 상태 입니다.

MView를 직접 조회해 보겠습니다.

M_COUNT 뷰를 조회하세요.

SELECT * FROM M_COUNT;

<실행결과>

	JOB	COUNT(*)
1	PLANNER	2000000
2	TRAINEE	2000000
3	ANALYST	4000000
4	CLERK	4000000
5	SALESMAN	2000000
6	MANAGER	2000000
7	PROGRAMMER	2000000
8	DESIGNER	2000000

입력된 데이터가 반영되지 않았습니다.job이 “MANAGER”인 사원수가 그대로 2,000,000 입니다.

이번에는 SELECT ~ GROUP BY 구문을 실행하여 방금 입력한 데이터가 반영되었는지 확인해 보겠습니다.

MYEMP 테이블에서 직무(job)별로 사원수를 조회하세요.

SELECT JOB, COUNT(*) 

FROM    MYEMP

GROUP BY JOB;

<실행결과>

	JOB	COUNT(*)
1	CLERK	4000000
2	TRAINEE	2000000
3	SALESMAN	2000000
4	PLANNER	2000000
5	DESIGNER	2000000
6	PROGRAMMER	2000000
7	ANALYST	4000000
8	MANAGER	2000001

실시간 구체화 뷰 기능이 동작하여 직무가 “MANAGER”인 사원이 한명 더 늘어서 2,000,001로 되었습니다. 이때 실제 MView인 M_COUNT는 갱신되지 않은 상태 입니다. 

MView인 M_COUNT 뷰를 직접 조회했을 때는 2,000,000 이였고, MView를 별도로 갱신하지 않았는데도 사용자의 쿼리에 MView의 내용이 갱신된 것처럼 보이는 이유는 실시간으로 MView Log 를 통해서 사용자의 현재 쿼리에서만 MView의 갱신된 데이터를 보여주기 때문 입니다. 

실제 MView는 갱신되지 않았으므로 어느 시점에 MVIew를 갱신해야 합니다. 실행계획은 복잡하여 표시는 하지 않았는데 SQL Developer에서 F10을 눌러 확인해 보시기 바랍니다. MView Log와 M_COUNT MView가 복잡하게 조인되었음을 확인 할 수 있습니다. M_COUNT MView의 내용만 가지고 쿼리 결과를 만든것이 아니라서 조회되는 데이터 순서가 이전의 SELECT ~ GROUP BY와는 다르게 보입니다.

이 시점에서 MView의 상태를 확인해 보겠습니다.

M_COUNT MView의 상태를 조회 하세요.

SELECT MVIEW_NAME, STALENESS, ON_QUERY_COMPUTATION

FROM   USER_MVIEWS;

<실행결과>

	MVIEW_NAME	STALENESS	ON_QUERY_COMPUTATION
1	M_COUNT	STALE	Y

STALENESS 값이 STALE 입니다. 이는 원본 테이블인  MYEMP가 변경되어 MView가 오래되었다는 뜻으로 현재의 MView는 변동사항이 반영되지 않았고  MView가 가지고 있는 내용은 STALE 되기 전에 FRESH 상태의 모습 입니다.  이때의 MView를 이전 상태에 대한 읽기 일관성 뷰라고 이야기 합니다.

오라클13.2 이상에서 실시간 구체화뷰 기능을 사용하기 위해서는 몇가지 조건이 충족 되어야 합니다. 

1. QUERY_REWRITE_INTEGRITY 매개 변수가 ENFORCED (기본값) 또는 TRUSTED로 설정된 경우에만 사용할 수 있습니다. STALE_TOLERATED로 설정된 경우 사용할 수 없습니다.

2. REFRESH ... ON COMMIT 옵션을 사용하는 MView와 함께 사용할 수 없습니다.

3. MVIEW 생성시 ENABLE ON QUERY COMPUTATION 옵션으로 생성 되어야 합니다.

입력한 데이터를 삭제하고 QUERY_REWRITE_INTEGRITY 파라미터 값을 STALE_TOLERATED로 변경하여 실시간 구체화 뷰 기능이 동작하지 않도록 한후 데이터를 입력 후 조회해 보겠습니다.

실습

이전에 입력한 “홍길동” 데이터를 삭제 합니다.

MYEMP에서 ename이 “홍길동”인 사원을 삭제하세요.

DELETE FROM MYEMP WHERE ENAME = '홍길동';

COMMIT;

실시간 구체화 뷰 기능이 동작하지 않도록 QUERY_REWRITE_INTEGRITY 파라미터 값을 STALE_TOLERATED로 변경합니다.

ALTER SESSION 명령으로 QUERY_REWRITE_INTEGRITY 파라미터 값을 STALE_TOLERATED로 변경하세요.

ALTER SESSION SET  QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;

<실행결과>

Session이(가) 변경되었습니다.

데이터 한건을 입력하고 MView를 조회해 보겠습니다.

MYEMP 테이블에 한 건을 입력하세요.직무(job)는 “MANAGER”로 입력 합니다.

INSERT INTO MYEMP (EMPNO, ENAME, DEPTNO, PAY, JOB, GENDER) VALUES ('99999999','홍길동','1',9999999,'MANAGER','M');

COMMIT;

<실행결과>

1 행 이(가) 삽입되었습니다.

SELECT ~ GROUP BY 구문을 실행하여 입력한 데이터가 반영되었는지 확인해 보겠습니다.

MYEMP 테이블에서 직무(job)별로 사원수를 조회하세요.

SELECT JOB, COUNT(*) 

FROM    MYEMP

GROUP BY JOB;

<실행결과>

	JOB	COUNT(*)
1	PLANNER	2000000
2	TRAINEE	2000000
3	ANALYST	4000000
4	CLERK	4000000
5	SALESMAN	2000000
6	MANAGER	2000000
7	PROGRAMMER	2000000
8	DESIGNER	2000000

데이터가 입력 되었지만 반영되지 않았습니다. 실시간 구체화 뷰 기능이 동작하지 않았습니다. 

Mview를 수작업으로 직접 갱신한 후 다시 SELECT ~ GROUP BY 쿼리문을 실행해 보겠습니다.

MView를 갱신 합니다.

오라클에서 제공하는 DBMS_MVIEW 패키지의 REFRESH 프로시저를 이용하여 M_COUNT MView를 갱신하세요.

EXEC DBMS_MVIEW.REFRESH('M_COUNT', METHOD=>'C');

<실행결과>

PL/SQL 프로시저가 성공적으로 완료되었습니다.

다시 앞의 SELECT ~ GROUP BY 쿼리문을 실행해 보겠습니다.

MYEMP 테이블에서 직무(job)별로 사원수를 조회하세요.

SELECT JOB, COUNT(*) 

FROM    MYEMP

GROUP BY JOB;

<실행결과>

	JOB	COUNT(*)
1	PLANNER	2000000
2	TRAINEE	2000000
3	ANALYST	4000000
4	CLERK	4000000
5	SALESMAN	2000000
6	MANAGER	2000001
7	PROGRAMMER	2000000
8	DESIGNER	2000000

<실행계획>

M_COUNT 뷰를 정상적으로 접근하였고 입력한 데이터가 잘 반영이 되었습니다.

 

#구체화뷰, #오라클구체화뷰, #오라클MVIEW, #오라클뷰, #MaterializedView