10. 실체화 뷰 쿼리로 재작성
2011.06.07 23:29
10. 실체화 뷰 쿼리로 재작성
▶ 일반 뷰 : 쿼리만 저장, 자체적으로 데이터를 갖지 않음
실체화 뷰(Materialized View) : 물리적으로 실제 데이터를 갖음.
스냅샷 기술을 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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
40 | 2. 서브쿼리 Unnesting | darkbeom | 2011.05.16 | 19727 |
39 | 2. 소트를 발생시키는 오퍼레이션 | 휘휘 | 2011.06.12 | 3471 |
38 | 2. 파티션 Pruning | 실천하자 | 2011.06.22 | 26040 |
37 | 3. 병렬 조인 | 실천하자 | 2011.06.28 | 7166 |
36 | 3. 다양한 인덱스 스캔 방식 | 멋진넘 | 2011.02.19 | 33825 |
35 | 3. 해시 조인 | darkbeom | 2011.03.21 | 21545 |
34 | 3. 옵티마이저의 한계 | 멋진넘 | 2011.04.19 | 7866 |
33 | 3. 뷰 Merging | 실천하자 | 2011.05.15 | 23452 |
32 | 3. 데이터 모델 측면에서의 검토 | 멋진넘 | 2011.06.13 | 5886 |
31 | 3. 인덱스 파티셔닝 | darkbeom | 2011.06.20 | 53968 |
30 | 4. 테이블 Random 액세스 부하 [1] | darkbeom | 2011.02.24 | 14694 |
29 | 4. 조인 순서의 중요성 | 운영자 | 2011.03.28 | 14280 |
28 | 4. 통계정보 Ⅰ | darkbeom | 2011.04.26 | 18147 |
27 | 4. 조건절 Pushing | 실천하자 | 2011.05.31 | 7049 |
26 | 4. 소트가 발생하지 않도록 SQL 작성 | 멋진넘 | 2011.06.13 | 7904 |
25 | 5. 테이블 Random 액세스 최소화 튜닝 | suspace | 2011.02.25 | 6983 |
24 | 6. 스칼라 서브쿼리를 이용한 조인 [1] | 휘휘 | 2011.03.29 | 6646 |
23 | 5. Outer 조인 | 실천하자 | 2011.03.30 | 5036 |
22 | 5. 카디널리티 | suspace | 2011.04.26 | 5960 |
21 | 5. 조건절 이행 | 휘휘 | 2011.05.30 | 5427 |