일반적인 뷰는 하나 또는 그이상의 테이블에 기초해 논리적으로 정의한 테이블로서, '저장된 쿼리' 또는 '가상의 테이블'이라고 불린다
테이블이 사용될 수 있는 대부분 위치에 뷰를 사용할수 있고, 뷰에 기초해 또 다른 뷰를 생성할 수도 있다.
정의에서도 알수 있듯이 뷰는 쿼리만을 저장하고 있을 뿐 자체적으로 데이터를 갖지는 않는다.
반면, 실체화 뷰(Materialized View, 이하 MV)는 'materialize'가 의미하는 것처럼 물리적으로 실제 데이터를 갖는다.
MV는 과거 분산환경에서 실시간 또는 일정주기로 데이터를 복제하는데 사용하던 Snapshot 기술을 DW분야에 적응 시킨 것이며,
여전히 데이터 복제 용도로 사용할 수 있다.
DW분야에서는 주로 두 개 이상의 테이블을 미리 조인해 두거나 (Join View) 대량의 테이블을 미리 group by해서 집계 해두는 형태
(Aggregate View)로 많이 활용되고 있다
MV를 활용하는 이유는 기준 테이블이 그만큼 대용량이기 때문에 Join View 같은 데이터를 중복 저장하는 비효율이 있어 활용도가 낮고,
주로 Aggregate View형태로 활용되는 편이다
아래는 MV를 사용하게 만드는 가장 중요한 두 가지 특징이다
사용자가 직접 관리하는 일반 집계 테이블과 비교 할 때 MV의 가장 큰 장점은, 자동으로 쿼리가 재작성 (Rewrite)된다는 점이다.
일반 집계테이블이 있음을 미리 인지한 상태에서 직접 그 테이블을 참조하도록 쿼리를 작성해야만 한다.
하지만 MV는 사용자가 집계테이블의 존재를 몰라도 된다.
사용자는 기준(base) 테이블을 쿼리하지만 옵티마이저가 알아서 MV를 엑세스하도록 쿼리를 변환해 주기 때문이다.
쿼리 재작성(Query Rewrite) 기능이 작동하려면 MV를 정의할 때 enable query rewrite 옵션을 지정해 주어야 하고,
세션 또는 시스템 레벨에서 아래와 같이 파라미터도 변경해 주어야한다.
9i 까지는 기본설정이 false였지만, 10g부터는 true로 바뀌었다.
alter session set query_rewrite_enabled = true;
MV의 쿼리 재작성 기능이 어떤 식으로 작동하는지 테스트를 통해 확인해 보자
SQL> create table 상품
2 as
3 select rownum 상품ID, dbms_random.string('u', 10) 상품명
4 from dual connect by level <= 10;
테이블이 생성되었습니다.
SQL>
SQL> create table 고객
2 as
3 select rownum 고객ID, dbms_random.string('a', 10) 고객명
4 from dual connect by level <= 100;
테이블이 생성되었습니다.
SQL>
SQL> create table 판매
2 as
3 select 상품ID, 고객ID, 판매일련번호
4 , to_char(to_date('20081231', 'yyyymmdd')+ 상품ID, 'yyyymmdd') 판매일자
5 , round(dbms_random.value(1, 100)) 판매수량
6 , round(dbms_random.value(1000, 100000), -2) 판매금액
7 from 상품, 고객, (select rownum 판매일련번호 from dual connect by level <= 100);
테이블이 생성되었습니다.
테스트를 위하여 위와 같이 상품, 고객, 판매 세개의 테이블을 만든다
SQL> create materialized view log on 판매
2 with sequence, rowid(상품ID, 고객ID, 판매일자, 판매수량, 판매금액)
3 including new values;
구체화된 뷰 로그가 생성되었습니다.
SQL> create materialized view 월고객상품별_MV
2 build immediate -- 바로 MV 데이터 생성
3 refresh fast on commit -- 커밋 시점에 MV에 실시간 반영
4 enable query rewrite -- query rewrite 활성화
5 as
6 select 상품ID, 고객ID, substr(판매일자, 1, 6) 판매월
7 , sum(판매수량) 판매수량, sum(판매금액) 판매금액
8 from 판매
9 group by 상품ID, 고객ID, substr(판매일자, 1, 6);
구체화된 뷰가 생성되었습니다.
기준(Base) 테이블에 발생한 트랜잭션을 실시간 반영할 수 있도록 MV로그를 생성하였고,
refresh fast on commit 옵션을 가진 MV를 정의하였다.
MV를 정의하면서 데이터도 곧바로 만들어지도록(select 수행결과가 M/V에 저장됨) build immediate옵션도 주었다.
이제 쿼리를 수행해보자.
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.06 0.26 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.06 0.27 0 18 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1000 HASH GROUP BY (cr=12 pr=0 pw=0 time=7130 us)
1000 HASH JOIN (cr=12 pr=0 pw=0 time=23243 us)
100 TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=326 us)
1000 HASH JOIN (cr=9 pr=0 pw=0 time=13595 us)
10 TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=63 us)
1000 MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=3018 us)
사용자는 판매 테이블 쿼리했지만 옵티마이저에 의해 월고객상품별_MV가 액세스되었고,
그 단계에서 6개 블록만 읽은 것을 볼 수있다.
아래는 no_rewrite 힌트를 이용해 쿼리 재작성 기능을 방지한 상태에서 수행한 결과다.
판매 테이블을 직접액세스하면서 419개의 블록 I/O가 발생한 것을 볼 수 있다.
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 3 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.20 0.20 0 425 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.20 0.20 0 428 0 1000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 43
Rows Row Source Operation
------- ---------------------------------------------------
1000 HASH GROUP BY (cr=425 pr=0 pw=0 time=201780 us)
100000 HASH JOIN (cr=425 pr=0 pw=0 time=2201307 us)
100 TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=329 us)
100000 HASH JOIN (cr=422 pr=0 pw=0 time=1200658 us)
10 TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=57 us)
100000 TABLE ACCESS FULL 판매 (cr=419 pr=0 pw=0 time=300021 us)