10. 실체화 뷰 쿼리로 재작성

▶ 일반 뷰 : 쿼리만 저장, 자체적으로 데이터를 갖지 않음

    실체화 뷰(Materialized View) : 물리적으로 실제 데이터를 갖음.

                                                MV 는 과거에 분산 환경에서 실시간 또는 일정 주기로 데이터를 복제하는데 사용하던

                                                스냅샷 기술을 DW분야에 적응시킨 것.

                                                여전히 데이터 복제 용도로 사용할 수 있다.

▶ 장점 : 자동으로 쿼리가 재작성. 사용자는 기준테이블을 쿼리하지만 옵티마이저가 알아서 MV를 액세스하도록 쿼리를 변환.

▶ 쿼리 재작성 기능 작동하려면 

    - MV를 정의할 때 enable query rewrite 옵션 지정

    - 세션 또는 시스템 레벨에서 alter session set query_rewrite_enabled = ture와 같이 파라미터 변경.

      (9i까지는 false가 기본설정, 10g부터는 true)

create table 상품 as
select rownum 상품ID, dbms_random.string('u', 10) 상품명
from   dual connect by level <= 10;

 

create table 고객 as
select rownum 고객ID, dbms_random.string('a', 10) 고객명
from   dual connect by level <= 100;

 

create table 판매 as
select 상품ID, 고객ID, 판매일련번호
     , to_char(to_date('20081231', 'yyyymmdd')+ 상품ID, 'yyyymmdd') 판매일자
     , round(dbms_random.value(1, 100)) 판매수량
     , round(dbms_random.value(1000, 100000), -2) 판매금액
from  상품, 고객, (select rownum 판매일련번호 from dual connect by level <= 100);

 

create materialized view log on 판매
with sequence, rowid(상품ID, 고객ID, 판매일자, 판매수량, 판매금액)
including new values;

 

create materialized view 월고객상품별_MV
build immediate          -- 바로 MV 데이터 생성
refresh fast on commit   -- 커밋 시점에 MV에 실시간 반영
enable query rewrite     -- query rewrite 활성화
as
select 상품ID, 고객ID, substr(판매일자, 1, 6) 판매월
     , sum(판매수량) 판매수량, sum(판매금액) 판매금액
from   판매
group by 상품ID, 고객ID, substr(판매일자, 1, 6);

 

     - 기준테이블에 발생한 트랜잭션을 실시간 반영할 수 있도록 MV로그를 생성

select p.상품명, c.고객명, substr(s.판매일자, 1, 6) 판매월 , sum(s.판매수량) 판매수량, sum(s.판매금액) 판매금액
from   판매 s, 상품 p, 고객 c
where  s.상품ID = p.상품ID
and    s.고객ID = c.고객ID
group by p.상품명, c.고객명, substr(s.판매일자, 1, 6)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.00       0.00          0         12          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.01       0.01          0         18          0        1000

 

 

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  SORT GROUP BY (cr=12 pr=0 pw=0 time=3582 us)
   1000    HASH JOIN  (cr=12 pr=0 pw=0 time=4893 us)
    100        TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=127 us)
   1000       HASH JOIN  (cr=9 pr=0 pw=0 time=2401 us)
     10            TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=29 us)
   1000          MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=10 us)

    - 사용자는 판매테이블 쿼리했지만, 옵티마이저에 의해  월고객상품별_MV 가 엑세스.

▶ 쿼리 재작성 기능 방지

select /*+ no_rewrite */ p.상품명, c.고객명, substr(s.판매일자, 1, 6) 판매월
     , sum(s.판매수량) 판매수량, sum(s.판매금액) 판매금액
from   판매 s, 상품 p, 고객 c
where  s.상품ID = p.상품ID
and    s.고객ID = c.고객ID
group by p.상품명, c.고객명, substr(s.판매일자, 1, 6);

 

 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     1      0.00       0.00          0          0          0           0
Fetch        11     0.00       0.26          0      425         0           1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        13      0.00       0.26          0       425          0          1000

 

 

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  SORT GROUP BY (cr=425 pr=181 pw=0 time=240786 us)
 100000    HASH JOIN  (cr=425 pr=181 pw=0 time=715177 us)
    100           TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=120 us)
 100000        HASH JOIN  (cr=422 pr=181 pw=0 time=514836 us)
     10                TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=16 us)
 100000            TABLE ACCESS FULL 판매 (cr=419 pr=181 pw=0 time=414581 us)

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 이주영 (suspace)
최초작성일: 2011년 6월 7일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^