오라클12C, 실시간구체화뷰(Real-Time Materilized Views)

FSP 0 325 2020.08.05 23:38

오라클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;

Comments