꿈꾸는 개발자, DBA 커뮤니티 구루비

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

일반적인 뷰는 하나 또는 그 이상의 테이블에 기초해 논리적으로 정의한 테이블로서 '저장된 쿼리' 또는 '가상의 테이블'이라고 부른다.

뷰는 쿼리만 저장하고 있을 뿐 자체적으로 데이터를 갖지 않는다.
실체화 뷰(Materialized View)는 물리적으로 실제 데이터를 갖는다.

두개 이상의 테이블를 미리 조인(Join View)
대량의 테이블을 미리 group by 해서 집계해 두는 형태(Aggregate View) 로 많이 활용 되는 편이다.

두가지 특징

Refresh 옵션을 이용해 오라클이 집계 테이블을 자동 관리
옵티마이저에 의한 Query Rewrite가 지원

장점

자동으로 쿼리가 재작성(Rewiter) 된다는 점
사용자가 집계 테이블의 존재를 몰라도 된다. 사용자는 기준 테이블(Base)을 쿼리하지만 옵티마이저가 알아서 MV를 액세스하도록 쿼리를 변환해준다.

MV의 궈리 재작성 기능의 예


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);


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.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       11      0.00       0.01          0         12          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.00       0.01          0         12          0        1000
 
 
Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  SORT GROUP BY (cr=12 pr=0 pw=0 time=13075 us)
   1000   HASH JOIN  (cr=12 pr=0 pw=0 time=40047 us)
    100    TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=594 us)
   1000    HASH JOIN  (cr=9 pr=0 pw=0 time=22386 us)
     10    TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=87 us)
   1000    MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=5030 us)

사용자는 판매 테이블을 쿼리했지만 옵티마이저에 의해 월고객상품별_MV가 액세스되었고，그 단계에서 6개 블록만 읽은 것을 볼 수 있다.

no rewrite 힌트를 이용해 쿼리 재작성 기능을 방지한 상태


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.26       0.26          0        425          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.26       0.26          0        425          0        1000
 
 
Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  SORT GROUP BY (cr=425 pr=0 pw=0 time=270053 us)
 100000   HASH JOIN  (cr=425 pr=0 pw=0 time=2903057 us)
    100    TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=185 us)
 100000    HASH JOIN  (cr=422 pr=181 pw=0 time=1601620 us)
     10     TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=637 us)
 100000     TABLE ACCESS FULL 판매 (cr=419 pr=0 pw=0 time=400036 us)

판매 테이블을 직접 액세스하면서 419개의 블록 I/O가 발생한 것을 볼 수 있다.