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

조회 수 8754 추천 수 0 2013.08.21 12:33:50
오라클잭 *.176.29.44

<Materialized View>

-실체화 뷰

-일반적인 view가 논리적 view라면 Mview는 물리적으로 실제 데이터를 갖는다.

 snapshot 기법을 이용한 데이터 복제용도로 사용하던 것을 대량의 data를 미리 조인하거나 집계하는 형태로 많이 사용됨

 

 

Mview 특징

  • refresh 옵션을 이용해 오라클이 집계 테이블을 자동 관리하도록 할 수 있음
  • 옵티마이저에 의한 query rewrite가 지원된다(query_rewrite_enabled 제어)
  • SQL문장을 수행하였어도 미리 정의된 MView가 존재한다면, MView를 조회하도록 Query가 다시 쓰여지는 됩니다

 

                                                                                                                            <오라클클럽 발췌>

  - BUILD IMMEDIATE : MView 생성과 동시에 데이터들도 생성되는 옵션

 

  - BUILD DEFERRED : MView를 생성은 하지만, 그 안의 데이터는 추후에 생성하도록 하는 기능 입니다.

 

  - REFRESH 절은 오라클이 MView의 데이터를 언제, 어떻게 Refresh 하는지를 결정 하는 방법입니다.  

    

    ON COMMIT 은 기초 테이블에 Commit 이 일어날 때 Refresh 가 일어나는 방안이며, 이는 1 개의 테이블에

    COUNT(*), SUM(*)과  같은 집합  함수를 사용하거나, MView에 조인만이 있는 경우, Group By 절에 사용된

    컬럼에 대해 COUNT(col) 함수가 기술된 경우만 사용이 가능 합니다.

 

    ON DEMAND는 사용자가 DBMS_MVIEW 패키지 (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)

    를 실행 한 경우 Refresh 되는 경우 입니다.

 

  - Refresh를 하는 방법에는 FORCE, COMPLETE, FAST, NEVER의 4가지가 존재 합니다.

      COMPLETE : MView의 정의에 따라 MView의 데이터 전체가 Refresh 되는      

      FAST : 새로운 데이터가 삽입될 때마다 점진적으로 Refresh 되는 방안으로 Direct Path나 Mview log를 이용 합니다.

      FORCE : 이 경우 먼저 Fast Refresh가 가능한지 점검 후 가능하면 이를 적용하고, 아니면 Complete Refresh를 적용          

      NEVER : MView의 Refresh를 발생시키지 않습니다

 

 

 

____________________________________________________________________________________________________________

TEST

환경:상품,고객.판매 테이블 생성

 

-기존 테이블에 발생한 트랜잭션을 실시간 반영할 수 있도록 MV로그를 생성

create materialized view log on 판매
with sequence, rowid(상품ID, 고객ID, 판매일자, 판매수량, 판매금액)
including new values;

 

--Mview 생성

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)

------------------------------------------------------------------------------------------------------

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.016        0.033          0         81          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch      101    0.016        0.008          0         12          0       1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total      103    0.031        0.041          0         93          0       1000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=47)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
   1000   HASH GROUP BY (cr=12 pr=0 pw=0 time=8254 us)
   1000     HASH JOIN  (cr=12 pr=0 pw=0 time=18579 us)
    100        TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=324 us)
   1000       HASH JOIN  (cr=9 pr=0 pw=0 time=10707 us)
     10           TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=38 us)
   1000          MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=2016 us)

*판매테이블을 쿼리했지만 옵티마이저에 의해 월고객상품별_MV가 액세스 되었고 그 단계에서 6개 블록만 읽음

 

-no_rewrite 힌트를 이용해 쿼리 재작성 기능을 방지

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 Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.009          0         66          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch      101    0.250        0.251          0        425          0       1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total      103    0.250        0.260          0        491          0       1000

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=47)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
   1000   HASH GROUP BY (cr=425 pr=0 pw=0 time=250228 us)
 100000    HASH JOIN  (cr=425 pr=0 pw=0 time=2101933 us)
    100         TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=621 us)
 100000      HASH JOIN  (cr=422 pr=0 pw=0 time=1101021 us)
     10            TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=77 us)
 100000         TABLE ACCESS FULL 판매 (cr=419 pr=0 pw=0 time=200034 us)

 *판매 테이블을 직접 액세스하면서 419개의 블록 I./O가 발생