Oracle物化视图介绍

Oracle 物化视图说明

1 详细语法

CREATE MATERIALIZED VIEW [mv_name] [

TABLESPACE [ts_name] -- 指定表空间

PARALLEL (DEGREE 2) -- 并行度设计为2（与目前的CPU个数相等） BUILD [IMMEDIATE|DEFERRED] -- 创建是否产生数据 REFRESH [FAST|COMPLETE|FORCE] -- 快速、完全刷新

[ON COMMIT|ON DEMAND START WITH (start_time) NEXT (next_time)] -- 刷新方式 ENABLE QUERY REWRITE -- 查询重写 ] AS

{创建物化视图用的查询语句};

2 语法解释

以上是Oracle创建物化视图（Materialized View,以下简称MV）时的常用语法,各参数的含义如下:

1、创建方式（Build Methods）

包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据，而BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。

2、刷新（Refresh）：

指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVE*。**ST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。

refresh [fast|complete|force] 视图刷新的方式:

fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化，建立增量刷新物化视图

还需要一个物化视图日志表。create materialized view log on （主表名）。

complete: 全部刷新。相当于重新执行一次创建视图的查询语句。

force: 这是默认的数据刷新方式。当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。

MV数据刷新的时间：

on demand: 在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）

on commit: 当主表中有数据提交的时候，立即刷新MV中的数据；

start ??: 从指定的时间开始，每隔一段时间（由next指定）就刷新一次； Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

3、查询重写（Query Rewrite）：

包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。

3 物化视图日志

物化视图日志：如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

1、详细语法

CREATE MATERIALIZED VIEW LOG ON [t_name] -- 表名 tablespace [ts_name] -- 日志保存在特定的表空间

WITH [ROWID|PRIMARY KEY|SEQUENCE]; -- 日志类型

4 物化视图类型

物化视图可以分为以下三种类型：只包含连接的物化视图；包含聚集的物化视图；嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。

5 各类型快速刷新的限制

所有类型的快速刷新物化视图都必须满足的条件：

1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM； 2.物化视图不能包含对LONG和LONG RAW数据类型的引用。

5.1 只包含连接的物化视图

1.必须满足所有快速刷新物化视图都满足的条件； 2.不能包括GROUP BY语句或聚集操作；

3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上；

4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。

5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。 6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。

下面，我们看一个满足快速刷新条件的例子，然后依次违反上述6个条件，分别测试，查看会出现什么错误。

首先，创建测试环境：

SQL> create table dim_a (id number primary key, name varchar2(30)); 表已创建。

SQL> create table dim_b (id number primary key, name varchar2(30)); 表已创建。

SQL> create table fact (id number, aid number, bid number, num number); 表已创建。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id); 表已更改。

SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id); 表已更改。

SQL> insert into dim_a select rownum, 'a'||rownum from user_objects; 已创建74行。

SQL> insert into dim_b select rownum, 'b'||rownum from user_objects; 已创建74行。

SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2

2 from user_objects; 已创建74行。 SQL> commit; 提交完成。

接下来建立一个可以成功快速刷新的例子，这个物化视图只包含连接： SQL> create materialized view log on dim_a with rowid; 实体化视图日志已创建。

SQL> create materialized view log on dim_b with rowid; 实体化视图日志已创建。

SQL> create materialized view log on fact with rowid; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id; 实体化视图已创建。

下面我们依次违反上面的6个条件，观察错误信息。 1.包含SYSDATE/ROWNUM或RAW/LONG RAW数据类型。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num, sysdate time 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性

错误提示是不能设置ON COMMIT属性，去掉ON COMMIT语句再次尝试： SQL> create materialized view mv_fact_err refresh fast as

2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num, sysdate time 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

根据预期一样，建立快速刷新的物化视图报错，不过提示信息不明确，很难定位具体问题。使用ROWNUM后报错的提示信息和使用SYSDATE的一样。

可以通过DBMS_MVIEW包的EXPLAIN_MVIEW过程来得到更加详细的错误信息。有关这个包的具体使用方法可以参考：http://blog.itpub.net/post/468/13318 SQL> begin

2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,

3 a.name a_name, b.name b_name, num, rownum row_id 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id'); 7 end; 8 /

PL/SQL 过程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT'; MSGTXT

--------------------------------------- 在实体化视图中引用了 ROWNUM

使用这种方法就可以得到更加准确的错误信息了。

测试另外一个约束之前要说明一下，Oracle文档上给出的约束条件是不能包括RAW和LONG RAW类型，根据我的测试结果，估计是文档出现了错误，这里应该是LONG和LONG RAW类型。

SQL> alter table dim_a add (col_raw raw(10) default '1'); 表已更改。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num, a.col_raw 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id; 实体化视图已创建。

SQL> update dim_a set col_raw = '2'; 已更新74行。 SQL> commit; 提交完成。

SQL> select distinct col_raw from mv_fact_err; COL_RAW

-------------------- 02

SQL> drop materialized view mv_fact_err; 实体化视图已删除。

SQL> alter table dim_a drop (col_raw); 表已更改。

SQL> alter table dim_a add (col_long long default '1'); 表已更改。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num, a.col_long 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id

6 and f.bid = b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-00997: 非法使用 LONG 数据类型

SQL> alter table dim_a drop (col_long); 表已更改。

2.不能包括GROUP BY语句或聚集操作

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, count(num) count_num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id

7 group by f.rowid, a.rowid, b.rowid, f.id, a.name, b.name; from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-32401: \上的实体化视图日志没有新值

不管是否存在连接，只要包括聚集操作，就要满足聚集物化视图快速刷新的条件。这个错误提示正是聚集物化视图快速刷新需要满足的条件之一。

3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上： SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid = b.id; 实体化视图已创建。

SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> alter table fact drop constraint fk_fact_aid; 表已更改。

SQL> alter table dim_a drop primary key; 表已更改。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid = b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-12052: 无法快速刷新实体化视图 YANGTK.MV_FACT

这个错误提示不是很明确，但是Oracle错误消息文档上对这个错误产生的原因已经分析的很清楚了。

ORA-12052 cannot fast refresh materialized view string.string

Cause: Either ROWIDs of certain tables were missing in the definition or the inner table of an outer join did not have UNIQUE constraints on join columns.

Action: Specify the FORCE or COMPLETE option. If this error occurred during creation, the materialized view definition may have been changed. Refer to the documentation on materialized views. SQL> delete mv_capabilities_table; 已删除13行。 SQL> begin

2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,

3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid = b.id'); 7 end; 8 /

PL/SQL 过程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT'; MSGTXT

---------------------------------------------------------- 在内部表的联接列上没有唯一性约束条件

SQL> alter table dim_a add primary key (id); 表已更改。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id); 表已更改。

4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作：

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 or f.bid = b.id; 实体化视图已创建。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 or f.bid = b.id; where f.aid = a.id(+) *

ERROR 位于第 5 行:

ORA-01719: OR 或 IN 操作数中不允许外部连接运算符 (+)

这个错误提示是十分明显的，不过下面的又只能通过EXPLAIN_MVIEW过程来定位错误原因。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid != b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性

SQL> create materialized view mv_fact_err refresh fast as

2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid != b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

SQL> delete mv_capabilities_table; 已删除15行。 SQL> begin

2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,

3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid != b.id');

7 end; 8 /

PL/SQL 过程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT'; MSGTXT

------------------------------------------------------------ 使用了除等号 (=) 外的运算符联接谓词 实体化视图中的外部联接

在实体化视图中存在一个或多个联接

5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中： SQL> create materialized view mv_fact_err refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 or f.bid = b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-12052: 无法快速刷新实体化视图 YANGTK.MV_FACT_ERR

这个错误的信息在上面已经列出了，错误原因说明比较明确。 SQL> delete mv_capabilities_table; 已删除17行。 SQL> begin

2 dbms_mview.explain_mview('select f.rowid f_rowid, a.rowid a_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 or f.bid = b.id'); 7 end; 8 /

PL/SQL 过程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT'; MSGTXT

--------------------------------------------------- SELECT 列表没有所有从表的 rowid

6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。 SQL> drop materialized view log on dim_a; 实体化视图日志已删除。

SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-23413: 表 \不带实体化视图日志

这个错误说明十分明确，可以直接定位错误原因。

5.2 包含聚集的物化视图

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)。

首先，建立一个只包含聚集操作的满足快速刷新条件的例子，然后建立一个同时包括聚集和连接操作的例子。

最后依次违反上述11个条件，分别测试，查看会出现什么错误。（第一种约束条件是所有物化视图都必须满足的条件，在文章物化视图的快速刷新（一）中已经进行了说明，这里就不再重复描述了）。

首先，创建测试环境：

SQL> create table dim_a (id number primary key, name varchar2(30)); 表已创建。

SQL> create table dim_b (id number primary key, name varchar2(30)); 表已创建。

SQL> create table fact (id number, aid number, bid number, num number); 表已创建。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id); 表已更改。

SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id); 表已更改。

SQL> insert into dim_a select rownum, 'a'||rownum from user_objects; 已创建48行。

SQL> insert into dim_b select rownum, 'b'||rownum from user_objects; 已创建48行。

SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2

2 from user_objects; 已创建48行。 SQL> commit; 提交完成。

建立可以成功快速刷新的例子，这个物化视图只包含聚集：

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact group by aid; 实体化视图已创建。

建立快速刷新的例子，物化视图包含聚集和连接：

SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view log on dim_a with rowid, sequence (id, name) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact, dim_a where aid = dim_a.id group by name; 实体化视图已创建。

接着，依次违反聚集物化视图快速刷新的每一个限制，检查出现的错误信息：

2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制： (1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列； (2)必须指明ROWID和INCLUDING NEW VALUES；

(3)如果对基表的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。 SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view mv_fact refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact group by aid; from fact group by aid *

ERROR 位于第 3 行:

ORA-23413: 表 \不带实体化视图日志

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact_error refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num, 3 count(bid) count_b from fact group by aid; count(bid) count_b from fact group by aid *

ERROR 位于第 3 行:

ORA-12033: 不能使用 \上实体化视图日志中的过滤器列

SQL> create materialized view mv_fact_error refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num

3 from fact where bid = 5 group by aid; from fact where bid = 5 group by aid *

ERROR 位于第 3 行:

ORA-12033: 不能使用 \上实体化视图日志中的过滤器列 SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid, sequence (aid, num); 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact group by aid; from fact group by aid *

ERROR 位于第 3 行:

ORA-32401: \上的实体化视图日志没有新值 SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> drop materialized view log on dim_a; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view log on dim_a with primary key, sequence (name) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact, dim_a where aid = dim_a.id group by name; from fact, dim_a where aid = dim_a.id group by name *

ERROR 位于第 3 行:

ORA-12032: 不能使用 \上实体化视图日志中的 rowid 列 SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> drop materialized view log on dim_a; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view log on dim_a with rowid (id, name) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num 3 from fact, dim_a where aid = dim_a.id group by name; 实体化视图已创建。

SQL> truncate table mv_capabilities_table; 表已截掉。 SQL> begin

2 dbms_mview.explain_mview('select name, count(*) count, count(num) count_num, sum(num) sum_num

3 from fact, dim_a where aid = dim_a.id group by name'); 4 end; 5 /

PL/SQL 过程已成功完成。

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT

------------------------------ - ----------------------------- REFRESH_COMPLETE Y REFRESH_FAST Y

REFRESH_FAST_AFTER_INSERT Y REFRESH_FAST_AFTER_ONETAB_DML Y

REFRESH_FAST_AFTER_ANY_DML N mv 日志没有序列号 REFRESH_FAST_AFTER_ANY_DML N mv 日志没有序列号

REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 已选择7行。

SQL> select * from mv_fact; NAME COUNT COUNT_NUM SUM_NUM

------------------------------ ---------- ---------- ---------- a1 7 7 420a2 9 9 450a3 9 9 468a4 9 9 486a5 8 8 400a6 8 8 416 已选择6行。

SQL> insert into fact values (100, 1, 1, 1000); 已创建 1 行。

SQL> update dim_a set name = 'a1a' where id = 1; 已更新 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact; NAME COUNT COUNT_NUM SUM_NUM

------------------------------ ---------- ---------- ---------- a2 9 9 450a3 9 9 468a4 9 9 486a5 8 8 400a6 8 8 416a1a 8 8 1420 已选择6行。

SQL> update dim_a set name = 'a1' where name = 'a1a'; 已更新 1 行。

SQL> update fact set aid = 7 where aid = 1;

已更新8行。

SQL> update fact set aid = 1 where aid = 2; 已更新9行。

SQL> update dim_a set name = 'a2a' where name = 'a2'; 已更新 1 行。

SQL> update fact set aid = 2 where aid = 7; 已更新8行。

SQL> delete fact where num = 1000; 已删除 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact; NAME COUNT COUNT_NUM SUM_NUM

------------------------------ ---------- ---------- ---------- a2 9 9 450a3 9 9 468a4 9 9 486a5 8 8 400a6 8 8 416a1a 8 8 1420 已选择6行。

SQL> exec dbms_mview.refresh('MV_FACT'); BEGIN dbms_mview.refresh('MV_FACT'); END; *

ERROR 位于第 1 行:

ORA-12057: 实体化视图 \无效, 必须进行完全刷新 ORA-06512: 在\ORA-06512: 在\ORA-06512: 在\ORA-06512: 在line 1

SQL> exec dbms_mview.refresh('MV_FACT', 'C') PL/SQL 过程已成功完成。

对于包含对多表的UPDATE/DELETE/INSERT混合操作的物化视图，只有建立物化视图日志的时候指定SEQUENCE才能保证快速刷新。

3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX； SQL> create materialized view log on fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, sum(num) sum_num, 3 stddev_pop(num) std_num 4 from fact group by aid; from fact group by aid *

ERROR 位于第 4 行:

ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性

SQL> create materialized view mv_fact_err refresh fast as

2 select aid, count(*) count, count(num) count_num, sum(num) sum_num, 3 stddev_pop(num) std_num 4 from fact group by aid; from fact group by aid *

ERROR 位于第 4 行:

ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

SQL> begin

2 dbms_mview.explain_mview('select aid, count(*) count, count(num) count_num, sum(num) sum_num,

3 stddev_pop(num) std_num 4 from fact group by aid'); 5 end; 6 /

PL/SQL 过程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = 'REFRESH_FAST_AFTER_INSERT'; MSGTXT

-------------------------------------------------------表达式中聚集函数嵌套

上面的例子包含了其他聚集函数STDDEV_P0P(expr)，因此无法进行快速刷新。 4.必须指定COUNT(*)；

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select aid, count(num) count_num, sum(num) sum_num 3 from fact group by aid; 实体化视图已创建。 SQL> begin

2 dbms_mview.explain_mview('select aid, count(num) count_num, sum(num) sum_num 3 from fact group by aid'); 4 end; 5 /

PL/SQL 过程已成功完成。

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT

------------------------------ - ---------------------------------- REFRESH_COMPLETE Y REFRESH_FAST Y

REFRESH_FAST_AFTER_INSERT Y

REFRESH_FAST_AFTER_ONETAB_DML N 在选择列表中不存在 COUNT(*)

REFRESH_FAST_AFTER_ANY_DML N 查看禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 已选择6行。

不加COUNT(*)，建立快速刷新的物化视图居然成功了，通过执行EXPLAIN_MVIEW过程，可以发现，由于确实COUNT(*)对于INSERT操作是可以快速刷新的，不过对于UPDATE和DELETE则会造成快速刷新的失败。 SQL> select * from mv_fact_err; AID COUNT_NUM SUM_NUM

---------- ---------- ---------- 1 8 432 2 8 352 3 8 368 4 8 384 5 8 400 6 8 416

已选择6行。

SQL> insert into fact values (100, 1, 2, 1000); 已创建 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact_err; AID COUNT_NUM SUM_NUM

---------- ---------- ---------- 1 9 1432 2 8 352 3 8 368 4 8 384 5 8 400 6 8 416

已选择6行。

SQL> delete fact where id = 100; 已删除 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact_err; AID COUNT_NUM SUM_NUM

---------- ---------- ---------- 1 9 1432 2 8 352 3 8 368 4 8 384 5 8 400 6 8 416

已选择6行。

SQL> exec dbms_mview.refresh('MV_FACT_ERR') BEGIN dbms_mview.refresh('MV_FACT_ERR'); END;

*

ERROR 位于第 1 行:

ORA-12057: 实体化视图 \无效, 必须进行完全刷新 ORA-06512: 在\ORA-06512: 在\ORA-06512: 在\ORA-06512: 在line 1

SQL> exec dbms_mview.refresh('MV_FACT_ERR', 'C') PL/SQL 过程已成功完成。

SQL> select * from mv_fact_err; AID COUNT_NUM SUM_NUM

---------- ---------- ---------- 1 8 432 2 8 352 3 8 368 4 8 384 5 8 400 6 8 416

已选择6行。

对于ON COMMIT的物化视图要注意，这种情况将造成无法快速刷新，且不会报任何错误，除非你手工执行完全刷新，否则物化视图中的数据无法继续保持同步。

5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在； 比如：包含SUM(a)，则必须同时包含COUNT(a)。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select aid, count(*) count, sum(num) sum_num 3 from fact group by aid; 实体化视图已创建。

SQL> select * from mv_fact_err; AID COUNT SUM_NUM

---------- ---------- ---------- 1 9 1432 2 8 352 3 8 368 4 8 384 5 8 400 6 8 416

已选择6行。

SQL> insert into fact values (101, 2, 2, 1000); 已创建 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact_err; AID COUNT SUM_NUM

---------- ---------- ----------

1 9 1432 2 9 1352 3 8 368 4 8 384 5 8 400 6 8 416

已选择6行。

SQL> delete fact where id = 101; 已删除 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact_err; AID COUNT SUM_NUM

---------- ---------- ---------- 1 9 1432 2 9 1352 3 8 368 4 8 384 5 8 400 6 8 416

已选择6行。

SQL> exec dbms_mview.refresh('MV_FACT_ERR'); BEGIN dbms_mview.refresh('MV_FACT_ERR'); END; *

ERROR 位于第 1 行:

ORA-12057: 实体化视图 \无效, 必须进行完全刷新 ORA-06512: 在\ORA-06512: 在\ORA-06512: 在\ORA-06512: 在line 1

SQL> exec dbms_mview.refresh('MV_FACT_ERR', 'C') PL/SQL 过程已成功完成。 SQL> begin

2 dbms_mview.explain_mview('select aid, count(*) count, sum(num) sum_num 3 from fact group by aid'); 4 end; 5 /

PL/SQL 过程已成功完成。

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT

------------------------------ - ----------------------------------- REFRESH_COMPLETE Y

REFRESH_FAST Y

REFRESH_FAST_AFTER_INSERT Y

REFRESH_FAST_AFTER_ONETAB_DML N 使用 SUM(expr) 时, 未提供 COUNT(expr)

REFRESH_FAST_AFTER_ANY_DML N 查看禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 已选择6行。

这个错误的现象和4中的十分相似。

6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；

SQL> drop materialized view mv_fact_err; 实体化视图已删除。

SQL> create materialized view mv_fact refresh fast on commit as

2 select aid, count(*) count, count(num) count_num, sum(num) sum_num, 3 variance(num) var_num 4 from fact group by aid; 实体化视图已创建。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, 3 variance(num) var_num 4 from fact group by aid; from fact group by aid *

ERROR 位于第 4 行:

ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性

SQL> create materialized view mv_fact_err refresh fast as 2 select aid, count(*) count, count(num) count_num, 3 variance(num) var_num 4 from fact group by aid; from fact group by aid *

ERROR 位于第 4 行:

ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

SQL> truncate table mv_capabilities_table; 表已截掉。 SQL> begin

2 dbms_mview.explain_mview('select aid, count(*) count, count(num) count_num, 3 variance(num) var_num 4 from fact group by aid'); 5 end; 6 /

PL/SQL 过程已成功完成。

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT

------------------------------ - ------------------------------ REFRESH_COMPLETE Y REFRESH_FAST N

REFRESH_FAST_AFTER_INSERT N agg(expr) 需要相应的 SUM(expr) 函数

REFRESH_FAST_AFTER_ONETAB_DML N 查看禁用 REFRESH_FAST_AFTER_INSERT 的原因 REFRESH_FAST_AFTER_ANY_DML N 查看禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 已选择6行。

7.SELECT列表中必须包括所有的GROUP BY列；

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select count(*) count, count(num) count_num 3 from fact group by aid; from fact group by aid *

ERROR 位于第 3 行:

ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性

SQL> create materialized view mv_fact_err refresh fast as 2 select count(*) count, count(num) count_num 3 from fact group by aid; from fact group by aid *

ERROR 位于第 3 行:

ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

SQL> truncate table mv_capabilities_table; 表已截掉。 SQL> begin

2 dbms_mview.explain_mview('select count(*) count, count(num) count_num 3 from fact group by aid'); 4 end; 5 /

PL/SQL 过程已成功完成。

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT

------------------------------ - ----------------------------- REFRESH_COMPLETE Y REFRESH_FAST N

REFRESH_FAST_AFTER_INSERT N 实体化视图至少忽略了选择列表中的一个分组关键字 REFRESH_FAST_AFTER_ONETAB_DML N 查看禁用 REFRESH_FAST_AFTER_INSERT 的原因

REFRESH_FAST_AFTER_ANY_DML N 查看禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 已选择6行。

8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；

物化视图包含MIN或MAX聚集函数；

物化视图包含SUM(expr)，但是没有包括COUNT(expr)； 物化视图没有包含COUNT(*)。

SQL> create materialized view mv_fact_err refresh fast on commit as 2 select aid, count(*) count, count(num) count_num, max(num) max_num 3 from fact group by aid; 实体化视图已创建。

SQL> SELECT * FROM MV_FACT_ERR; AID COUNT COUNT_NUM MAX_NUM

---------- ---------- ---------- ---------- 1 8 8 96 2 8 8 86 3 8 8 88 4 8 8 90 5 8 8 92 6 8 8 94 已选择6行。

SQL> INSERT INTO FACT VALUES (101, 2, 1, 100); 已创建 1 行。 SQL> COMMIT; 提交完成。

SQL> SELECT * FROM MV_FACT_ERR; AID COUNT COUNT_NUM MAX_NUM

---------- ---------- ---------- ---------- 1 8 8 96 2 9 9 100 3 8 8 88 4 8 8 90 5 8 8 92 6 8 8 94 已选择6行。

SQL> DELETE FACT WHERE ID = 101; 已删除 1 行。 SQL> COMMIT; 提交完成。

SQL> SELECT * FROM MV_FACT_ERR; AID COUNT COUNT_NUM MAX_NUM

---------- ---------- ---------- ---------- 1 8 8 96

3 8 8 88 4 8 8 90 5 8 8 92 6 8 8 94 2 8 8 86 已选择6行。

SQL> UPDATE FACT SET NUM = 95 WHERE NUM = 96; 已更新 1 行。 SQL> COMMIT; 提交完成。

SQL> SELECT * FROM MV_FACT_ERR; AID COUNT COUNT_NUM MAX_NUM

---------- ---------- ---------- ---------- 3 8 8 88 4 8 8 90 5 8 8 92 6 8 8 94 2 8 8 86 1 8 8 95 已选择6行。

SQL> truncate table mv_capabilities_table; 表已截掉。 SQL> begin

2 dbms_mview.explain_mview('select aid, count(*) count, count(num) count_num, max(num) max_num

3 from fact group by aid'); 4 end; 5 /

PL/SQL 过程已成功完成。

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT

------------------------------ - ------------------------------ REFRESH_COMPLETE Y REFRESH_FAST Y

REFRESH_FAST_AFTER_INSERT Y

REFRESH_FAST_AFTER_ONETAB_DML N 实体化视图使用了 MIN 或 MAX 聚集函数

REFRESH_FAST_AFTER_ANY_DML N 查看禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因 REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 已选择6行。

根据测试的结果，发现Oracle的文档描述有些问题，MAX和MIN聚集函数是支持单表DML操作的快速刷新的。EXPLAIN_MVIEW过程给出的解释也不完全正确。下面看看包含连接的情况。

SQL> create materialized view mv_fact_err2 refresh fast on commit as 2 select name, count(*) count, count(num) count_num, max(num) max_num 3 from fact, dim_a where aid = dim_a.id group by name; 实体化视图已创建。

SQL> select * from mv_fact_err2; NAME COUNT COUNT_NUM MAX_NUM

------------------------------ ---------- ---------- ---------- a1 8 8 95a2 8 8 86a3 8 8 88a4 8 8 90a5 8 8 92a6 8 8 94 已选择6行。

SQL> insert into fact values (101, 1, 1, 100); 已创建 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact_err2; NAME COUNT COUNT_NUM MAX_NUM

------------------------------ ---------- ---------- ---------- a1 9 9 100a2 8 8 86a3 8 8 88a4 8 8 90a5 8 8 92a6 8 8 94 已选择6行。

SQL> delete fact where id = 101; 已删除 1 行。 SQL> commit; 提交完成。

SQL> select * from mv_fact_err2; NAME COUNT COUNT_NUM MAX_NUM

------------------------------ ---------- ---------- ---------- a1 9 9 100a2 8 8 86a3 8 8 88a4 8 8 90a5 8 8 92a6 8 8 94 已选择6行。

由此可见，MAX和MIN函数支持只包含聚集不包含连接的物化视图的快速刷新。这种刷新对任意DML操作有效。如果物化视图包含多表连接，则只支持INSERT操作，其他DML操作将导致物化视图的快速刷新失败。

第二、三点前面的测试已经描述过了。

9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；

SQL> drop materialized view mv_fact_err2; 实体化视图已删除。

SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> drop materialized view mv_fact_err; 实体化视图已删除。

SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view log on fact with rowid, sequence (id, aid, bid, num) 2 including new values; 实体化视图日志已创建。

5.3 包含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。 嵌套物化视图：

嵌套物化视图的每层都必须满足快速刷新的限制条件；

对于同时包含聚集和连接的嵌套物化视图，不支持ON COMMIT的快速刷新。 下面看几个具体的例子描述上面的限制条件：

首先建立一个符合快速刷新条件的UNION ALL物化视图：

SQL> create table dim_a (id number primary key, name varchar2(30)); 表已创建。

SQL> create table dim_b (id number primary key, name varchar2(30)); 表已创建。

SQL> create table fact (id number, aid number, bid number, num number); 表已创建。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id); 表已更改。

SQL> alter table fact add constraint fk_fact_bid foreign key (bid) references dim_b(id); 表已更改。

SQL> insert into dim_a select rownum, 'a'||rownum from user_objects; 已创建51行。

SQL> insert into dim_b select rownum, 'b'||rownum from user_objects; 已创建51行。

SQL> insert into fact select rownum, mod(rownum, 6) + 1, mod(rownum, 5 ) + 1, rownum *2 2 from user_objects; 已创建51行。 SQL> commit; 提交完成。

SQL> create materialized view log on dim_a with rowid; 实体化视图日志已创建。

SQL> create materialized view log on dim_b with rowid; 实体化视图日志已创建。

SQL> create materialized view log on fact with rowid; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid row_id, a.name name, num, 'a' marker 3 from fact f, dim_a a 4 where f.aid = a.id

5 select name, count(*) count, count(num) count_num, sum(num) sum_num, 'b' marker 6 from fact, dim_b where bid = dim_b.id group by name; 实体化视图已创建。

SQL> create materialized view mv_fact_err refresh fast on commit as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num, 'a' marker 3 from fact, dim_a where aid = dim_a.id(+) group by name 4 union all

5 select name, count(*) count, count(num) count_num, sum(num) sum_num, 'b' marker 6 from fact, dim_b where bid = dim_b.id group by name; from fact, dim_a where aid = dim_a.id(+) group by name *

ERROR 位于第 3 行:

ORA-12054: 无法为实体化视图设置 ON COMMIT 刷新属性 SQL> create materialized view mv_fact_err refresh fast as

2 select name, count(*) count, count(num) count_num, sum(num) sum_num, 'a' marker 3 from fact, dim_a where aid = dim_a.id(+) group by name 4 union all

5 select name, count(*) count, count(num) count_num, sum(num) sum_num, 'b' marker 6 from fact, dim_b where bid = dim_b.id group by name; from fact, dim_a where aid = dim_a.id(+) group by name *

ERROR 位于第 3 行:

ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图 SQL> truncate table mv_capabilities_table; 表已截掉。 SQL> begin

2 dbms_mview.explain_mview('select name, count(*) count, count(num) count_num, sum(num) sum_num, ''a'' marker

3 from fact, dim_a where aid = dim_a.id(+) group by name 4 union all

5 select name, count(*) count, count(num) count_num, sum(num) sum_num, ''b'' marker 6 from fact, dim_b where bid = dim_b.id group by name'); 7 end; 8 /

PL/SQL 过程已成功完成。

SQL> select capability_name, possible, msgtxt from mv_capabilities_table 2 where capability_name like 'REFRESH%'; CAPABILITY_NAME P MSGTXT

------------------------------ - -------------------------------- REFRESH_COMPLETE Y REFRESH_FAST N

REFRESH_FAST_AFTER_INSERT N 顶层查询具有 LEFT OUTER, RIGHT OUTER 或 FULL OUTER 联接

REFRESH_FAST_AFTER_INSERT N UNION ALL MV 中的某些查询块不能快速刷新

REFRESH_FAST_AFTER_ONETAB_DML N 查看禁用 REFRESH_FAST_AFTER_INSERT 的原因

REFRESH_FAST_AFTER_ANY_DML N 查看禁用 REFRESH_FAST_AFTER_ONETAB_DML 的原因

REFRESH_FAST_PCT N PCT 不可能在实体化视图中的任何从表上 已选择7行。

可见，这里描述的不允许外连接指的是包含聚集的UNION ALL物化视图。

第五点涉及到物化视图的PCT属性，这里就不在展开讨论了，第六点是刷新UNION ALL物化视图对系统兼容性的要求，这里也不在进行测试了。 嵌套物化视图的例子：

SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> drop materialized view log on dim_a; 实体化视图日志已删除。

SQL> drop materialized view log on dim_b; 实体化视图日志已删除。

SQL> drop materialized view log on fact; 实体化视图日志已删除。

SQL> create materialized view log on dim_a with rowid; 实体化视图日志已创建。

SQL> create materialized view log on dim_b with rowid; 实体化视图日志已创建。

SQL> create materialized view log on fact with rowid; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id 6 and f.bid = b.id; 实体化视图已创建。

SQL> create materialized view log on mv_fact with rowid, sequence (a_name, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_mv_fact refresh fast on commit as

2 select a_name, count(*) count, count(num) count_num, sum(num) sum_num 3 from mv_fact 4 group by a_name; 实体化视图已创建。

SQL> select * from mv_mv_fact;

A_NAME COUNT COUNT_NUM SUM_NUM ------------------------------ ---------- ---------- ---------- a11 7 7 420 a2 9 9 450

a3 9 9 468 a4 9 9 486 a5 8 8 400 a6 8 8 416 已选择6行。

SQL> update dim_a set name = 'a1' where name = 'a11'; 已更新2行。 SQL> commit; 提交完成。

SQL> select * from mv_mv_fact;

A_NAME COUNT COUNT_NUM SUM_NUM ------------------------------ ---------- ---------- ---------- a2 9 9 450 a3 9 9 468 a4 9 9 486 a5 8 8 400 a6 8 8 416 a1 7 7 420 已选择6行。

对于只包含连接或只包含聚集的嵌套物化视图是支持基于提交的快速刷新的。但是对于同时包含连接和聚集的嵌套物化视图则不支持ON COMMIT的快速刷新。 SQL> drop materialized view mv_mv_fact; 实体化视图已删除。

SQL> drop materialized view mv_fact; 实体化视图已删除。

SQL> drop materialized view log on dim_a; 实体化视图日志已删除。

SQL> create materialized view log on dim_a with rowid, sequence (id, name) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, b.rowid b_rowid, f.id, 3 aid, b.name b_name, num 4 from fact f, dim_b b 5 where f.bid = b.id; 实体化视图已创建。

SQL> create materialized view log on mv_fact with rowid, sequence (aid, num) 2 including new values; 实体化视图日志已创建。

SQL> create materialized view mv_mv_fact refresh fast on commit as

2 select a.name, count(*) count, count(num) count_num, sum(num) sum_num 3 from mv_fact, dim_a a 4 where aid = a.id 5 group by a.name;

from mv_fact, dim_a a *

ERROR 位于第 3 行:

ORA-12053: 这不是一个有效的嵌套实体化视图

SQL> create materialized view mv_mv_fact refresh fast as

2 select a.name, count(*) count, count(num) count_num, sum(num) sum_num 3 from mv_fact, dim_a a 4 where aid = a.id 5 group by a.name; 实体化视图已创建。

6 其他说明

在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。

可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。

物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。

物化视图由于是物理真实存在的，故可以创建索引。创建方式和对普通表创建方式相同。

7 例子

如何建立在特定的表空间上，这些在其他的物化视图上面几乎都没有任何介绍的。主要以我做的一个例子来操作 创建操作

-- 创建物化视图时应先创建存储的日志空间

CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv tablespace ZGMV_DATA --日志保存在特定的表空间 WITH ROWID ;

CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata tablespace ZGMV_DATA --日志保存在特定的表空间 WITH ROWID,sequence(LEVYDETAILDATAID);

CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata tablespace ZGMV_DATA --日志保存在特定的表空间 WITH rowid,sequence(LEVYDATAID); -- 创建物化视图

create materialized view MV_LVY_LEVYDETAILDATA

TABLESPACE ZGMV_DATA --保存表空间 BUILD DEFERRED --延迟刷新不立即刷新

refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新 on demand --按照指定方式刷新

start with to_date('24-11-2005 18:00:10', 'dd-mm-yyyy hh24:mi:ss') --第一次刷新时间 next TRUNC(SYSDATE+1)+18/24 --刷新时间间隔 as

SELECT levydetaildataid, detaildatano, taxtermbegin, taxtermend, ......

ROUND(taxdeduct * taxpercent1, 2) - ROUND(taxdeduct * taxpercent2, 2) - ROUND(taxdeduct * taxpercent3, 2) - ROUND(taxdeduct * taxpercent4, 2) -

ROUND(taxdeduct * taxpercent5, 2) taxdeduct, ROUND(taxfinal * taxpercent1, 2) - ROUND(taxfinal * taxpercent2, 2) - ROUND(taxfinal * taxpercent3, 2) -

ROUND(taxfinal * taxpercent4, 2) - ROUND(taxfinal * taxpercent5, 2) taxfinal, a.levydataid, a.budgetitemcode, taxtypecode, ......

from tb_lvy_levydetaildata a, tb_lvy_levydata c, MV_LVY_LEVYTAXBGTDIV b WHERE a.levydataid = c.levydataid

AND a.budgetdistrscalecode = b.budgetdistrscalecode AND a.budgetitemcode = b.budgetitemcode AND c.incomeresidecode = b.rcvfisccode AND C.TAXSTATUSCODE='08' AND C.NEGATIVEFLAG!='9'; -- 创建物化视图的索引

create index IX_mv_lvy_levytaxbgtdiv_taxtypecode on mv_lvy_levytaxbgtdiv(taxtypecode) TABLESPACE ZGMV_DATA; 删除操作

-- 删除物化视图时应先删除存储的日志空间

DROP materialized view log on mv_lvy_levytaxbgtdiv; DROP materialized view log on tb_lvy_levydetaildata; DROP materialized view log on tb_lvy_levydata; -- 删除物化视图

drop materialized view MV_LVY_LEVYDETAILDATA;

本文来源：https://www.bwwdw.com/article/7sew.html