오라클12C, 실시간구체화뷰(Real-Time Materilized Views)
오라클12C, 실시간구체화뷰(Real-Time Materilized Views)
------------------------------------------------------------------------
-- ORACLE12C R2(12.2) 실시간구체화뷰(Real-Time MVIEW) 실습
-- GROUP 함수, 시간이걸리는 복잡한 조인 튜닝은 MVIEW
-- 원래 뷰는 실제 데이터는 저장하지 않고 쿼리만 저장하는데
-- 구체화뷰(Materialied View)는 실제 데이터까지 저장하는 물리적뷰 입니다.
------------------------------------------------------------------------
-- myemp1 테이블에 생성된 인덱스 확인
SELECT a.index_name, a.column_name, b.visibility
FROM user_ind_columns a, user_indexes b
WHERE a.table_name = 'MYEMP1'
AND a.index_name = b.index_name
ORDER BY index_name;
-- 실습테이블 데이터 SELECT, 1000만
select * from myemp1;
-- 부서별 최소급여를 구하는 쿼리 2.5초 정도...
-- Mview가 생성되지 않은 상태에서 아래 쿼리를 실행하자.
SELECT deptno, empno, sal
FROM myemp1
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM myemp1
GROUP BY deptno)
ORDER BY deptno;
-- MVIEW로그 생성(구체화뷰의 실시간 갱신을 위해)
DROP MATERIALIZED VIEW LOG ON myemp1;
CREATE MATERIALIZED VIEW LOG ON myemp1
WITH ROWID, SEQUENCE(deptno, sal) INCLUDING NEW VALUES;
-- 구체화뷰 생성
DROP MATERIALIZED VIEW MVIEW_TEST ;
CREATE MATERIALIZED VIEW MVIEW_TEST
REFRESH FAST ON DEMAND -- DBMS_MVIEW 패키지에서 REFRESH 명령시 갱신
--REFRESH FAST ON COMMIT -- 원본데이터가 COMMIT 될때 갱신,
--1개의 테이블에 COUNT(*), SUM, MAX, MIN과 같은 집합 함수를 사용하거나, MView에 조인만이 있는 경우,
-- Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만 사용이 가능 합니다.
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
--SELECT SUM(sal) FROM myemp1;
--SELECT COUNT(*) FROM myemp1;
SELECT deptno, min(sal) FROM myemp1 GROUP BY deptno;
-- 뷰의 상태를 확인하자.
SELECT mview_name,
staleness, -- FRESH : 원본데이터랑 동기화가 잘되어있다는 의미
-- STALE : 원본테이블의 데이터가 변경 되었는데 뷰가 갱신되지 않았음, 뷰 사용불가
-- NEEDS COMPILE : 컴파일이 필요한 상태
-- UNUSABLE : 뷰를 읽을 수 없음
on_query_computation
FROM user_mviews;
-- 구체화뷰를 이용하여 쿼리실행 0초
SELECT deptno, empno, sal
FROM myemp1
WHERE (deptno, sal) in (SELECT deptno, min(sal)
FROM myemp1
GROUP BY deptno)
ORDER BY deptno;
-- 테스트를 위해 데이터를 한건 생성하자.
delete from myemp1 where empno = 11111110;
insert into myemp1(empno, ename, sal, deptno) values (11111110, '이종철', 0, '1');
commit;
-- 구체화뷰의 상태 확인
SELECT mview_name,
staleness,
on_query_computation
FROM user_mviews;
-- Mview를 갱신하지 않아 옵티마이저가 Mview를 사용안한다.
SELECT deptno, empno, sal
FROM myemp1
WHERE (deptno, sal) in (SELECT deptno, min(sal)
FROM myemp1
GROUP BY deptno)
ORDER BY deptno;
-- dbms_mview 패키지로 Mview를 갱신하자.
EXEC dbms_mview.refresh('MVIEW_TEST', method=>'C');
-- 이제는 Mview를 사용한다. 0초
SELECT d.dname, e.empno, e.sal
FROM myemp1 e, mydept1 d
WHERE (e.deptno, e.sal) in (SELECT e1.deptno, min(e1.sal)
FROM myemp1 e1
GROUP BY e1.deptno)
AND e.deptno = d.deptno
ORDER BY d.dname;
오라클12.2 이상의 실시간 구체화뷰 기능을 사용하기 위해서는 몇가지 조건이 충족 되어야 합니다.
1. QUERY_REWRITE_INTEGRITY 매개 변수가 ENFORCED (기본값) 또는 TRUSTED로 설정된 경우에만 사용할 수 있습니다. QUERY_REWRITE_INTEGRITY 매개 변수가 STALE_TOLERATED로 설정된 경우 사용할 수 없습니다.
2. REFRESH ... ON COMMIT 옵션을 사용하는 MView와 함께 사용할 수 없습니다.
3. MVIEW 생성시 ENABLE ON QUERY COMPUTATION 옵션으로 생성 되어야 합니다.
MTEMP 테이블에 데이터 행을 하나더 INSERT 후 실시간 MView 가능이 동작하는지 확인해 보겠습니다.
실습
현재 세션 레벨에서 QUERY_REWRITE_INTEGRITY 매개변수 값을 ENFORCED로 변경 합니다.
ALTER SESSION 명령으로 QUERY_REWRITE_INTEGRITY 매개변수 값을 ENFORCED로 설정 하세요. |
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = ENFORCED;
-- 다시 테스트를 위해 데이터를 생성하자.
insert into myemp1(empno, ename, sal, deptno) values (22222220, '이종철', 0, '1');
insert into myemp1(empno, ename, sal, deptno) values (33333330, '이종철', 0, '2');
insert into myemp1(empno, ename, sal, deptno) values (44444440, '이종철', 0, '3');
commit;
-- 위에서 입력한 데이터가 보인다.
SELECT deptno, empno, sal
FROM myemp1
WHERE (deptno, sal) in (SELECT deptno, min(sal)
FROM myemp1
GROUP BY deptno)
ORDER BY deptno;
