오라클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;
-- 이번에는 뷰의 실시간 갱신 기능을 이용해 데이터가 변경되는 즉시 뷰가
-- 갱신되는 것을 확인하자.
-- ENABLE ON QUERY COMPUTATION 옵션 동작을 위해
-- 기본값은 enforced(오라클이 일관성과 무결성을 알아서 보장, 원본이 변경되고 구체화뷰가 동기화 안된 경우 뷰 사용불가)
-- STALE_TOLERATED : 구체화된 뷰가 원본과 데이터가 동기화 안되어도 구체화뷰 사용가능.
alter session set QUERY_REWRITE_INTEGRITY = STALE_TOLERATED;
--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 mview_name,
staleness,
on_query_computation
FROM user_mviews;
-- Mview를 사용한다. 하지만 위에 입력한 사원이 뷰에 반영되지 않는다.
-- 원본데이터에 변경이 있어서 뷰가 컴파일이 필요한 상태가 되면 원래 구체화뷰를 사용하지 않고 오류발생
-- STABLE
-- 이러한 변경된 경우에도 뷰를 사용하도록 세션레벨에서 QUERY_REWRITE_INTEGRITY = STALE_TOLERATED 설정
SELECT deptno, empno, sal
FROM myemp1
WHERE (deptno, sal) in (SELECT deptno, min(sal)
FROM myemp1
GROUP BY deptno)
ORDER BY deptno;
-- 구체화뷰를 최신 상태로 갱신 후 다시 조회하자.
EXEC dbms_mview.refresh('MVIEW_TEST', method=>'C');
-- 데이터를 삭제하자.
delete from myemp1 where empno = 22222220;
delete from myemp1 where empno = 33333330;
delete from myemp1 where empno = 44444440;
commit;
-- 구체화뷰에서 VIEW LOG를 이용하여 위 삭제한 데이터가 SELECT가 되지 않는다.
-- 구체화뷰가 출력하는 데이터가 삭제되면 원본 동기화와 관계없이 구체화뷰에서 SELECT 안됨
SELECT deptno, empno, sal
FROM myemp1
WHERE (deptno, sal) in (SELECT deptno, min(sal)
FROM myemp1
GROUP BY deptno)
ORDER BY deptno;
-- 다시 테스트를 위해 데이터를 생성하자.
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;
