오라클 - MATERIALIZED VIEW 활용방법

하단은 레퍼런스 문서.

참고 하기 좋은 것은 하단의 링크

http://helols.tistory.com/31

 

 

 

MATERIALIZED VIEW 활용방법
==========================

PURPOSE

---

이 문서는 Oracle 8i에서 materialized view 및 query rewrite를 사용하는
방법을 간략히 소개하고 있다.

SCOPE

---

8i~9i Standard Edition에서는 지원하지 않는다.

Explanation

---

1. MATERIALIZED VIEW

Materialized View(이하 MVIEW)는 DW 환경이나, replication 환경에
유용한 기능으로, inner-join, outer-join, equi-join 등 각종 view를
수동으로 갱신하거나, 주기적으로 자동 갱신을 할 수 있게 해 준다.

원격 데이터베이스의 테이블이 포함된 MVIEW는 양방향 replication을
지원한다. 또한 MVIEW는 사용자에게는 투명하게 cost optimization에
기반을 둔 qurey rewrite 기능을 제공한다. Query rewrite 기능을
제공하기 위해 Oracle 에서는 Dimension이라는 객체를 추가 했는데,
Dimension 객체는 대용량 데이터에 대한 질의를 집계성 데이터에 대한
질의로 자동 변환 해 주는 기능을 제공해 준다.

MVIEW는 질의 실행을 할 때마다 매번 대량의 join이나, aggregation
연산을 수행하지 않고, 미리 계산된 값을 질의하기 때문에 성능 향상을
가져올 수 있으며, optimizer는 MVIEW가 어느때 사용되는 것이
적절할지를 판단할 수 있게 설계되었다.
Query rewrite는 사용자에는 투명하다. 만약 환경이 적절히
셋업 되어 있다면, 대량 대이터에 대한 복잡한 질의 응답 속도를
획기적으로 개선할 수 있게 한다.

2. MVIEW 관련 파라미터

MVIEW와 관련된 파라미터 목록은 다음과 같다.

optimizer_mode
query_rewrite_enabled
query_rewrite_integrity
compatible

1) optimizer_mode
Query Rewrite 기능을 사용하기 위해서는 init.ora 파일의 optimizer
mode값은 "ALL_ROWS" 나 "FIRST_ROWS"로 지정하거나, "CHOOSE"인 상태에
서 모든 테이블을 ANALYZE 시켜 두어야 한다.

2) query_rewrite_enabled
파라미터 query_rewrite_enabled 의 값은 "TRUE"로 지정한다.

3) query_rewrite_integrity
파라미터 query_rewrite_integrity 는 선택적으로 지정할 수 있는
파라미터이지만, "STALE_TOLERATED", "TRUSTED", 또는 "ENFORCED"
으로 지정되어야 한다. 이 파라미터는 query rewrite의 정확성을 제어
하는 파라미터이다.

각각의 의미는 다음과 같다

TRUSTED : optimizer에서 MVIEW의 데이터가 정확하다고
간주하고 질의 수행. Integrity 확인을 하지않음.
ENFORCED: query_rewrite_integrity 의 기본값으로,
사용자가 integrity constraint를 확인하여야
한다. MVIEW는 fresh한 데이터를 포함하여야 한다.
STALE_TOLERATED : Optimizer에서 데이터가 stale 상태이거나
fresh 상태인 경우 모두 MVIEW 사용

3. MVIEW 사용에 필요한 권한

MVIEW를 사용하기 위한 권한은 base 테이블에 대한 사용자의 권한에
달려있다. 두개의 중요한 시스템 권한은 다음과 같다.

grant rewrite
grant global rewrite

1) grant rewrite
MVIEW의 base table이 모두 사용자 자신의 테이블일 경우,
자신이 선언한 MVIWE 사용 가능.

2) grant global rewrite
사용자가 어느 schema에 속한 MVIEW라도 사용 가능.

3) MVIEW 사용에 필요한 권한이 충족된 경우 다음 조건을 만족하여야 한
다.
a. 세션에 query rewrite 기능이 enable 되어 있음.
b. MVIWE 자체가 enable 되어 있음.
c. integrity level이 적절히 셋업 되어 있음.
d. MVIEW에 데이터가 존재함.

Example

---

다음과 같은 테이블이 있을 때,

Dealer (dealer_num, dealer_name, dealer_city, dealer_state)
Automobile (auto_num, auto_name, auto_year)
Shipping (shipping_num, shipping_day, shipping_month, shipping_time)
Summary (dealer_num, auto_num, shipping_num, auto_value)

MVIEW 생성

Create Materialized View test_mv
as
select d.dealer_num, d.dealer_name, v.auto_value, s.shipping_num,
s.shipping_day, v.dealer_num, v.rowed, s.rowid
from summary v, dealer d, shipping s
where v.shipping_num = s.shipping_num
and v.dealer_num = d.dealer_num

4. Query rewrite에서 MVIEW 사용 여부 판단 알고리즘

1) Full SQL Text Match
질의의 select 문장과 MVIEW를 만들때 사용한 select 문장 비교

2) Partial SQL Text Match

Full SQL Text Match가 실패할 경우 select 문장의 from 절 이하의
내용이 MVIEW를 만들때 사용한 내용과 일치하는지 비교

3) Generla Query Rewrite Method
1, 2 항에서 실패할 경우, optimizer에서 MVIEW 사용 가능 여부를 판단.
필요한 데이터가 MVIWE에서 제공하는 것 보다 적거나, 많거나, 변환 가능
한지를 판단하고, MVIWE 데이터가 충분한지 여부를 joing compatibility,
grouping compatibility, aggregate compatibility 등을 확인하여 판단

5. MVIEW와 Integrity Constraints

MVIEW는 DW 환경에서 유용하게 사용될 수 있는데, 대부분의 DW는
integrity constraint를 사용하지 않는다. 즉 DW는 원천 데이터에서
integrity가 보장되었다고 간주한다.

다른 한편으로 integrity constraint는 query rewrite에 유용하다.
이 모순되는 사항은 NOVALIDATE 와 RELY 옵션을 이용해 조율을 맞추어야 한다.

1) query_rewrite_enabled = enforced
데이터베이스의 constarint는 validate 상태로 두어야 한다.

2) query_rewrite_enabled = stale_tolerated | trusted
데이터베이스의 constraint를 nonvalidate, rely로 지정 해 준다.

6. Query Rewrite와 Hint 사용

Index 관련 Hint를 사용하는 것 처럼, query rewite 관련 Hint를 사용하여
제어할 수 있다.

NOREWRITE :
Select /*+NOREWRITE*/...
REWRITE:
Select /*+REWRITE(mv1)*/...

7. MVIEW 사용 예제

1) Full SQL Text Match

Select d.dealer_name, shiping_month, a.autonum, sum(v.auto_value) as sum_sales,
count(v.auto_value) as count_values
from summary f, dealer d, shippings, automobile a
where v.shipping_num = s.shipping_num
and v.dealer_num = d.dealer_num
and v.auto_num = a.auto_num
group by d.dealer_name, shipping_month, a.auto_num

위 SQL 문은 다음과 같이 미리 생성된 MVIEW를 이용하도록 rewrite 될 수 있다.

select dealer_name, shipping_month, auto_num, sum_value, count_value
from <MVIEW명>

2) Partial SQL Text Match

Select d.dealer_name, shiping_month, a.autonum, avg(v.auto_value0 as avg_sales,
count(v.auto_value) as count_values
from summary f, dealer d, shippings, automobile a
where v.shipping_num = s.shipping_num
and v.dealer_num = d.dealer_num
and v.auto_num = a.auto_num
group by d.dealer_name, shipping_month, a.auto_num

위 SQL 문장은 다음과 같이 미리 생성된 MVIEW를 사용할 수 있다.

select dealer_name, shipping_month, auto_num, sum_values/count_values as avg_values
from test_mv

Reference Documents

---

<note: 106024.1>