메뉴 건너뛰기

bysql.net

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

2011.06.08 03:19

오라클잭 조회 수:9949

<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가 발생

 

번호 제목 글쓴이 날짜 조회 수
35 1. 옵티마이저 file 휘휘 2011.04.18 6057
34 3. 옵티마이저의 한계 - P 휘휘 2011.04.18 3695
33 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.18 6158
32 3. 옵티마이저의 한계 휘휘 2011.04.19 6698
31 4. 통계정보 Ⅰ file 토시리 2011.04.25 16002
30 6. 히스토그램 오예스 2011.04.25 17363
29 5. 카디널리티 오라클잭 2011.04.27 12913
28 7. 비용 file balto 2011.05.02 4989
27 8. 통계정보 Ⅱ AskZZang 2011.05.04 5913
26 1. 쿼리 변환이란? 운영자 2011.05.16 6254
25 3. 뷰 Merging 오라클잭 2011.05.17 6080
24 2. 서브쿼리 Unnesting 토시리 2011.05.18 2074
23 5. 조건절 이행 file balto 2011.05.30 5460
22 4. 조건절 Pushing 오예스 2011.05.31 17467
21 6. 조인 제거 AskZZang 2011.06.01 5440
20 7. OR-Expansion AskZZang 2011.06.01 8320
19 12. 기타 쿼리 변환 휘휘 2011.06.06 3118
» 10. 실체화 뷰 쿼리로 재작성 오라클잭 2011.06.08 9949
17 11. 집합 연산을 조인으로 변환 오라클잭 2011.06.08 4954
16 2. 소트를 발생시키는 오퍼레이션 file balto 2011.06.12 4846