'query'에 해당되는 글 2건

  1. 2009.10.22 [Oracle] Query : Matenalized View
2009. 10. 22. 11:37

Materialized View는 일명 MView라고들 말한다.
우리가 소위 알고 있는 view는 실시간의 데이터는 유지하지만, 시간이 많이 걸린다는 단점이 존재한다.
그에 반해 Summary table이라는 것이 있다, 일명 CTAS라 불리는 것이다.
CREATE TABLE AS SELECT ..... 로 생성하는 테이블을 CTAS라 부른다.
이건 실시간의 데이터는 아니지만 성능상의 장점이 존재한다.

이런 VIEW와 CTAS의 장점을 다 갖추고 있는 것이 Materialized View라는 넘이다.


▷ Materialized View란?

너무 빈번하게 발생하는 특정쿼리가 존재한다. 이런 쿼리들을 VIEW형식으로 저장하는 것이 Materialized View라고 생각한다.
BUT 이것은 VIEW처럼 논리적인 공간이 아닌 물리적인 공간이라 보면된다.

간단하게 설명하면, 대용량의 데이터를 SUM, MIN, MAX, AVG, COUNT(*)와 같은 명령어를 사용해 너무나도 자주 조회하는 Query가
있을 것이다. 이 런 Query의 결과만큼의 새로운 테이블을 생성해 놓는 방법이 Materialized View라고 이해하자.


▷ MView의 생성

Materialized View를 생성하기 위해서는 Query Rewrite권한과 CREATE MATERIALIZED VIEW 권한을 MView를 생성하는 유저에게 부여해야한다.
부여하는 놈은 당연 SYSDBA 유저인 SYS이다.

SQL> conn scott/tiger

-- 부서별 최고 SALARY를 조회하는 MView를 생성
SQL> CREATE MATERIALIZED VIEW max_sql_per_dept
        BUILD IMMEDIATE
        REFRESH
        COMPLETE
        ON DEMAND
        ENABLE QUERY REWRITE
        AS
        SELECT MAX( a.sal), a.deptno
          FROM emp a, dept b
         WHERE a.deptno = b.deptno ;

-- MView를 쓰는 방법
SQL> SELECT * FROM max_sal_per_dept;

-- 결과
MAX(A.sal)   DEPTNO
---------- ---------
   2850        30
   3000        20
   5000        10


-- OPTION DESCRIOTION

- BULID IMMEDIATE : MView생성과 동시에 데이터들도 생성되는 옵션
- BULID DEFERRED : MView를 생성은 하지만, 그 안의 데이터는 추후에 생성하도록 하는 옵션
- REFRESH : MView의 데이터를 새로 고치는 시기와 방법 결정
   ☞ 시기
      1) ON COMMIT - 기초 테이블에 Commit이 일어날 때 Refresh가 일어나는 방안이며,
         이는 1개의 테이블에 Count(*), SUM(*)과 같은 집합함수를 사용하거나,
         MView에 조인만이 있는 경우,  GROUP BY절에 사용된 컬럼에 대해 COUNT(col)함수가
         기술된 경우만 사용이 가능

      2) ON DEMAND - 사용자가 DBMS_MVIEW 패키지(REFRESH, REFRESH_ALL_MVIEWS,
          REFRESH_DEPENDENT)를 실행한 경우, Refresh되는 경우

   ☞ 방법
      1) COMPLETE - MView의 정의에 따라 MView의 데이터 전체가 Refresh되는 것으로,
          ATOMIC_REFRESH=TRUE와 COMPLETE으로 설정한 경우
      2) FAST - 새로운 데이터가 삽입될 때마다 점진적으로 Refresh 되는 방안
      3) FORCE - 이 경우 먼저 Fast Refresh가 가능한지 점검 후 가능하면, 이를 적용하고, 아니면
          Complete Refresh를 적용(Default)
      4) NEVER - Refresh를 쓰지 않는다.

- ENABLE QUERY REWRITE - MView 생성시 이 옵션을 주어야만 임의의 SQL문장을 처리시 Query 
  Rewirte를 고려한다. 이는 쿼리 재작성의 기능이다.  이전의 쿼리를 수정 안하고 재작성이 가능한 기능


▷ Query Rewrite

쿼리 재작성 또는 덮어쓰기 정도로 보면 된다, 이미 생성된 뷰의 쿼리를 재작성하는 기능이다.

- 사용방법
1. SYSDBA로 로그인
SQL> ALTER SYSTEM SET QUERY_REWRITE_ENABLED='TRUE';

2. MView를 테스트하는 유저로..
SQL> SET AUTOTRACE ON // 실행계획을 얻는 것이다.
SQL> ALTER SESSION SET OPTIMIZER_MODE='FIRST_ROWS';
SQL> SELECT MAX( a.sal), a.deptno
       FROM emp a, dept b
      WHERE a.deptno = b.deptno
      GROUP BY a.deptno ;

-- Mview를 생성했을 때의 쿼리를 다시 실행하면, 다음과 같은 결과가 출력된다.



▷ MVIEW Refresh

DBMS_MVIEW.REFRESH( 'max_sql_per_dept') ;


----------------------------------------------------------------------------------------------
출처 : jaechun's Story 블러그
----------------------------------------------------------------------------------------------

Posted by 옹니미