개념

1. MV(Materialized View) : 쿼리 & 데이터를 가지고 있는 view
2. 특징
   1. refresh 옵션을 이용해 오라클이 집계 테이블 자동 관리
   2. 옵티마이저에 의한 Query Rewrite 지원
      1. 사용자가 직접 그 테이블을 참조하도록 쿼리 작성하지 않아도 됨
      2. 사용자는 기준 테이블을 쿼리하지만 옵티마이저가 알아서 mv를 액세스하도록 쿼리 변환 수행
3. 활용
   1. DW 분야에서 주로 2개 이상의 테이블을 미리 조인 해둠(Join View. 활용도 낮음)
   2. 대량의 테이블을 미리 group by해서 집계해 두는 형태(Aggregate View)

테스트

• 준비사항
  1. MV 로그 : 기준 테이블에 발생한 트랙잭션을 실시간 반영
  2. build immediate : mv 정의하면서 데이터도 곧바로 만들어지도록 함(select 문 수행결과가 mv에 저장 됨)
  3. refresh fast on commit : commit 발생 시 자동으로 refresh 실행

• mv 사용 할 경우

Current SQL statement for this session:
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)
 
Transformation SQL statement : 
SELECT   "P"."상품명" "상품명", "C"."고객명" "고객명",
         "월고객상품별_MV"."판매월" "판매월",
         SUM ("월고객상품별_MV"."판매수량") "판매수량",
         SUM ("월고객상품별_MV"."판매금액") "판매금액"
    FROM "HEAEUN"."상품" "P",
         "HEAEUN"."고객" "C",
         "HEAEUN"."월고객상품별_MV" "월고객상품별_MV"
   WHERE "월고객상품별_MV"."고객ID" = "C"."고객ID"
     AND "월고객상품별_MV"."상품ID" = "P"."상품ID"
GROUP BY "P"."상품명", "C"."고객명", "월고객상품별_MV"."판매월"

==================================================================
TRACE : 월고객상품별_MV가 액세스 되었고, 이 단계에서 6개의 블록만을 읽음
==================================================================
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.15          8         10          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.03       0.03          0         12          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.10       0.19          8         22          0        1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  HASH GROUP BY (cr=12 pr=0 pw=0 time=42911 us)
   1000   HASH JOIN  (cr=12 pr=0 pw=0 time=69453 us)
    100    TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=1043 us)
   1000    HASH JOIN  (cr=9 pr=0 pw=0 time=37139 us)
     10     TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=126 us)
   1000     MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=8094 us) 

• mv 사용하지 않을 경우

Current SQL statement for this session:
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)

Transformation SQL statement : 
SELECT   /*+ NO_REWRITE */
         "P"."상품명" "상품명", "C"."고객명" "고객명",
         SUBSTR ("S"."판매일자", 1, 6) "판매월",
         SUM ("S"."판매수량") "판매수량", SUM ("S"."판매금액") "판매금액"
    FROM "HEAEUN"."판매" "S", "HEAEUN"."상품" "P", "HEAEUN"."고객" "C"
   WHERE "S"."상품ID" = "P"."상품ID" AND "S"."고객ID" = "C"."고객ID"
GROUP BY "P"."상품명", "C"."고객명", SUBSTR ("S"."판매일자", 1, 6)

=========================================================
TRACE : 판매 테이블을 직접 액세스하면서 419개의 블록 I/O 발생
=========================================================
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.11          6          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       68      0.35       0.55        135        425          0        1000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       70      0.40       0.66        141        431          0        1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
   1000  HASH GROUP BY (cr=425 pr=135 pw=0 time=546642 us)
 100000   HASH JOIN  (cr=425 pr=135 pw=0 time=7411286 us)
    100    TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=1074 us)
 100000    HASH JOIN  (cr=422 pr=135 pw=0 time=4408435 us)
     10     TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=140 us)
 100000     TABLE ACCESS FULL 판매 (cr=419 pr=135 pw=0 time=1005434 us)