블로그 이미지
잡초 개발자의 좌충우돌 이야기 yysvip

카테고리

분류 전체보기 (211)
Java Programing (24)
Web Programing (20)
Database (36)
Tool (46)
까칠한 IT (27)
까칠한 정보 (42)
까칠한 Strory (16)
까칠한 Project (0)
Total1,164,722
Today124
Yesterday4,565

달력

« » 2018.12
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

공지사항

ORACLE의 Materialized View

  까칠한 남자 Strory       yysvip.tistory.com       잡초 개발자 까찰한 쑤의 좌충우돌 개발 이야기

 

Materialized View는 일명 MView라고 한다.


소위 알고 있는 view는 실시간의 데이터는 유지하지만 시간이 많이 걸린다는 단점이 존재한다.

그에 반해 Summary table이라는 것이 있다. 일명 CTAS라고 하며, CREATE TABLE AS SELECT ...... 로 생성하는 테이블을 CTAS라 부른다. 실시간의 데이터는 아니지만 성능상의 장점이 있다.
 
이런 VIEW와 CTAS 의 장점을 다 갖추고 있는 것이 Materialized View이다.

이제 Materialized View에 대해 알아보자.

 

 

▣ Materialized View란??

 

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

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

 

 

▣ MView의 생성

 

Materialized View를 생성하기 위해서는 Query Rewrite권한과  CREATE MATERIALIZED VIEW 권한을 MView를 생성하는 유저에게 부여해야 한다.

 

부여하는 놈은 당연 SYSDBA 유저인 SYS이다.

SQL> GRANT QUERY REWRITE TO 유저명;
SQL> GRANT CREATE MATERIALIZED VIEW TO 유저명;

 

MView를 생성할 유저를 SCOTT 라고 가정을 한다

SQL> conn scott⁄tiger

SQL> DROP MATERIALIZED VIEW LOG ON EMP
SQL> CREATE MATERIALIZED VIEW LOG ON EMP WITH PRIMARY KEY, ROWID INCLUDING NEW VALUES;

SQL> CREATE MATERIALIZED VIEW max_sal_per_dept
     BUILD IMMEDIATE
     REFRESH FAST
     ON COMMIT
     ENABLE QUERY REWRITE AS
     SELECT MAX(A.SAL), A.DEPTNO
       FROM EMP A, DEPT B
      WHERE A.DEPTNO = B.DEPTNO
      GROUP BY A.DEPTNO;

 

 

부서별 최고 SALARY를 조회하는 MView를 생성

SQL> SELECT * FROM max_sal_per_dept;
-- MView를 쓰는 방법
MAX(A.SAL)     DEPTNO
---------- ----------
      2850         30
      3000         20
      5000         10
 

 

※ 생성시 OPTION 정리

 

 

 - BUILD IMMEDIATE : MView 생성과 동시에 데이터들도 생성되는 옵션

 - BUILD 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를 적용(디폴트)

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

 

 

 

▣ 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를 생성했을 때의 쿼리를 다시 실행한다.

 

 

다음처럼 쿼리를 재작성했다는 것을 Execution Plan을 보면 REWRITE 된걸 알 수 있다

 

 

참고 문서 목록 :

 

오라클의 Materialized View 이야기 - http://likebnb.tistory.com/125

Posted by yysvip

최근에 달린 댓글

최근에 받은 트랙백

글 보관함