上一篇对oracle中物化视图有了一些基本了解，知道物化视图日志的创建方式。在这篇文章中，将重点讲解
物化视图的刷新方式，这也是一个比较难的知识点，和大家以前学习。因为很长一段时间不用mv，说真的，我
基本上都忘的差不多了，最近的项目涉及到物化视图的一个几个库（超过3T）的迁移，所以温习下。
我们通过查看试图dba_mviews的REFRESH_METHOD字段可以发现该自动有如下几种属性，换句话说也就是说
物化视图有如下几种刷新方式：
COMPLETE (C) - Materialized view is completely refreshed from the masters
FORCE (?) - Oracle Database performs a fast refresh if possible, otherwise a complete refresh
FAST (F) - Oracle Database performs an incremental refresh applying changes that correspond to changes
in the masters since the last refresh
NEVER (N) - User specified that the Oracle Database should not refresh this materialized view
简单在总结为如下几点:
compelete: 完全刷新，在压力比较大的情况下，完全刷新可能会带来很多问题，特别是跨站点的情况，对网络要求很高。
force：强制刷新，竟可能的使用增量快速刷新，如果不能则使用完全刷新。默认值是force。
fast： 增量快速刷新。
never：从不刷新。
还有一个字段BUILD_MODE，这是表明创建mv的方式，说白了就是指在创建mv时是否生成数据。默认有如下几种：
IMMEDIATE - Populated from the masters during creation
DEFERRED - Not populated during creation. Must be explicitly populated later by the user.
PREBUILT - Populated with an existing table during creation. The relationship of the contents of this prebuilt table
to the materialized view's masters is unknown to the Oracle Database.
简单总结为如下几点：
immediate：创建mv时就生成数据了，也就是在创建mv时就会进行一次完全刷新，同步数据。默认方式。
deferred: 在创建mv时不生成数据，后面跟你的操作实际需要才生成数据，换句话说，使用这种方式创建mv时，当你查询
mv时数据是空的，比如你手工刷新以后，才能查到数据。
prebuilt: 创建时需要先存在跟物化视图存在相同的对象，不然会报错ora-12059。
如下例子：
SQL> create table t1 as select * from sys.dba_objects where rownum < 10001;
Table created.
SQL> create table t2 as select * t1;
Table created.
SQL>
SQL> alter table t1 add constraint pk_t1 primary key(object_id) ;
Table altered.
SQL> alter table t2 add constraint pk_t2 primary key(object_id) ;
Table altered.
SQL> create materialized view log on t1;
Materialized view log created.
SQL> create materialized view log on t2;
Materialized view log created.
SQL> create materialized view mv_tab1 refresh fast on commit as
2 select a.owner,b.object_name from t1 a,t2 b
3 where a.object_id=b.object_id;
select a.owner,b.object_name from t1 a,t2 b
*
ERROR at line 2:
ORA-12052: cannot fast refresh materialized view KILLDB.MV_TAB1
---conn /as sysdba
SQL> delete mv_capabilities_table;
92 rows deleted.
SQL> commit;
Commit complete.
SQL> begin
2 dbms_mview.explain_mview('select a.owner,b.object_name from killdb.t1 a,killdb.t2 b where a.object_id=b.object_id');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT';
MSGTXT
------------------------------------------------------------------------------------------------------------------------
the SELECT list does not have the rowids of all the detail tables
mv log must have ROWID
mv log must have ROWID
SQL>
—conn killdb/killdb
SQL> create materialized view mv_tab1 refresh force on commit as
2 select a.owner,b.object_name from t1 a,t2 b
3 where a.object_id=b.object_id;
Materialized view created.
SQL>
SQL> select owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE
2 from sys.dba_mviews where MVIEW_NAME='MV_TAB1';
OWNER MVIEW_NAME R REFRES REFRESH_ BUILD_MOD
--------------- -------------------- - ------ -------- ---------
KILLDB MV_TAB1 N COMMIT FORCE IMMEDIATE
SQL>
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
9562
SQL>
SQL> drop materialized view mv_tab1;
Materialized view dropped.
SQL> create materialized view mv_tab1 build deferred refresh force on commit as
2 select a.owner,b.object_name from t1 a,t2 b
3 where a.object_id=b.object_id;
Materialized view created.
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
0
SQL> exec dbms_mview.refresh('MV_TAB1');
PL/SQL procedure successfully completed.
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
9562
SQL>
SQL> drop materialized view mv_tab1;
Materialized view dropped.
SQL> create materialized view mv_tab1 on prebuilt table with reduced precision as
2 select a.owner,b.object_name from t1 a,t2 b
3 where a.object_id=b.object_id;
create materialized view mv_tab1 on prebuilt table with reduced precision as
*
ERROR at line 1:
ORA-12059: prebuilt table "KILLDB"."MV_TAB1" does not exist
SQL> !
[ora10g@killdb ~]$ oerr ora 12059
12059, 00000, "prebuilt table \"%s\".\"%s\" does not exist"
// *Cause: The specified prebuilt table did not exist.
// *Action: Reissue the SQL command using BUILD IMMEDIATE, BUILD DEFERRED, or
// ensure that the prebuilt table exists.
//
[ora10g@killdb ~]$
[ora10g@killdb ~]$ exit
exit
SQL> create materialized view mv_tab1 refresh force on commit as
2 select a.owner,b.object_name from t1 a,t2 b
3 where a.object_id=b.object_id;
Materialized view created.
SQL> create table mv_tab2 as select a.owner,b.object_name from t1 a,t2 b
2 where a.object_id=b.object_id;
Table created.
SQL> create materialized view mv_tab2 on prebuilt table with reduced precision as
2 select a.owner,b.object_name from t1 a,t2 b
3 where a.object_id=b.object_id;
Materialized view created.
SQL>
SQL> select owner,MVIEW_NAME,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,BUILD_MODE
2 from sys.dba_mviews where owner='KILLDB';
OWNER MVIEW_NAME R REFRES REFRESH_ BUILD_MOD
--------------- -------------------- - ------ -------- ---------
KILLDB MV_TAB1 N COMMIT FORCE IMMEDIATE
KILLDB MV_TAB2 N DEMAND FORCE PREBUILT
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
9562
SQL> select count(*) from mv_tab2;
COUNT(*)
----------
9562
SQL> delete from t1 where object_id < 101;
99 rows deleted.
SQL> delete from t2 where object_id < 101;
99 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
9463
SQL> select count(*) from mv_tab2;
COUNT(*)
----------
9562
SQL> exec dbms_mview.refresh('MV_TAB2');
PL/SQL procedure successfully completed.
SQL> select count(*) from mv_tab2;
COUNT(*)
----------
9463
由于通常是用快速刷新，所以这里重点描述下快速刷新。要创建快速刷新的物化视图，需要满足一些列条件：
所以快速刷新的物化视图必须满足下面2点：
1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；
2.物化视图不能包含对LONG和LONG RAW数据类型的引用。
只包含连接的物化视图：
1.必须满足所有快速刷新物化视图都满足的条件；
2.不能包括GROUP BY语句或聚集操作；
3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上；
4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。
5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。
从我们前面的例子都可以看到，必须创建物化视图日志，且必须是基于rowid方式，不然就会报错。
如果是包含子查询的物化视图，就更复杂了。看下面的例子：
SQL> drop materialized view mv_tab1;
Materialized view dropped.
SQL> drop materialized view mv_tab2;
Materialized view dropped.
SQL> drop materialized view mv_tab1;
Materialized view dropped.
SQL> drop materialized view mv_tab2;
Materialized view dropped.
SQL> create table t1 as select * from sys.dba_objects;
Table created.
SQL> alter table t1 add constraint pk_t1 primary key(object_id) ;
Table altered.
SQL> create materialized view log on t1 with primary key;
Materialized view log created.
SQL> create materialized view mv_tab1 REFRESH FAST FOR UPDATE AS
2 select * from t1 where object_id > 10000 and object_id < 30001;
Materialized view created.
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
19890
SQL> delete from t1 where object_id > 20000 and object_id < 25000;
4999 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
19890
SQL> exec dbms_mview.refresh('MV_TAB1');
PL/SQL procedure successfully completed.
SQL> select count(*) from mv_tab1;
COUNT(*)
----------
14891
关于物化视图快速刷新涉及子查询的情况，有一些限制，如下（来自官方文档）：
1)Materialized views must be primary key materialized views.
---基表必须存在主键
2)The master's materialized view log must include certain columns referenced in the subquery. For information about
which columns must be included, see "Logging Columns in the Materialized View Log".
---基表的物化视图日志必须包含子查询涉及的列。
3) If the subquery is many to many or one to many, join columns that are not part of a primary key must be included
in the materialized view log of the master. This restriction does not apply to many to one subqueries.
---对于多对多或一对多的子查询，join 列可以不是主键的一部分，但是必须是基表物化视图日志的一部分。多对一的情况不存在这个限制。
4) The subquery must be a positive subquery. For example, you can use the EXISTS condition, but not the NOT EXISTS condition.
---子查询必须是积极的，例如你的条件可以使用exists，但是不能使用not exists。
5) The subquery must use EXISTS to connect each nested level (IN is not allowed).
---对于嵌套的子查询，必须使用exists，不能使用in。
6) Each table can be in only one EXISTS expression.
---每个表只能被exists引用1次。
7) The join expression must use exact match or equality comparisons (that is, equi-joins).
--连接表达式必须使用精确匹配或"=" 进行操作。
8) Each table can be joined only once within the subquery.
---每个表在子查询中只能被join 1次。
9 A primary key must exist for each table at each nested level.
---对于嵌套的字句每一层涉及的表都必须存在主键。
10) Each nested level can only reference the table in the level above it.
---每个嵌套层只能引用其上层嵌套的表。
11) Subqueries can include AND conditions, but each OR condition can only reference columns contained within one row.
Multiple OR conditions within a subquery can be connected with an AND condition.
---子查询中可以包含and条件，如果存在or，那么每个条件只能引用一个列。对于多个or存在的情况，可以和and 条件
进行关联。
12) All tables referenced in a subquery must reside in the same master site or master materialized view site.
---子查询中所有引用的表在master站点或master MV站点中都必须是存在的。
如果不满足条件，可能包如下类似的很多错误：
SQL> create materialized view mv_tab3 refresh fast on commit as
2 select * from t3 where EXISTS (select * from t4 where object_id like '2%')
3 and object_id > 10000 and object_id < 25000;
and object_id > 10000 and object_id < 25000
*
ERROR at line 3:
ORA-12014: table 'T4' does not contain a primary key constraint
SQL> create materialized view mv_tab3 refresh fast on commit as
2 select * from t3 where EXISTS (select * from t4 where object_id like '2%')
3 and object_id > 10000 and object_id < 25000;
and object_id > 10000 and object_id < 25000
*
ERROR at line 3:
ORA-12014: table 'T4' does not contain a primary key constraint
SQL> alter table t4 add constraint pk_t4 primary key(object_id) ;
Table altered.
SQL> create materialized view mv_tab3 refresh fast on commit as
2 select * from t3 where EXISTS (select * from t4 where object_id like '2%')
3 and object_id > 10000 and object_id < 25000;
and object_id > 10000 and object_id < 25000
*
ERROR at line 3:
ORA-23413: table "KILLDB"."T4" does not have a materialized view log
SQL> create materialized view log on t4 with primary key;
Materialized view log created.
SQL> create materialized view mv_tab3 refresh fast on commit as
2 select * from t3 where EXISTS (select * from t4 where object_id like '2%')
3 and object_id > 10000 and object_id < 25000;
and object_id > 10000 and object_id < 25000
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
SQL>
SQL> create materialized view mv_tab4 refresh start with sysdate next sysdate+1/24
2 as select * from t4 where object_id < 30001;
Materialized view created.
SQL> select job,next_date,next_sec,interval,what from user_jobs;
JOB NEXT_DATE NEXT_SEC INTERVAL WHAT
------ --------- ---------------- ---------------- ---------------------------------------------
21 22-AUG-12 04:13:38 sysdate+1/24 dbms_refresh.refresh('"KILLDB"."MV_TAB4"');
另外，关于快速刷新，还涉及到很多内容，如下内容来自老杨的博客：
+++++++++ 关于包含聚合的物化视图
1.必须满足所有快速刷新物化视图都满足的条件；
2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：
(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；
(2)必须指明ROWID和INCLUDING NEW VALUES；
(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。
3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；
4.必须指定COUNT(*)；
5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在；
比如：包含SUM(a)，则必须同时包含COUNT(a)。
6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；
Oracle推荐同时包括SUM(expr*expr)。
7.SELECT列表中必须包括所有的GROUP BY列；
8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；
物化视图包含MIN或MAX聚集函数；
物化视图包含SUM(expr)，但是没有包括COUNT(expr)；
物化视图没有包含COUNT(*)。
注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，
否则物化视图至此以后都不再自动刷新，且不会报任何错误。
9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；
10.如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。
11.如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，
快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。
12.对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件：
SELECT语句列表中应该包含GROUPING标识符：可以是GROUP BY表达式中所有列的GROUPING_ID函数，也可以是GROUP BY表达式中每一列的GROUPING函数；
例如：GROUP BY语句为：GROUP BY CUBE(a, b)，则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。
GROUP BY不能产生重复的GROUPING。
比如：GROUP BY a, ROLLUP(a, b)则不支持快速刷新，因为包含了重复的GROUPING：(a), (a, b), (a)。
++++++++ 包含UNION ALL的物化视图：
1.UNION ALL操作必须在查询的顶层。可以有一种情况例外：UNION ALL在第二层，而第一层的查询语句为SELECT * FROM；
2.被UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件；
3.SELECT列表中必须包含一列维护列，叫做UNION ALL标识符，每个UNION ALL分支的标识符列应包含不同的常量值；
4.不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询；
5.不支持基于分区改变跟踪（PCT）的刷新；
6.兼容性设置应设置为9.2.0。
++++++++ 嵌套物化视图：
1. 嵌套物化视图的每层都必须满足快速刷新的限制条件；
2. 对于同时包含聚集和连接的嵌套物化视图，不支持ON COMMIT的快速刷新。
关于物化视图的内容非常多，详细的东西参考官方文档，老杨的这部分内容其实都有些老了，部分是9i的。
另外还有MV GROUP等等，不多说了。
回复 Dong_2 取消回复