Snapshot vs Materialized View

오라클 8 까지는 snapshot, 8i 이후로는 materialized view(이하 m-view)로 알려져 있으나,

엄밀히 말하면, m-view는 snapshot을 포함한 좀 더 발전된 스키마이며, 10g에서도 snapshot은 잘 사용되고 있다.

 

기본적으로 snapshot은 테이블을 replication(복제)하기 위한 방법으로 사용되었으며, m-view는 snapshot의 기능에 추가적으로 on-commit(실시간복제), 통계,함수 등의 조회를 보다 빠르게 하기 위해서 만들어 진다. 그러나 원격지 tabled의 on-commit(실시간복제)는 지원되지 않으므로, 원격지 replication(복제)는 snapshot과  m-view 의 기능은 동일하다.(생성하는 쿼리도 snapshot과 m-view는 거의 동일하다.)

 

1. snapshot log 생성

snapshot은 complete(truncate 후 모든 데이터 복제)와 fast(변경된 데이터만 복제) 두 가지 옵션이 있는데, 서버부하를 줄이기 위해서 대부분 fast를 사용한다.

변경된 데이터를 check 하기 위해서는 먼저 snapshot log를 생성해야 된다.

SQL> create snapshot log on <snapshot_log_name>;

<(with rowid (column_name)>

두 번째 행의 rowid 옵션을 빼면 기본적으로 primary key 로 로그가 생성된다.

 

2. snapshot 생성

 

Database Link 생성
--------------------

SQL) CREATE DATABASE LINK <db link 명>
     2  CONNECT TO <user명> IDENTIFIED BY < password > USING <host명> ;

Database link created.

Create the snapshot
-------------------

SQL) create snapshot <snapshot명>
  2  refresh <fast/complete>
  5  start with sysdate
  6  next sysdate + (5/1440)          /* 5 분마다 refresh 5/1440 대신 1/288 도 가능*/
  7  as select * from <user명><table명>@<db_link 명>;

 

snapshot log를 rowid로 만들었다면, as select 앞에 with rowid를 넣어줘야함.

 

참고 쿼리

마스터 테이블 쪽 snapshot log 조회
select * from dba_snapshot_logs ;
마스터 테이블 쪽 snapshot 조회
select * from dba_registered_snapshots;

 

Local 쪽 snapshot 조회

select * from dba_snapshots;

 

 

 

 

오라클 자료

* snapshot

 

No. 10358

SCOPE
-----
Standard Edition 은 Oracle 8.1.6 이상 부터 Basic Replication 지원이 된다.

snapshot의 기본 개념과 예제
==========================

먼저 Oracle7 에서의 snapshot 에 대해 논하고 implementation 하는 방법과
refresh 하는 방법에 대해 알아보자 .

1) 서론

Oracle7 에서는 분산 network 환경에서 서로 다른 노드의 갯수에 제한이 없이
master table 의 replication 을 위한 table snapshot 을 제공한다.
snapshot 은 update 가 불가능하다. 이것은 read 만 가능한 table 과 같다고
할 수 있겠다. Update 는 master table 에서만 가능하다.
각 master table의 변경된 사항은 snapshot (replicat) 에 비동기적으로
반영되어진다. 이 refresh 는 SQL 명령어를 이용해 수동적으로 수행되어질 수도
있고, refresh 시간에 따라 자동적으로 수행되어질 수도 있다.

2)  Simple 과  Complex

Simple snapshot 은 각 row 가 1개의 remote table 의 1개의 row 에 기초한
것이다. 즉 Group by, Order by, subqueries, joins, set operation이 없는
경우이다. 반대로 snapshot 에 정의된 query 가 이러한 구문을 포함하면
complex snapshot 이라 한다.

3)  snapshots 의 장점

. remote table 에 대한 snapshot 을 local 에 가짐으로써 network 을 통한
query 를 하지 않아도 되어 시간을 절약할 수 있다.
. 여러가지 원인에 의해 master table 을 access 하지 못하더라도 master
site 사용자들은 여전히 snapshots을 사용할 수 있다.
  
4) 사용 방법

CREATE SNAPSHOT <schema>.snapshot_name
       PCTFREE               integer
       PCTUSED               integer
       INITRANS              integer
       MAXTRANS              integer
       TABLESPACE            tablespace
       STORAGE               storage_clause
       CLUSTER               cluster (column_list)
       REFRESH               <fast>
                             <complete>
                             <force>              
       START WITH date
       NEXT date
                      
5)snapshot 에 관련한 오브젝트들

Master table
snapshot base table
snapshot log

6) snapshot 에 관련한 Data dictionary Views:

DBA_snapshotS
DBA_snapshot_LOGS
USER_snapshotS
ALL_snapshotS
MVIEW$<snapshot name>

7) snapshot 에 관련한 Data dictionary tables:

SNAP$<snapshot name>
MLOG$<snapshot name>

8) 연관된 Packages

*DBMS_snapshot

이것은 스크립트를 수행하면 생성된다.

catproc.sql
  
  --  이 Package 에서 수행되는 것들
  --
  --  purge_log         - 필요없는 row 의 log 를 purge
  --  refresh           - 주어진 snapshot 의 refresh
  --  refresh_all       - 모든 snapshot 을 refresh        
  --  drop_snapshot     - snapshot 을 drop
  --  set_up            - snapshot refresh 를 위해 master 쪽을 setup
  --  wrap_up           - master site 에서 refresh를 record
  --  get_log_age       - log 에서 가장 오래된 date 를 check
  --  testing           - snapshot을 test (currently null)
  --  I_am_a_refresh    - flag used to let triggers identify refreshes

이 package 는 snapshot 를 생성하기 전에 반드시 존재하여야 한다.

9) init.ora 에 적용되는 파라미터들

snapshot 의 refresh process 를 위한 파라미터는 다음과 같다.

   o  snapshot_REFRESH_PROCESSES                (number)
   o  snapshot_REFRESH_INTERVAL                 (seconds)
   o  snapshot_REFRESH_KEEP_CONNECTIONS      (true/false)

10) snapshot Mechanism

Snapshot이 생성되면, Oracle은 이 snapshot에 관련한 여러 다른 오브젝트들도  
함께 생성한다. 이들 오브젝트는 변화되어지지 않는다.
snapshot site에서는 snapshot 에서 정의한 query 에 의해 가져온 row 를 저장할
base table 을 생성한다. 또한 이 table 을 기반으로 view 를 생성한다.
이 view 는 read-only 이고 snapshot 을 이용하는 사용자가 이용 가능하다 .
또 다른 view 로는 snapshot 을 refresh 하기 위한 view 가 있다.

snapshot refreshing:

주기적으로 snapshot 은 refresh 하여 최근의 변화된 사항들을 master
table 에 반영한다. Snapshot process는 snapshot 에서 정의된 query를
이용하여  변화된 사항들을 가져와 이전의 snapshot data 를 변환한다.

각 snapshot 은 각기 트렌젝션에서 refresh 된다.

snapshot Log:

snapshot log 는 master table과 관련이 있고 master DB 쪽에 존재한다.
여기에는 mater table에서 update/delete/insert 되는 row 가 저장되며
snapshot refresh 시 이용된다. 임의의 master table에 기초한 simple
snapshot이 refresh되면 snapshot log의 해당 row가 반영된다.  
이것을 "fast refresh"라 일컫는다.  한 snapshot log는 같은 master table에
설정된 여러 개의 snapshot log 에 사용되어진다. Log 의 특정 row 는 모든
snapshot 에 의해 필요 없게 되면 삭제가 되어 그 크기가 작게 유지된다.
그러나, 필요로 하는 snapshot 이 하나라도 있으면 지워지지 않는다.

Snapshot이 simple snapshot이 아니고 snapshot log가 없으면 fast refresh
는 불가능하며 이 경우 "complete refresh" 에 의해 refresh 된다.

=========
사용 예 :
=========

**************************************************
[1] Simple snapshot 과 snapshot log (on Master):
**************************************************

MASTER SITE (REMOTE)
--------------------

Create a snapshot log on the master table:

    sqlplus scott/tiger

    create snapshot log on emp
    tablespace users
    storage (initial 10K pctincrease 0)
    pctfree 5;

NOTE:  init.ora 파라미터는 필요하지 않다.

snapshot SITE (LOCAL)
---------------------

1.  다음처럼 initSID.ora 파라미터를 설정한다.

    snapshot_refresh_interval = 20

    snapshot_refresh_processes = 2

    snapshot_refresh_keep_connections = true

    open_cursors = 250

2.  sqldba

    connect internal

    shutdown

    startup  (init.ora 파라미터 적용)

3.  sqlplus scott/tiger

    create database link chicago

    connect to scott identified by tiger  -- remote, master table의

                                                          owner/passwd 지정.
    using 'chicago';

    NOTE:SQL*Net V2 의 'chicago'는 tnsnames.ora에 정의되어 있어야 한다.

4.  sqlplus scott/tiger

    create snapshot emp_snap

    pctfree 5

    pctused 60

    refresh fast

    start with sysdate

    next sysdate + (1/288)          /* 5 분마다 refresh */

    as select * from emp@chicago;

MASTER SITE (REMOTE)
--------------------

1.  sqlplus scott/tiger

    insert into emp (empno,ename,job,hiredate,deptno)

    values (1234, 'SCOTT','DBA',sysdate,10);
    commit;

snapshot SITE (LOCAL)
---------------------

1.  sqlplus scott/tiger  (or simply switch to client window)

    select count(*) from emp_snap;

    snapshot 이 자동으로 refresh 될 때까지 반복한다.(약 5 분)
    이 방법은 fast refresh 가 작동됨을 보여준다.

 

(2) REFRESH COMPLETE OPTION 을 이용한 snapshot.
  
=====================
snapshot SITE (LOCAL)
=====================

snapshot 생성
---------------

SQL) connect system/manager
Connected.
SQL) grant resource to saj;

Grant succeeded.

Database Link 생성
--------------------

SQL) create database link aixlink
  2  connect to system identified by manager
  3  using 't:tcaix:V716';

Database link created.

Create the snapshot
-------------------

SQL) connect saj/saj
Connected.
SQL) create snapshot deptsnap2
  2  pctfree 5
  3  pctused 60
  4  refresh complete
  5  start with sysdate
6  next sysdate + 1/(288*20)
    /* REFRESH EVERY 15 SECONDS */
  7  as select * from system.dept@aixlink;

snapshot created.

 

* m-view

No. 12181

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>

 

No. 11999

8I MATERIALIZED VIEW의 ON COMMIT REFRESH 기능
=============================================

기능 소개
---------

Oracle 7이나 8 버젼의 snapshot은 지정된 시간에 refresh 작업이 기동되는 반면,
Oracle 8i 버젼의 새로운 기능인 ON COMMIT refresh는 트랜잭션 COMMIT과 동시에
원격 MATERIALIZED VIEW(구 SNAPSHOT)에 대하여 refresh 작업이 기동된다.

범위
----

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

MATERIALIZED VIEW LOG 작성 예제
--------------------------------

drop materialized view log on emp;

create materialized view log on emp
with rowid (empno, ename, job, mgr, hiredate, sal, deptno)
including new values
/

select * from emp;

ON COMMIT refresh 기능을 위해서는 반드시 INCLUDING NEW VALUES 옵션을 사용
하여야 한다.

ON COMMIT MATERIALIZED VIEW 작성 예제
-------------------------------------

drop materialized view mv_emp;

create materialized view mv_emp
build immediate
refresh fast on commit
as
select count(*), deptno, sum(sal), count(sal)
from emp
group by deptno;

ON COMMIT refresh 기능을 위해서는 반드시 BUILD IMMEDIATE(default)와
ON COMMIT 옵션을 사용하여야 한다.

 

SQL> select * from mv_emp;

  COUNT(*)     DEPTNO   SUM(SAL) COUNT(SAL)
---------- ---------- ---------- ----------
         3         10       8750          3
         5         20      10875          5

SQL> select deptno from emp where empno = 7934;

    DEPTNO
----------
        10

SQL> update emp set deptno = 20 where empno = 7934;
SQL> commit;

SQL> select * from mv_emp;

  COUNT(*)     DEPTNO   SUM(SAL) COUNT(SAL)
---------- ---------- ---------- ----------
         2         10       7450          2
         6         20      12175          6

특성 및 제약 조건
-----------------

ON COMMIT refresh 사용에는 다음과 같은 제약 조건이 있다:

1. MATERIALIZED VIEW는 반드시 COUNT, SUM 등과 같은 aggregate 함수를
   가져야 하거나,
2. 죠인으로만 구성되어야 한다.
3. 하나의 테이블을 대상으로 반드시 COUNT(*) 함수가 기술되어야 한다.
4. GROUP BY 절에 의해서 grouping 대상이 되는 컬럼에 대하여
   COUNT(<column_name>) 함수가 반드시 기술되어야 한다.
5. Database Link 를 이용하는 remote db에서는 실행할 수 없다.
6. FAST REFRESH 기능을 사용할 경우에는 다음과 같은 제약 조건을 고려하여야
   한다:

   - FROM 절에는 뷰 지정은 가능하지 않고 베이스 테이블 지정만이 가능하다.
   - SYSDATE와 ROWNUM 지정은 가능하지 않다.
   - RAW 혹은 LONG RAW 데이타 타입에 대한 지정은 가능하지 않다.
   - HAVING이나 CONNECT BY 절을 포함할 수 없다.
   - WHERE 절에 죠인을 지정할 경우에는 AND로 구성된 equi-join 만이 가능하다.
   - 서브 질의, 인라인 뷰(INLINE VIEW), UNION이나 MINUS와 같은 집합 함수
     는 지원되지 않는다.

발생 가능한 오류 코드
--------------------

ORA-12051: ON COMMIT attribute is incompatible with other options
ORA-12054: cannot set the ON COMMIT refresh attribute for the
           materialized view

위의 두 경우 모두 ON COMMIT refresh를 수행할 수 없는 상황으로, 구사된
문법의 오류가 있는 지를 확인하여야 한다.

Reference Documents
-------------------
<Note:101705.1>

 

No. 17680

(V8I) MATERIALIZED VIEW 생성 시 ORA-12054 ERROR 해결 방법
=========================================================

PURPOSE
-------

   Oracle 8i부터 제공되는 기능인 Materialized view를 생성할 때
   single table에 대해 ON COMMIT refresh 옵션을 사용하여 생성 시
   발생할 수 있는 ORA-12054 에러의 해결방법에 대하여 알아보기로 한다.

SCOPE
-----
Materialized View Feature는 8i~9i Standard Edition에서는 지원하지
않는다.

Problem Description
-------------------

   다음과 같이 Materialized view를 생성하려고 시도할 때 ORA-12054
   에러가 발생한다.
   현재 테이블 test_v에 다음과 같은 데이타가 저장되어 있다고 가정한다.

   SQL> select * from test_v;

   KEY        BONUS        SEQ
   ----- ---------- ----------
   aa        120000          1
   aa        120000          2
   ab        120500          3
   ac        620000          4
   aa        120000          8
   ab        120500          9
   ac        620000         10     
   ....................

   현재 사용자가 원하는 형태의 출력 format은 다음과 같다.

   .....     SU          S1         S2         S3  ...
   .....     --  ---------- ---------- ----------  ....
   ....       a      720777     241000    1240000  .....     
   ................

   이와 같은 결과를 얻기 위해 아래와 같이 Materialized view를 생성하였다.

   1  create materialized view mv1
   2  build immediate
   3  refresh fast on commit
   4  as
   5  select count(*), substr(key, 1, 1),
   6         sum(decode(trim(key), 'aa', bonus, 0)) as s1,
   7         sum(decode(trim(key), 'ab', bonus, 0)) as s2,
   8         sum(decode(trim(key), 'ac', bonus, 0)) as s3,
   9         count(bonus)
  10  from test_v
  11* group by substr(key,1,1)
  SQL> /
      from test_v
           *
  ERROR at line 10:
  ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

  그런데, 이와 같이 ORA-12054 에러가 발생하면서 생성이 되지 않는다.

Workaround
----------
none

Solution Description
--------------------

   이 문제에 대한 해결책을 알아보기로 한다.

   GROUP BY 절에 의해서 grouping 대상이 되는 컬럼(예:key)에 대하여
   COUNT(<column_name>) 함수가 반드시 기술되어야 한다.
   이는 single table에 대하여 ON COMMIT refresh 특성을 갖는
   materialized view를 생성 시에 반드시 고려해야 할 제약사항 중 하나이다.

   For M.V.'s with Single-Table Aggregates, there are some conditions
   on refresh that need to be satisfied -

   Single Table Aggregates:
   =======================

   a) They can only have a single table.
   b) The SELECT list must contain all GROUP BY columns.
   c) Expressions are allowed in the GROUP BY and SELECT
      clauses provided they are the same.
   d) They cannot have a WHERE clause.
   e) They cannot have a MIN or MAX function.
   f) A materialized view log must exist on the table and must
      contain all columns referenced in the materialized view.
      The log must have been created with the INCLUDING NEW VALUES clause.
   g) If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
   h) If VARIANCE(expr) or STDDEV(expr) is specified,
      you must have COUNT(expr) and SUM(expr).

   위의 materialized view 생성 문장이 실패한 이유는 위의 제약 조건 중
   g)번을 위배했기 때문이다.
   즉, SUM(expr)에 대한 각각의 COUNT(expr) statement가 빠져 있기 때문이다.
   각 SUM(expr)에 대하여 다음과 같이 모든 COUNT 함수가 추가되어야 한다.

   "COUNT(DECODE(TRIM(key), 'aa ', bonus, 0)) AS c1"
 
   위와 같은 제약 조건에 따라서 사용자의 materialized view 생성 문장은
   다음과 같이 수정되어야 한다.

   SQL> create materialized view mv1
   build immediate
   refresh fast on commit
   as
   select count(*), substr(key,1,1),
          sum(decode(trim(key),'aa',bonus,0)) as s1,
          count(decode(trim(key),'aa',bonus,0)) as c1,
          sum(decode(trim(key),'ab',bonus,0)) as s2,
          count(decode(trim(key),'ab',bonus,0))as c2,
          sum(decode(trim(key),'ac',bonus,0)) as s3,
          count(decode(trim(key),'ac',bonus,0)) as c3
   from test_v
   group by substr(key,1,1)

   Materialized view created

Reference Documents
-------------------
<Note:101705.1>

[출처] Snapshot vs Materialized View|작성자 대보름