오라클 성능 고도화 원리와 해법 II (2010년)
실체화 뷰 쿼리로 재작성 0 0 3,177

by 구루비 쿼리변환 MATERIALIZED VIEW MV [2010.05.14]


일반적인 뷰는 하나 또는 그이상의 테이블에 기초해 논리적으로 정의한 테이블로서, '저장된 쿼리' 또는 '가상의 테이블'이라고 불린다
테이블이 사용될 수 있는 대부분 위치에 뷰를 사용할수 있고, 뷰에 기초해 또 다른 뷰를 생성할 수도 있다.

정의에서도 알수 있듯이 뷰는 쿼리만을 저장하고 있을 뿐 자체적으로 데이터를 갖지는 않는다.
반면, 실체화 뷰(Materialized View, 이하 MV)는 'materialize'가 의미하는 것처럼 물리적으로 실제 데이터를 갖는다.

MV는 과거 분산환경에서 실시간 또는 일정주기로 데이터를 복제하는데 사용하던 Snapshot 기술을 DW분야에 적응 시킨 것이며,
여전히 데이터 복제 용도로 사용할 수 있다.
DW분야에서는 주로 두 개 이상의 테이블을 미리 조인해 두거나 (Join View) 대량의 테이블을 미리 group by해서 집계 해두는 형태
(Aggregate View)로 많이 활용되고 있다
MV를 활용하는 이유는 기준 테이블이 그만큼 대용량이기 때문에 Join View 같은 데이터를 중복 저장하는 비효율이 있어 활용도가 낮고,
주로 Aggregate View형태로 활용되는 편이다

아래는 MV를 사용하게 만드는 가장 중요한 두 가지 특징이다

  • Refresh 옵션을 이용해 오라클이 집계 테이블을 자동 관리하도록 할 수 있다.
  • 옵티마이저에 의한 Query Rewrite가 지원된다.

사용자가 직접 관리하는 일반 집계 테이블과 비교 할 때 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)


문서에 대하여

"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3234

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입