오라클 성능 고도화 원리와 해법 II (2012년)
목록
실체화 뷰 쿼리로 재작성 0 0 99,999+

by 구루비스터디 쿼리변환 MATERIALIZED VIEW MV [2018.04.01]

  1. 실체화 뷰 쿼리로 재작성
    1. MV 특징
    2. MV 장점
    3. 쿼리재작성 기능 작동법
    4. MV의 쿼리 재작성 기능


실체화 뷰 쿼리로 재작성

  • 뷰는 하나 또는 그 이상의 테이블에 기초해 논리적으로 정의한 테이블 (저장된쿼리, 가상의 테이블 이라 불림)
  • 테이블이 사용될 수 있는 대부분 위치에 뷰를 사용할 수 있고, 뷰에 기초해 또다른 뷰를 생성할 수 있음.
  • 뷰는 쿼리만 저장하로 있을 뿐 자체적으로 데이터를 갖지 않음
  • 실체화된 뷰(Materialized View, 이하 MV) 는 물리적으로 실제 데이터를 갖고 있음


h6 MV 활용이유

  • 기준테이블이 대용량이기 때문에 Join View는 같은 데이터를 중복 저장하는 비효율이 있어 활용도가 낮고 , 주로 Aggregate View 형태로 활용됨.


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


MV 장점
  • 자동으로 쿼리가 재정성 된다는 점
  • 사용자가 집계 테이블의 존재를 몰라도 사용자는 기준 테이블을 쿼리하지만 옵티마이저가 알아서 MV를 액세스하도록 쿼리를 변환해 줌.


쿼리재작성 기능 작동법
  • MV정의시 enable query rewrite 옵션 지정
  • 세션/시스템 레벨에서의 파라미터 설정 alter session set query_rewrite_enable=true
  • (9i까지는 default 값이 false 였으나 10g부터는 true )


MV의 쿼리 재작성 기능

 

-- 상품테이블생성
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);


-- 구체화된 뷰로그  생성
  기준 테이블에 발생한 트랜잭션을 실시간 반영할 수 있도록 MV 로그 생성 
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);


-- query rewrite 를 위한 쿼리수행
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);

Execution Plan
----------------------------------------------------------
Plan hash value: 4290105475

---------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |  1000 |  3994K|    12  (17)| 00:00:01 |
|   1 |  HASH GROUP BY                  |           |  1000 |  3994K|    12  (17)| 00:00:01 |
|*  2 |   HASH JOIN                     |           |  1000 |  3994K|    11  (10)| 00:00:01 |
|   3 |    TABLE ACCESS FULL            | 고객      |   100 |   196K|     3   (0)| 00:00:01 |
|*  4 |    HASH JOIN                    |           |  1000 |  2026K|     8  (13)| 00:00:01 |
|   5 |     TABLE ACCESS FULL           | 상품      |    10 | 20150 |     3   (0)| 00:00:01 |
|   6 |     MAT_VIEW REWRITE ACCESS FULL| 월고객상품|  1000 | 60000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("월고객상품별_MV"."고객ID"="C"."고객ID")
   4 - access("월고객상품별_MV"."상품ID"="P"."상품ID")

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.063        0.062          0        174          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch       11    0.016        0.006          0         12          0       1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total       13    0.078        0.068          0        186          0       1000

Misses in library cache during parse   : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
   1000  HASH GROUP BY (cr=12 pr=0 pw=0 time=713 us cost=12 size=4090000 card=1000)
   1000   HASH JOIN  (cr=12 pr=0 pw=0 time=2622 us cost=11 size=4090000 card=1000)
    100    TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=99 us cost=3 size=201500 card=100)
   1000    HASH JOIN  (cr=9 pr=0 pw=0 time=1873 us cost=8 size=2075000 card=1000)
     10     TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=9 us cost=3 size=20150 card=10)
   1000     MAT_VIEW REWRITE ACCESS FULL 월고객상품별_MV (cr=6 pr=0 pw=0 time=374 us cost=4 size=60000 card=1000)


-- 사용자는 판매 테이블을 쿼리 했지만 옵티마이저에 의해 월고객상품별_MV가 엑세스되었다.

-- 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);

Execution Plan
----------------------------------------------------------
Plan hash value: 1609446651

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   104K|   405M|   132   (7)| 00:00:02 |
|   1 |  HASH GROUP BY       |      |   104K|   405M|   132   (7)| 00:00:02 |
|*  2 |   HASH JOIN          |      |   104K|   405M|   127   (3)| 00:00:02 |
|   3 |    TABLE ACCESS FULL | 고객 |   100 |   196K|     3   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |      |   104K|   205M|   123   (2)| 00:00:02 |
|   5 |     TABLE ACCESS FULL| 상품 |    10 | 20150 |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| 판매 |   104K|  5894K|   119   (1)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("S"."고객ID"="C"."고객ID")
   4 - access("S"."상품ID"="P"."상품ID")



Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch       11    0.141        0.135          0        425          0       1000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total       13    0.141        0.136          0        425          0       1000

Misses in library cache during parse   : 0
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)


Rows     Row Source Operation
-------  -----------------------------------------------------------------------
   1000  HASH GROUP BY (cr=425 pr=0 pw=0 time=570 us cost=132 size=425409544 card=104063)
 100000   HASH JOIN  (cr=425 pr=0 pw=0 time=211762 us cost=127 size=425409544 card=104063)
    100    TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=198 us cost=3 size=201500 card=100)
 100000    HASH JOIN  (cr=422 pr=0 pw=0 time=118413 us cost=123 size=215722599 card=104063)
     10     TABLE ACCESS FULL 상품 (cr=3 pr=0 pw=0 time=9 us cost=3 size=20150 card=10)
 100000     TABLE ACCESS FULL 판매 (cr=419 pr=0 pw=0 time=25319 us cost=119 size=6035654 card=104063)

-- query rewrite 기능 비활성화를 통해 비효율이 발생한것을 확인할 수 있다.

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

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

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

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

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