메뉴 건너뛰기

bysql.net

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

2011.06.07 23:29

suspace 조회 수:5529

 

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
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^


번호 제목 글쓴이 날짜 조회 수
» 10. 실체화 뷰 쿼리로 재작성 suspace 2011.06.07 5529
39 9. Outer 조인을 Inner 조인으로 변환 darkbeom 2011.06.07 7831
38 8. 공통 표현식 제거 darkbeom 2011.06.07 5217
37 12. 기타 쿼리 변환 [3] 실천하자 2011.06.03 6644
36 4. 조건절 Pushing 실천하자 2011.05.31 7015
35 7. OR-Expansion 멋진넘 2011.05.31 8715
34 6. 조인 제거 멋진넘 2011.05.30 4590
33 5. 조건절 이행 휘휘 2011.05.30 5406
32 2. 서브쿼리 Unnesting darkbeom 2011.05.16 19693
31 3. 뷰 Merging 실천하자 2011.05.15 23377
30 7. 비용 휘휘 2011.05.03 6163
29 1. 쿼리 변환이란? 실천하자 2011.05.02 4923
28 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.30 31054
27 5. 카디널리티 suspace 2011.04.26 5953
26 4. 통계정보 Ⅰ darkbeom 2011.04.26 18084
25 6. 히스토그램 실천하자 2011.04.25 10914
24 3. 옵티마이저의 한계 멋진넘 2011.04.19 7855
23 1. 옵티마이저 file 실천하자 2011.04.18 11208
22 2. 옵티마이저 행동에 영향을 미치는 요소 휘휘 2011.04.18 5089
21 8. 고급 조인 테크닉-2 file suspace 2011.04.05 7014