본문 바로가기
백엔드기술/개발환경

Materialized View

by RevFactory 2012. 2. 10.

◎ 이 문서는 Materialized View에 대해 8i부터 10g까지의 자료를 정리한 것이다.
◎ 목차:

1. Materialized View
2. Materialized View 관련 Initialization 파라미터
3. Materialized View 사용에 필요한 권한
4. Query Rewrite에서 Materialized View 사용 여부 판단 알고리즘
5. Materialized View와 Integrity Constraints
6. Query Rewrite와 Hint 사용
7. Three Types of Materialized Views
8. Materialized View Log 의 구조 (WITH ROWID, PRIMARY KEY 사용)
9. Materialized View의 문법
10. Materialized View 의 Index
11. Materialized View를 만드는 방법 (사용예제)
12. Materialized View에서 Index Organize Table을 이용하기 (8.1.6 이상)
13. Materialized View의 On Commit Refresh 기능 (8.1.6 이상)
14. Materialized View 생성 시 ORA-12054 ERROR 해결 방법 (8.1.6 이상)
15. Materialized View 에서 Order By 절 사용하기 (8.1.7 이상)
16. Materialized View를 Refresh 하는 방법
17. Materialized View와 관련된 시스템 딕셔너리
18. 진단을 위한 DBMS_MVIEW.EXPLAIN_REWRITE / EXPLAIN_MVIEW 사용법 (9.0.2 이상)
19. Nested Materialized Views


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;
8. Materialized View Log 의 구조 (WITH ROWID, PRIMARY KEY 사용)
◎ 우리가 Materialized View Log를 생성하면 Schema에는 mlog$_<master_table_name> 구조의
Log Table이 생성이 된다.
- Oracle은 이 Log에 변화되는 사항을 반영하게 된다. 그리고 Fast Refresh가 되면 이 Log의
데이터를 Materialized View에 반영하게 된다.
- 다음의 문장으로 확인을 할 수 있다.
select log_owner, master, log_table, rowids, primary_key
from dba_mview_logs;

LOG_OWNER MASTER LOG_TABLE ROWIDS PRIMARY_KEY
--------------- ---------- ----------- --------- -----------
SCOTT DEPT MLOG$_DEPT NO YES
◎ Log를 생성하기 위한 문법 구조
CREATE MATERIALIZED VIEW LOG ON <Master_Table_Name>
TABLESPACE <Tablespace_name>
PCTFREE <Percent_Of_Free_Space>
WITH [ ROWID | PRIMARY KEY ] , [ SEQUENCE ]
INCLUDING NEW VALUES ;
- WITH 절의 사용 예
1) WITH ROWID
SQL> create materialized view log on fnd_user with rowid including new values;
2) WITH ROWID(Col1, ... , ColN)
SQL> create materialized view log on fnd_user with rowid(user_id, user_name)
2 including new values;
3) WITH ROWID, SEQUENCE(Col1, ... , ColN)
SQL> create materialized view log on fnd_user with rowid, sequence(user_id, user_name)
2
including new values;
4) WITH ROWID, PRIMARY KEY
SQL> create materialized view log on wf_in with rowid, primary key including new values;
5) WITH PRIMARY KEY
SQL> create materialized view log on wf_in with primary key including new values;
6) WITH PRIMARY KEY, SEQUENCE(Col1, ... , ColN)
SQL> create materialized view log on wf_in with primary key, sequence(corrid)
2 including new values;
※ 주의: WITH ROWID(Col1, ... , ColN), SEQUENCE(Col1, ... , ColN) 의 문장은 가능하지 않다.
※ 주의:WITH ROWID(Col1, ... , ColN) 보다는 WITH ROWID, SEQUENCE(Col1, ... , ColN) 의 문장써라.
- WITH ROWID(Col1, ... , ColN) 와 WITH ROWID, SEQUENCE(Col1, ... , ColN)의 차이
☞ WITH ROWID(Col1, ... , ColN)는 순서가 부여되지 않는다.
☞ WITH ROWID, SEQUENCE(Col1, ... , ColN)는 SEQUENCE에 의해 컬럼의 순서가 부여 된다.
이는 나중에 Fast Refresh를 하기 위해 필요할 때가 있다.. 따라서 두번째 방법을 사용하라.
◎ Log를 생성시 Table의 구조..
1) ROWID를 가지고 Log를 생성할 때
SQL> create materialized view log on fnd_user with rowid including new values;
SQL> desc mlog$_fnd_user
Name Null? Type
------------------------------- -------- ----
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
SQL> drop materialized view log on fnd_user;
2) Primary Key 를 가지고 Log를 생성할 때
SQL> create materialized view log on wf_in with primary key including new values ; SQL> desc mlog$_wf_in
Name Null? Type
------------------------------- -------- ----
MSGID RAW(16)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255) SQL> drop materialized view log on wf_in;
3) ROWID와 NON-Primary Key 를 가지고 Log를 생성할 때
SQL> create materialized view log on fnd_user with rowid, sequence(user_id, user_name)
2 including new values ; SQL> desc mlog$_fnd_user
Name Null? Type
------------------------------- -------- ----
USER_ID NUMBER(15)
USER_NAME VARCHAR2(100)
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255) SQL> drop materialized view log on fnd_user;

4) ROWID와 Primary Key 를 가지고 Log를 생성할 때
SQL> create materialized view log on wf_in with rowid, Primary Key including new values ; SQL> desc mlog$_wf_in
Name Null? Type
------------------------------- -------- ----
MSGID RAW(16)
M_ROW$$ VARCHAR2(255)
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255) SQL> drop materialized view log on wf_in;
9. Materialized View의 문법
◎ 문법 :
CREATE MATERIALIZED VIEW <View_Name>
TABLESPACE <Tablespace_name>
BUILD [ IMMEDIATE | DEFERRED ]
REFRESH [ FAST | COMPLETE | FORCE ] ON [ DEMAND | COMMIT ]
START WITH <First_Refresh_Time> NEXT <Refresh_Time>
WITH [ ROWID | PRIMARY KEY ]
[ ENABLE | DISABLE ] QUERY REWRITE
AS
<Select_Statements>
◎ BUILD 절 (처음 MView를 어떻게 생성할 것인가에 대한 문장)
- IMMEDIATE : Default 값이다. 만드는 즉시 MV에 값이 생성이 된다.
- DEFERRED : 이 옵션을 이용해서 MView를 만들면 초기에는 값이 생성되어 있지 않는다.
그리고, DBMS_MVIEW.REFRESH()를 이용해서 Refresh 될 때 비로서 값이 생성된다.
단, 처음에는 반드시 Full Refresh를 수행해야지만 전체 값이 생성이 된다.
◎ REFRESH 절 (Refresh를 어떻게 할 것인가에 대한 문장)
- FAST : MV의 Master Table에 DML이 발생할 경우, 변경된 DML만 MV에 반영한다.
이는 MV가 FAST일 때 System이 자동으로 "Direct Loader Log"라는 것을 생성하고,
여기에 변화된 direct-path DML을 보관하고 있기 때문이다.
- COMPLETE : MV와 Master Table을 비교하면서 COMPLETE Refresh를 수행한다.
그리고 비록 MV가 FAST일지라도, COMPLETE Refresh를 수행하면 COMPLETE로 수행된다.
- FORCE : Default 값이다.
????
◎ ON 절 (언제 Refresh를 할 것인지에 대한 문장)
- DEMAND : Default 값이다.
Refresh는 DBMS_MVIEW.REFRESH()를 이용해서 Refresh 될 때 변경된 DML이 반영된다.
- COMMIT : DML이 발생한 후 Commit을 만나면 그 결과를 바로 MView에 반영한다.
*** 자세한 사항은 아래의 "13. Materialized View의 On Commit Refresh 기능"을 참조
◎ START WITH 절 : 처음 언제 Refresh를 할 것인지를 명시한다.
NEXT 절 : Refresh 주기를 몇시간으로 줄 것인지 명시한다.
◎ WITH 절
(Logging 정보를 명시하는 문장)
- ROWID : Master Table의 Primary Key가 없을 경우 ROWID를 이용해서 생성할 수 있다.
실제로 ROWID가 훨씬더 빠른 속도를 보장한다.
- PRIMARY KEY : Default 값이다. Master Table의 Primary Key를 이용해서 MView를 생성한다.
- WITH ROWID, PRIMARY KEY 이렇게 동시에 사용할 수도 있다.
10. Materialized View 의 Index
◎ Materialized View 는 Table에서 사용하는 Index를 다 사용할 수 있다.
- Oracle 9i 이상의 Materialized View에서는 Function Based Index 뿐만 아니라,
- Oracle 8i 이상에서는 Index Organize Table도 가능하다.
자세한 것은 "12. Materialized View에서 Index Organize Table을 이용하기" 참조.
◎ 예제..
SQL> desc FND_USER_MV
Name Null? Type
------------------------------- -------- ----
USER_ID NOT NULL NUMBER(15)
USER_NAME NOT NULL VARCHAR2(100)
COUNT(*) NUMBER

SQL> create index FND_USER_MV_N1 on FND_USER_MV(USER_ID);

Index created.
11. Materialized View를 만드는 방법 (사용예제)
◎ Materialized View를 만들때 고려사항
a. 만들려고 하는 Materialized View가 어떤 Type 인지..
b. Index는 어떻게 생성을 할 것인지.
c. Refresh 주기를 어떻게 설정할 것인지...
d. ON COMMIT을 사용할 지, 아니면 ON DEMAND를 사용할 지..
◎ 일반적으로 REFRESH FAST ON COMMIT 인 Materialized View는 실제 마스터 테이블의 DML 작업시
기존 보다 많은 부하가 마스터 테이블에 생성이 됩니다.
◎ 다음과 같은 Privileges를 갖어야 한다.
SQL> grant create any materialized view to disuser;
SQL> grant drop any materialized view to disuser;
SQL> grant alter any materialized view to disuser;
SQL> grant global query rewrite to disuser;
SQL> grant analyze any to disuser;
◎ Materialized View를 만들기 위한 전제 조건.
1) Materialized View의 대상이 되는 모든 Table은 Analyze 되어 있어야 합니다.
SQL> analyze table GL.GL_PERIOD_STATUSES compute statistics;
Table analyzed.
2) 사용되는 모든 MASTER Table의 컬럼에 대해 LOG Table을 생성합니다.
SQL> create materialized view log on applsys.fnd_user
2 with rowid, sequence(user_id, user_name) including new values;
3) On Commit Fast Refresh를 원한다면, Master Table이 존재하는 Schema에서 MView를 만들어야 함
- 그렇지 않으면 다음의 에러가 발생
*) "ORA-12015: cannot create a fast refresh materialized view from a complex query"
*) "ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view"
- 따라서 APPS와 같은 Schema에서 여러 User의 Table을 이용해서 Materialized View를 만든다면,
"REFRESH COMPLETE ON DEMAND" or "REFRESH FORCE ON DEMAND" 로 만들 수 밖에 없다.
- 그렇지 않고, 해당 Schema에 모든 Master Table에 존재하는 경우는 해당 Schema에
Materialized View를 만들고 이에 대한 Synonym을 주는 것이 가장 좋다.
◎ Single Table에 대한 Materialized View 만들는 방법:
1) Primary Key가 있는 Single Table에 대한 MV
*) Primary Key가 있는 Single Table 경우는 하나의 Row를 결정할 수 있는
Unique 한 값이 있기 때문에 어떤 방식으로든 쉽게 만들 수 있다.
i) WITH PRIMARY KEY를 이용해서 Log Table을 만든 경우
SQL> create materialized view log on applsys.wf_in
2 with primary key, sequence(corrid) including new values;
SQL> create materialized view applsys.wf_in_mv refresh fast
2 with primary key as select msgid, corrid from wf_in;
SQL> drop materialized view applsys.wf_in_mv;
SQL> create materialized view applsys.wf_in_mv build immediate refresh fast on commit
2 with primary key as select msgid, corrid from wf_in;
SQL> drop materialized view wf_in_mv;
SQL> create materialized view applsys.wf_in_mv build immediate refresh fast on commit
2 with primary key as select msgid, sum(corrid) from wf_in
3 where corrid <> '10' group by msgid;
ii) WITH ROWID를 이용해서 Log Table을 만든 경우
SQL> create materialized view log on wf_in
2 with rowid, sequence(msgid, corrid) including new values;
SQL> create materialized view wf_in_mv refresh fast
2 with rowid as select msgid, corrid from wf_in;
SQL> drop materialized view wf_in_mv;
SQL> create materialized view wf_in_mv build immediate refresh fast on commit
2 with rowid as select msgid, corrid from wf_in;
SQL> drop materialized view wf_in_mv;
SQL> create materialized view wf_in_mv build immediate refresh fast on commit
2 with rowid as select msgid, sum(corrid) from wf_in
3 where corrid <> '10' group by msgid;
2) Primary Key가 없는 Single Table에 대한 MV
*) Primary Key가 없는 Single Table 경우는 하나의 Row를 결정할 수 있는
Unique 한 값이 없기 때문에 Unique하게 만드는 방법을 써야만 한다.
*) 만약 Unique하게 하는 방법을 적용하지 않고 만든다면 다음의 에러가 난다.
SQL> create materialized view log on fnd_user
2 with rowid, sequence(user_id, user_name) including new values;

SQL> create materialized view fnd_user_mv refresh fast
2 as select user_id, user_name from fnd_user;
ERROR at line 1:
ORA-12014: table 'FND_USER' does not contain a primary key constraint
SQL> create materialized view fnd_user_mv refresh fast with rowid as
2 select distinct user_id, user_name from fnd_user;
ERROR at line 2:
ORA-12015: cannot create a fast refresh materialized view from a complex query

SQL> create materialized view log on fnd_user with rowid including new values;

SQL> create materialized view fnd_user_mv refresh fast with rowid
2 as select user_id, user_name, count(*) from fnd_user group by user_id, user_name;
ERROR at line 2:
ORA-12033: cannot use filter columns from materialized view log on "APPLSYS"."FND_USER"
*) 위의 에러를 해결하기 위해서는 하나의 Row를 Unique하게 만든는 방법을 적용해야 한다.
SQL> create materialized view log on fnd_user
2 with rowid, sequence(user_id, user_name) including new values;
SQL> create materialized view fnd_user_mv build immediate refresh fast with rowid
2 as select user_id, user_name, count(*) from fnd_user group by user_id, user_name;
SQL> drop materialized view fnd_user_mv;
SQL> create materialized view fnd_user_mv build immediate refresh fast on commit with rowid
2 as select user_id, user_name, count(*) from fnd_user group by user_id, user_name;

*) 또한 Aggregation이 있는 경우 반드시 Count(*)를 써서 Unique 성을 보장해야 한다.
SQL> create materialized view log on mv1
2 with rowid, sequence(key, bonus) including new values;
SQL> create materialized view mv1 build immediate refresh fast on commit
2 s
3 elect count(*), substr(key,1,1),
4 sum(decode(trim(key),'aa',bonus,0)) as s1,
5 count(decode(trim(key),'aa',bonus,0)) as c1,
6 sum(decode(trim(key),'ab',bonus,0)) as s2,
7 count(decode(trim(key),'ab',bonus,0))as c2,
8 sum(decode(trim(key),'ac',bonus,0)) as s3,
9 count(decode(trim(key),'ac',bonus,0)) as c3
10 from mv1
11 group by substr(key,1,1);
◎ Multiple Joined Table에 대한 Materialized View 만들는 방법:
※ Multiple Joined Table에 대한 Materialized View를 만들기 위해서는
반드시 해당 Table과 Column에 대한 Log Table을 만들어야 한다.
※ 그리고 두개의 Table이 Join이 되어서 결과가 나오는 구조이기 때문에,
각 Row에 대한 Unique 성을 보장하기 위해서 반드시 Count(*)를 사용 해야 합니다.
※ Materialized View를 만들 경우는 반드시 해당 User에서 만들는게 좋다.
※ 다음과 같은 SQL에 대해 Materialized View를 만드는 것을 가정하자.
create materialized view xen_emp_sal_mv
refresh fast on commit
as
select count(*) as cnt
,xef.employee_id
,xef.name
,xef.registration_number
,xsf.year
,xsf.month
,sum(xsf.salary) as salary
,count(xsf.salary) as cnt_salary
,sum(xsf.bonus) as bonus
,count(xsf.bonus) as cnt_bonus
,sum(xsf.education) as education
,count(xsf.education) as cnt_education
,sum(xsf.benefit) as benefit
,count(xsf.benefit) as cnt_benefit
from xen_employee_f xef
,xen_salary_f xsf
where xef.employee_id = xsf.employee_id
group by xef.employee_id, xef.name, xef.registration_number, xsf.year, xsf.month;
※ 이때 전제 사항은 xen_employee_f, xen_salary_f Table은 DISUSER Schema에 존재하고,
Materialized View 또한 DISUSER Schema에 만들면서, Fast Refresh, On Commit의
Materialized View를 만듬을 전제로 한다.
※ 그렇지 않고 APPS Schema에 만들고자 한다면, LOG는 DISUSER Schema에 생성하고,
APPS Schema에서 Materialized View는 REFRESH COMPLETE ON DEMAND로 만들어야 한다.
※ 그러면 다음과 같은 방법으로 만들 수 있다.
SQL> create materialized view log on disuser.xen_employee_f
2 with rowid, sequence(employee_id, name, registration_number)
3 including new values;

SQL> create materialized view log on disuser.xen_salary_f
2 with rowid,
3 sequence(salary_id, employee_id, year, month, salary, bonus, education, benefit)
4 including new values;

SQL> create materialized view xen_emp_sal_mv
2 tablespace euld
3 pctfree 10
4 build immediate
5 refresh fast on commit
6 enable query rewrite
7 as
8 select count(*) as cnt
9 ,xef.employee_id
10 ,xef.name
11 ,xef.registration_number
12 ,xsf.year
13 ,xsf.month
14 ,sum(xsf.salary) as salary
15 ,count(xsf.salary) as cnt_salary
16 ,sum(xsf.bonus) as bonus
17 ,count(xsf.bonus) as cnt_bonus
18 ,sum(xsf.education) as education
19 ,count(xsf.education) as cnt_education
20 ,sum(xsf.benefit) as benefit
21 ,count(xsf.benefit) as cnt_benefit
22 from xen_employee_f xef
23 ,xen_salary_f xsf
24 where xef.employee_id = xsf.employee_id
25 group by xef.employee_id, xef.name, xef.registration_number, xsf.year, xsf.month;
12. Materialized View에서 Index Organize Table을 이용하기 (8.1.6 이상)
◎ Oracle 8.1.6 이전 버젼에서 Materialized View의 Index 사용...
- Oracle 8i 이전에는 CREATE MATERIALIZED VIEW 는 'ORGANIZATION INDEX'를 가질수 없었다.
- 즉 MV 는 heap-organized table 만 사용가능하였다.
- 이전 version 에서 IOT 를 이용한 MV 를 생성시는 ora-905를 발생시킨다
◎ 하지만 Oracle 8.1.6 부터는 Heap 또는 Index-Organized Table(IOT)을 위한 logging이 가능하다

◎ 사용 예제
※ 다음과 같은 Mview가 있다고 가정하자
SQL> -- Create heap-organized table, test.t1
SQL> create table t1 (col1 number primary key, col2 varchar2(255));
SQL> -- Create Index-Organized Table (IOT), test.t1_iot
SQL> create table t1_iot (col1 number, col2 varchar2(255),
2 constraint t1_iot_pk primary key (col1))
3 organization index tablespace userdata
4 including col1 overflow tablespace userdata;
※ 두 가지 형태의 Index Organize Table을 만들어 보자.
SQL> -- Demonstrate IOT MV w/OVERFLOW on heap master, test.t1
SQL> create materialized view mv_t1 organization index
2 as select * from t1;
SQL> -- Demonstrate IOT MV w/OVERFLOW on IOT master, test.t1_iot
SQL> create materialized view mv_t1_iot
2 organization index
3 including col1 overflow tablespace userdata
4 refresh with primary key
5 as select * from t1_iot;
※ 데이터 딕셔너리에 대한 분석
SQL> -- Tables:
SQL> -- T1 := heap table (iot_type is null)< /FONT>
SQL> -- T1_IOT := iot (iot_type = 'IOT')
SQL> -- SYS_IOT_OVER_24894 := overflow segment for T1_IOT (object_id 24894)
SQL> -- Materialized Views:
SQL> -- MV_T1 := MV for master T1 (no associated OVERFLOW - see above CREATE)< /FONT>
SQL> -- MV_T1_IOT := MV for master T1_IOT
SQL> -- SYS_IOT_OVER_24900 := overflow segment for MV_T1_IOT (object_id 24900)
SQL> select table_name, iot_name, iot_type from dba_tables
2 where owner = 'TEST'
3 and (table_name like '%T1%' or iot_name like '%T1%')
4 order by table_name;
TABLE_NAME IOT_NAME IOT_TYPE
------------------------------ ---------------------- ------------
MV_T1 IOT
MV_T1_IOT IOT
SYS_IOT_OVER_24894 T1_IOT IOT_OVERFLOW
SYS_IOT_OVER_24900 MV_T1_IOT IOT_OVERFLOW
T1
T1_IOT IOT
6 rows selected.
SQL> -- MV Summary
SQL> select table_name, master, can_use_log, refresh_method
2 from dba_snapshots
3 where master in ('T1_IOT','T1');
TABLE_NAME MASTER CAN REFRESH_MET
------------------------------ -------------------- --- -----------
MV_T1 T1 YES PRIMARY KEY
MV_T1_IOT T1_IOT YES PRIMARY KEY
2 rows selected.
13. Materialized View의 On Commit Refresh 기능 (8.1.6 이상)
◎ Oracle 7이나 8 버젼의 snapshot은 지정된 시간에 refresh 작업이 기동되는 반면,
Oracle 8i 버젼의 새로운 기능인 ON COMMIT refresh는 트랜잭션 COMMIT과 동시에
원격 MATERIALIZED VIEW(구 SNAPSHOT)에 대하여 refresh 작업이 기동된다.
◎ Materialized View Log 작성 예제
- ON COMMIT refresh 기능을 위해서는 반드시 INCLUDING NEW VALUES 옵션을 사용 하여야 한다.
SQL> drop materialized view log on emp;
SQL> create materialized view log on emp
2 with rowid (empno, ename, job, mgr, hiredate, sal, deptno)
3 including new values;
SQL> select * from emp;

◎ ON COMMIT Materialized View 작성 예제
SQL> drop materialized view mv_emp;
SQL> create materialized view mv_emp
2 build immediate
3 refresh fast on commit
4 as
5 select count(*), deptno, sum(sal), count(sal)
6 from emp
7 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. 하나의 테이블을 대상으로 반드시 COUNT(*) 함수가 기술되어야 한다.
3. GROUP BY 절에 의해서 grouping 대상이 되는 컬럼은 반드시 COUNT(<column_name>)가 기술되어야 한다.
4. Database Link 를 이용하는 remote db에서는 실행할 수 없다.
5. FAST REFRESH 기능을 사용할 경우에는 다음과 같은 제약 조건을 고려하여야 한다:
- FROM 절에는 뷰 지정은 가능하지 않고 베이스 테이블 지정만이 가능하다.
- SYSDATE와 ROWNUM 지정은 가능하지 않다.
- RAW 혹은 LONG RAW 데이타 타입에 대한 지정은 가능하지 않다.
- HAVING이나 CONNECT BY 절을 포함할 수 없다.
- WHERE 절에 죠인을 지정할 경우에는 AND로 구성된 equi-join 만이 가능하다.
- 서브 질의, 인라인 뷰(INLINE VIEW), UNION이나 MINUS와 같은 집합 함수는 지원되지 않는다.

◎ 발생 가능한 오류 코드
- 두 경우 모두 ON COMMIT refresh를 수행할 수 없는 상황으로, 문법의 오류가를 검사하여야 한다.
o ORA-12051: ON COMMIT attribute is incompatible with other options
o ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
14. Materialized View 생성 시 ORA-12054 ERROR 해결 방법 (8.1.6 이상)
◎ Oracle 8i부터 제공되는 기능인 Materialized view를 생성할 때 single table에 대해 ON COMMIT refresh
옵션을 사용하여 생성 시 발생할 수 있는 ORA-12054 에러의 해결방법에 대하여 알아보기로 한다.

◎ 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를 생성하였다.
SQL> 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);
from test_v
*
ERROR at line 10:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
- 그런데, 이와 같이 ORA-12054 에러가 발생하면서 생성이 되지 않는다.

◎ 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 함수가 추가되어야 한다.
※ 위와 같은 제약 조건에 따라서 사용자의 materialized view 생성 문장은 다음과 같이 수정되어야 한다.
SQL> 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 count(decode(trim(key),'aa',bonus,0)) as c1,
8 sum(decode(trim(key),'ab',bonus,0)) as s2,
9 count(decode(trim(key),'ab',bonus,0))as c2,
10 sum(decode(trim(key),'ac',bonus,0)) as s3,
11 count(decode(trim(key),'ac',bonus,0)) as c3
12 from test_v
13 group by substr(key,1,1);
15. Materialized View 에서 Order By 절 사용하기 (8.1.7 이상)
◎ Materialized view를 생성하거나, INSERT-SELECT문에서, 보다 나은 성능을 위해 ORDER BY절을
사용할 수 있게 되었다. 이렇게 하여 table이나 materialized view에 data가 insert될 때,
지정된 order로 insert할 수 있어, insert된 order와 같은 순서로 select할 때 성능을 향상시킬 수 있다.
◎ 이렇게 처음 materialized view를 만들 때 ordering을 하면 data가 physical하게 clustering된다.
◎ 만약 order된 column에 대해 index가 생성되어 있을 경우, 그 index를 사용하여 materialized view의
data를 access하면, physical clustering되어 있으므로 I/O time이 현저히 줄어든다.
◎ Materialized view에서 ORDER BY절은 처음 CREATE시에만 사용할 수 있으며 full refresh 나
incremental refresh 때에는 사용할 수 없다.
◎ ORDER BY절은 materialized view의 definition에 포함되지 않으므로, 이로 인해 Oracle이 materialized
view를 detect하는 데에 변화는 없다. 또한 query rewrite도 ORDER BY절에 의해 영향을 받지 않는다.
◎ 사용 예제
SQL> create materialized view sales_ordered_date
2 tablespace sales_ts
3 -- enable query rewrite
4 as
5 select c.channel_desc, p.product_id, p.item_desc, s.customer_id, d.date_id, d.date_desc, s.units
6 from channels c, products p, days2 d, sales s
7 where c.channel_id = s.channel_id
8 and p.product_id = s.product_id
9 and d.date_id = s.date_id
10 order by d.date_id; -- date_id 순으로 Sorting을 한다.

SQL> alter materialized view sales_ordered_date enable query rewrite;

16. Materialized View를 Refresh 하는 방법
◎ Refresh를 해야하는 원인은 여러가지 이다. 예를 들어, Base Table 이 Truncate 되었고,
Fast Refresh가 아니라면, 사용자가 수동으로 Refresh를 해야 한다.
◎ Refresh를 수행한 Time 정보는 SYS.SNAP$ and SYS.MLOG$ 에 저장이 된다.
◎ DBMS_MVIEW.REFRESH() Package 사용
※ Refresh는 해당 MView가 어떤 속성을 갖느냐에 따라 다르다.
SQL> -- 해당 MView를 Fast Refresh를 하는 방법
SQL> dbms_mview.refresh('BOL_LEDGER_MV', 'F');
SQL> -- 해당 MView를 COMPLETE Refresh를 하는 방법
SQL> dbms_mview.refresh('BOL_LEDGER_MV', 'C');
SQL> -- 해당 MView를 일반적으로 Refresh를 하는 방법
SQL> dbms_mview.refresh('BOL_LEDGER_MV');
17. Materialized View와 관련된 시스템 딕셔너리
◎ 시스템 뷰
※ DBA_MVIEWS
- Materialized View에 대한 전체적인 정보를 보여준다.
※ DBA_MVIEW_LOGS
- Materialized View Log에 대한 전체적인 정보를 보여준다.
※ DBA_REGISTERED_SNAPSHOTS
- Materialized View에 대한 시스템 정보를 보여준다.
- 예를 들면, current_snapshots Column에는 마지막 Refresh 된 시간을 보여준다.
※ DBA_SNAPSHOT_LOGS
- Materialized View Log에 대한 시스템 정보를 보여준다.
◎ 예제..
※ MASTER Table ORDERS에 대한 Materialized View d의 정보를 조회해본다.
SQL> select log_owner, master, log_table
from dba_snapshot_logs
where master = 'ORDERS';

LOG_OWNER MASTER LOG_TABLE
-------------------- ------------- ---------------
SCOTT ORDERS MLOG$_ORDERS
SCOTT ORDERS MLOG$_ORDERS
※ MASTER Table ORDERS에 대한 Materialized View Location의 정보를 조회해본다.
SQL> select owner, name, snapshot_site
from dba_registered_snapshots
,dba_snapshot_logs
where dba_registered_snapshots.snapshot_id = dba_snapshot_logs.snapshot_id
and dba_snapshot_logs.master='ORDERS';

OWNER NAME SNAPSHOT_SITE
---------- ------------ ---------------
SCOTT ORDERS V804.WORLD
SCOTT SNAP_ORDERS NEGRIL.WORLD
※ MASTER Table FND_USER에 대한 가장 최근 Refresh Time을 조회해 본다.
SQL> select r.name, r.snapshot_site
,to_char(l.current_snapshots,'YYYY-MM-DD, HH:MI:SS') as refresh_date
from dba_registered_snapshots r
,dba_snapshot_logs l
where r.snapshot_id = l.snapshot_id
and l.master='FND_USER';
NAME SNAPSHOT_SITE REFRESH_DATE
--------------- ------------------- ---------------------
FND_USER_MV DEV 2005-03-31, 10:13:28
18. 진단을 위한 DBMS_MVIEW.EXPLAIN_REWRITE / EXPLAIN_MVIEW 사용법 (9.0.2 이상)
◎ Oracle 9i 이상에서는 DBMS_MVIEW에서는 diagnosing Query Rewrite 문제를 해결하기 위해
다음의 두가지 유용한 PL/SQL을 제공한다.:
※ EXPLAIN_REWRITE : Summary Materialized View를 사용할 때 왜 Query Rewrite가 안되는지 도움을 준다.
※ EXPLAIN_MVIEW : Summary Materialized View가 Query Rewrite 기능이 가능한지 알려준다.


◎ DBMS_MVIEW.EXPLAIN_REWRITE procedure

※ 이 procedure는 임의의 Query가 Rewite가 되는지 아닌지를 판단하는데 도움을 준다.
즉, 이 procedure를 수행하면 결과로 Rewrite가 되는지 아닌지와, 안되면 왜 안되는지
이유를 포함하는 Output을 발생하게 된다.
※ EXPLAIN_REWRITE을 수행하는데에는 두가지 방식이 있다.
하나는 output을 REWRITE_TABLE 에 저장을 하는 방식과, 다른 하나는 VARRAY에 저장을
하는 방식이다.
※ EXPLAIN_REWRITE Procedure의 구조는 다음과 같다.
1) REWRITE_TABLE 을 사용하는 EXPLAIN_REWRITE Procedure
PROCEDURE EXPLAIN_REWRITE (QUERY IN VARCHAR2,
MV IN VARCHAR2 := NULL,
STATEMENT_ID IN VARCHAR2 := NULL);
---
2) VARRAY 를 사용하는 EXPLAIN_REWRITE Procedure
PROCEDURE EXPLAIN_REWRITE (QUERY IN VARCHAR2,
MV IN VARCHAR2 := NULL,
MSG_ARRAY IN OUT SYS.RewriteArrayType);

※ EXPLAIN_REWRITE Procedure의 QUERY Parameter의 길이는 Max 32767 characters 이다.
※ DBMS_MVIEW.EXPLAIN_REWRITE 를 어떻게 사용하는지 예제는 다음의 File에 들어 있다.
(9.2.0)$ORACLE_HOME/rdbms/demo/smxrw.sql

※ 예제 1) REWRITE_TABLE 을 사용하는 예제
0) 준비사항으로 먼저 REWRITE_TABLE 을 만들어야 한다.
SQL> (9.2.0)$ORACLE_HOME/rdbms/admin/utlxrw.sql -- 이 SQL을 수행하면 테이블이 생성된다.
1) 다음과 같은 MV를 가정하자.
create materialized view mvj
enable query rewrite as
select dim1.dk1, dim2.dk2
from fact, dim1, dim2
where dim1.dk1 = fact.dk1
and dim2.dk2 = fact.dk2;

2) 그리고 원하는 Query가 다음과 같다고 가정하자.

select dim1.dk1, dim2.dk2
from fact, dim1, dim2, dim3
where dim1.dk1 = fact.dk1
and dim2.dk2 = fact.dk2
and dim2.dk2 = 1;

3) 이제 위 Query가 Query Rewrite가 되는지 아닌지 판단해 보자.

SQL> truncate table rewrite_table;

SQL> declare
2 query varchar2(256) := 'select dim1.dk1, dim2.dk2
3 from fact, dim1, dim2, dim3
4 where dim1.dk1 = fact.dk1
5 and dim2.dk2 = fact.dk2
6 and dim2.dk2 = 1';
7 begin
8 dbms_mview.explain_rewrite(query);
9 end;
SQL> /

SQL> select message from rewrite_table order by sequence;

** Here is example output:
MESSAGE
-----------------------------------------------------------------
QSM-01033: query rewritten with materialized view, MVJ

** Here is example output from another case where rewrite did not work:
MESSAGE
---------------------------------------------------------------------------
QSM-01094: outer-join filter not found in materialized join view
QSM-01105: no primary key or row id in MV, MVJO, is found for table, DIM1


※ 예제 2) VARRAY를 사용하는 예제
1) 원하는 MV와 원하는 Query는 위와 동일하다고 가정하자
2) 다음과 같은 Procedure를 만들자.
$ vi test.sql

set serveroutput on

declare
Rewrite_Array sys.rewriteArrayType := SYS.RewriteArrayType();
querytxt varchar2(1000) :=
'select dim1.dk1, dim2.dk2
from fact, dim1, dim2, dim3
where dim1.dk1 = fact.dk1
and dim2.dk2 = fact.dk2
and dim2.dk2 = 1';
msg_no number;
i number;
begin
dbms_snapshot.explain_rewrite(querytxt, NULL, Rewrite_Array);
msg_no := rewrite_array.count;
for i in 1..msg_no
loop
dbms_output.put_line('MV Name: ' ||Rewrite_Array(i).mv_name);
dbms_output.put_line('Query : ' ||Rewrite_Array(i).query_text);
dbms_output.put_line('Message: ' ||Rewrite_Array(i).message);
end loop;
end;
/

** Here is output for our example query and MV:

MV Name:
Query : select dim1.dk1, dim2.dk2 from fact, dim1, dim2, dim3 where
dim1.dk1 = fact.dk1 and dim2.dk2 = fact.dk2 and dim2.dk2 = 1
Message: QSM-01033: query rewritten with materialized view, MVJO1


◎ DBMS_MVIEW.EXPLAIN_MVIEW procedure

※ 이 procedure는 임의의 Materialized View 를 분석하여 MV_CAPABILITIES_TABLE Table에
분석 결과를 저장한다.
※ MV_CAPABILITIES_TABLE 테이블을 만들기 위해서는 다음을 수행하면 된다.
SQL> (9.2.0)$ORACLE_HOME/rdbms/admin/utlxmv.sql

※ 사용 방법
1) 원하는 MV와 원하는 Query는 위와 동일하다고 가정하자
2) 다음의 작업을 수행하라
SQL> truncate table mv_capabilities_table;
SQL> execute dbms_mview.explain_mview('MVJ');
SQL> select capability_name, possible from mv_capabilities_table
2 where mvname='MVJ' and capability_name like 'REWRITE%';
CAPABILITY_NAME P
------------------------------ -
REWRITE Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N
※ 자세한 사용 예제는 다음의 File에 잘 나와 있다.
(9.2.0)$ORACLE_HOME/rdbms/demo/smxmv1.sql
19. Nested Materialized Views
◎ Nested Materialized Views 란 MView안에 MView가 존재하는 형태이다.
따라서 Nested Materialized Views는 매우 복잡한 형태를 지니게 된다.
◎ 하지만 Nested Materialized Views 의 장점에도 불구하고 Fast Refresh에 대한 문제가 존재한다.
◎ 다음은 Nested Materialized Views에서 Fast Refresh가 가능하게 하는 방법이다.
a) Master Table에 대한 MView Log를 Rowid 사용해서 만든다.
b) Nested MView는 single-table aggregate 와 join view View로 구성이 되어야 한다.
c) single-table aggregate materialized view는 materialized join view 보다 먼저 와야 한다.
◎ 만약 Nested Materialized Views를 만들다 실패할 경우 다음의 에러가 발생한다.
Error: ORA-12053 (ORA-12053)
Text: This is not a valid nested materialized view

◎ Nested Materialized Views의 사용 예제
※ 다음의 Table을 고려해 보자
SQL> desc emp
Name Null? Type
----------------------------------------- -------- --------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> desc dept
Name Null? Type
----------------------------------------- -------- --------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
※ 위 두 테이블을 이용해서 MView를 만들어 보자
SQL> create materialized view log on emp with primary key, rowid;

SQL> create materialized view log on dept with primary key, rowid;
SQL> -- create the first MV as join MV, Rowid columns were added to enable
SQL> -- fast refresh on a join view...
SQL> create materialized view empmv1
refresh fast on demand
with primary key
as
select e.empno,d.deptno,e.ename,d.dname, e.rowid erowid,d.rowid drowid
from emp e, dept d
where e.deptno = d.deptno ;

SQL> alter table empmv1 add primary key(empno);

SQL> -- create materialized view log for the empmv1:
sql> create materialized view log on empmv1 with primary key,
rowid(deptno) including new values;

SQL> -- now create the nested materialized view empmv2:
SQL> create materialized view empmv2
refresh fast on demand
with primary key
as select empno, deptno
from empmv1 ; -- MView에서 가져온다.
from empmv1
*
ERROR at line 5:
ORA-12053: this is not a valid nested materialized view

SQL> -- we are missing one rule here , empmv2 has to be a single-table aggregate..
SQL> create materialized view empmv2
refresh fast on demand
with primary key
as select empno,deptno , count(*) -- Unique 성을 보장하기 위해
from empmv1
group by empno,deptno ;
◎ Oracle 9.2 부터는 single-table aggregates 와 join views 를 같이 사용할 수 있게 되었다
※ 사용 예제 (아래의 예제는 Oracle 9.2 이하에서는 ora-12053에러가 발생한다.)
SQL> -- create demo tables in Scott schema in 9.2 database and run the following ..
SQL> alter table emp add primary key(empno);

SQL> alter table dept add primary key(deptno);

SQL> create materialized view log on dept with primary key,
rowid(dname) including new values;

SQL> create materialized view log on emp with primary key,
rowid(deptno, sal) including new values;

SQL> create materialized view deptmv1
refresh fast on demand
with primary key
as select empno,deptno , count(sal) cnt_sal from emp
group by empno,deptno ;

SQL> alter table deptmv1 add primary key(empno);

SQL> create materialized view log on deptmv1 with primary key,
rowid(deptno, cnt_sal) including new values ;

SQL> create materialized view deptmv2
refresh fast on demand
with primary key
as
select empno,cnt_sal,dname,e.rowid erowid,d.rowid drowid
from deptmv1 e -- Mview가 먼저와야 한다.
,dept d
where e.deptno = d.deptno ;
◎ Nested Materialized Views 를 사용할 때의 제약사항
1. Nested materialized views 를 Refresh하고자 한다면 순서적으로 Refresh 되어야 한다.
예를 들어, 먼저 Empmv1을 수행하고 다음에 Empmv2을 수행해야 한다.
2. ON COMMIT에 대한 Fast refresh는 single-table aggregates 와 join views 를
같이 사용할 경우 지원되지 않는다.


출처 : http://blog.naver.com/khlee1000/40023271788

참고 : http://realcmc.blog.me/130037079275

 

저작자표시

'백엔드기술 > 개발환경' 카테고리의 다른 글

DB 연결 스트링  (0) 2012.10.16
리눅스 서버 정보 명령어  (0) 2011.11.17
리눅스 실무 - 명령어 정리  (0) 2011.08.31

관련글

티스토리툴바