h2.10 실체화 뷰 쿼리로 재작성
Refresh 옵션을 이용해 오라클이 집계 테이블을 자동 관리하도록 할 수 있다.
옵티마이저에 의한 Query Rewrite가 지원된다. - 사용자가 집계 테이블의 존재를 모른 채 기준 테이블을 쿼리해도 옵티마이저가 알아서 MV를 액세스 하도록 쿼리를 변환해 줌
alter session set query_rewrite_enabled = true;
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);
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);
Execution Plan
----------------------------------------------------------
Plan hash value: 4290105475
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 3994K| 12 (17)| 00:00:01 |
| 1 | HASH GROUP BY | | 1000 | 3994K| 12 (17)| 00:00:01 |
|* 2 | HASH JOIN | | 1000 | 3994K| 11 (10)| 00:00:01 |
| 3 | TABLE ACCESS FULL | 고객 | 100 | 196K| 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 1000 | 2026K| 8 (13)| 00:00:01 |
| 5 | TABLE ACCESS FULL | 상품 | 10 | 20150 | 3 (0)| 00:00:01 |
| 6 | MAT_VIEW REWRITE ACCESS FULL| 월고객상품| 1000 | 60000 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("월고객상품별_MV"."고객ID"="C"."고객ID")
4 - access("월고객상품별_MV"."상품ID"="P"."상품ID")
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.063 0.062 0 174 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.016 0.006 0 12 0 1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.078 0.068 0 186 0 1000
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1000 HASH GROUP BY (cr=12 pr=0 pw=0 time=713 us cost=12 size=4090000 card=1000)
1000 HASH JOIN (cr=12 pr=0 pw=0 time=2622 us cost=11 size=4090000 card=1000)
100 TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=99 us cost=3 size=201500 card=100)
1000 HASH JOIN (cr=9 pr=0 pw=0 time=1873 us cost=8 size=2075000 card=1000)
10 TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=9 us cost=3 size=20150 card=10)
1000 MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=374 us cost=4 size=60000 card=1000)
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);
Execution Plan
----------------------------------------------------------
Plan hash value: 1609446651
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 104K| 405M| 132 (7)| 00:00:02 |
| 1 | HASH GROUP BY | | 104K| 405M| 132 (7)| 00:00:02 |
|* 2 | HASH JOIN | | 104K| 405M| 127 (3)| 00:00:02 |
| 3 | TABLE ACCESS FULL | 고객 | 100 | 196K| 3 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 104K| 205M| 123 (2)| 00:00:02 |
| 5 | TABLE ACCESS FULL| 상품 | 10 | 20150 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| 판매 | 104K| 5894K| 119 (1)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."고객ID"="C"."고객ID")
4 - access("S"."상품ID"="P"."상품ID")
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 11 0.141 0.135 0 425 0 1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 13 0.141 0.136 0 425 0 1000
Misses in library cache during parse : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1000 HASH GROUP BY (cr=425 pr=0 pw=0 time=570 us cost=132 size=425409544 card=104063)
100000 HASH JOIN (cr=425 pr=0 pw=0 time=211762 us cost=127 size=425409544 card=104063)
100 TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=198 us cost=3 size=201500 card=100)
100000 HASH JOIN (cr=422 pr=0 pw=0 time=118413 us cost=123 size=215722599 card=104063)
10 TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=9 us cost=3 size=20150 card=10)
100000 TABLE ACCESS FULL 판매 (cr=419 pr=0 pw=0 time=25319 us cost=119 size=6035654 card=104063)