Oracle Materialized View

1. Materialized View

 

◎ Oracle 8i에서의 "MATERIALIZED VIEW" 는 "SNAPSHOT" 와 SYNONYM 으로 생각 하면 가장 좋을 것 같다. 

   이는 대용량의 DATABASE 에서 SUM 과 같은 AGGREGATE FUNCTION 사용 시 값 비싼 COST 를 줄이는 데

   사용하기에 적합한데 이는 REPLICATE 가 가능하여 SNAPSHOT 처럼 사용이 가능함을 의미한다.

 

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

 

◎ 원격 데이터베이스의 테이블이 포함된 MVIEW는 양방향 replication을 지원한다. 또한 MVIEW는 사용자에게는 
   투명하게 cost optimization에 기반을 둔 qurey rewrite 기능을 제공한다.

 

◎ Query rewrite란 table과 view들에 대한 연산으로 이루어진 SQL 문장이 해당 table들에 기반해서 정의된 
   materialized view에 대한 연산으로 변경되는 것을 말한다. 이러한 변경은 Optimizer에 의해 내부적으로 
   수행되기 때문에 일반 사용자나 application은 materialized view의 존재 여부를 인식하지 않아도 된다.

 

◎ Query rewrite는 cost-based optimization 모드에서만 가능하다. 따라서 Materialized View를 만들기 위해선

   해당 Table이 반드시 Analyze 되어 있어야 한다.

 

◎ Query rewrite 기능을 제공하기 위해 Oracle 에서는 Dimension이라는 객체를 추가 했는데,
   Dimension 객체는 대용량 데이터에 대한 질의를 집계성 데이터에 대한 질의로 자동 변환 해 주는 기능을 제공해 준다.

 

◎ MVIEW는 질의 실행을 할 때마다 매번 대량의 join이나, Aggregation 연산(예: SUM, COUNT 등)을 수행하지 않고,

   미리 계산된 값을 질의하기 때문에 성능 향상을 가져올 수 있으며, optimizer는 MVIEW가 어느때 사용되는 것이
   적절할지를 판단할 수 있게 설계되었다.

 

◎ Query rewrite는 사용자에는 투명하다. 만약 환경이 적절히 셋업 되어 있다면, 대량 대이터에 대한

   복잡한 질의 응답 속도를 획기적으로 개선할 수 있게 한다.

 

 

 

2. Materialized View 관련 Initialization 파라미터

 

◎ 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. Materialized View 사용에 필요한 권한

                         

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

◎ 다음은 두개의 중요한 시스템 권한에 대한 설명이다.
  1) grant rewrite
     - MVIEW의 base table이 모두 사용자 자신의 테이블일 경우, 자신이 선언한 MVIWE 사용 가능.
   
  2) grant global rewrite
     - 사용자가 어느 schema에 속한 MVIEW라도 사용 가능.
   
◎ MVIEW 사용에 필요한 권한이 충족된 경우 다음 조건을 만족하여야 한다.
   a.  세션에 query rewrite 기능이 enable 되어 있음.
   b.  MVIWE 자체가 enable 되어 있음.
   c.  integrity level이 적절히 셋업 되어 있음.
   d.  MVIEW에 데이터가 존재함.

 

 

4. Query Rewrite에서 Materialized View 사용 여부 판단 알고리즘

 

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. Materialized View와 Integrity Constraints

 

◎ MVW는 DW 환경에서 유용한데, 대부분의 DW는 integrity constraint를 사용하지 않는다.

   즉 DW는 원천 데이터에서 integrity가 보장되었다고 간주한다.

 

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

 

◎ query rewrite와 integrity constraint의 연관 관계

  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(MView_Name) */...

 

 

 

7. Three Types of Materialized Views

 

1) Materialized Aggregate View (MA-View)  

  - One Table

  - Aggregation (Sum, Avg...)

  - Example: 
    create materialized view MA
    build immediate
    enable query rewrite
    refresh on demand
    as
    select g_no, sum(amount), count(amount), count(*)
    from fact -- One Table
    group by g_no; 

   

  

2) Materialized Join View (MJ-View)

  - Many Tables 
  - inner/outer join (join index)
  - no aggregates 
  - Rowids from base tables in MV for incremental refresh

  - Example: 
    create materialized view MAJ
    build immediate
    enable query rewrite
    refresh on demand
    as
    select f.g_no, f.amount, t.t_day, f.rowid f_rid
    from fact f, time t
    where f.t_no = t.t_no; 

   

  

3) Materialized Aggregate Join View (MAJ-View)  

  - Many Tables 
  - inner/outer join (join index)

  - Aggregation (Sum, Avg...)

  - Example: 
    create materialized view MAJ
    build immediate
    enable query rewrite
    refresh on demand
    as
    select g_no, sum(amount), count(amount), count(*), t_day
    from fact, time 
    where f.t_no = t.t_no
    group by g_no, t_day;