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

柔情只为你懂 2022-05-15 04:21 627阅读 0赞

# [物化视图的快速刷新][Link 1] #

**物化视图的快速刷新（一） **

物化视图有三种刷新方式：COMPLETE、FAST和FORCE。

完全刷新（COMPLETE）会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。快速刷新（FAST）采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。采用FORCE方式，Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

显然快速刷新是物化视图刷新方式的首选。但是，不是所有的物化视图都可以进行快速刷新，只有满足某些条件的物化视图才具有快速刷新的能力。

根据查询的不同，快速刷新的限制条件也不相同，下面总结一下不同类型的物化视图对快速刷新的限制条件。

可以参考：

物化视图的快速刷新（二）：[http://blog.itpub.net/post/468/16456][http_blog.itpub.net_post_468_16456]

物化视图的快速刷新（三）：[http://blog.itpub.net/post/468/16496][http_blog.itpub.net_post_468_16496]

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

1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；

2.物化视图不能包含对LONG和LONG RAW数据类型的引用。

**只包含连接的物化视图：**

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;

实体化视图日志已创建。

实体化视图已创建。

下面我们依次违反上面的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][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> 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: "YANGTK"."DIM\_B" 上的实体化视图日志没有新值

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

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

SQL> drop materialized view mv\_fact;

实体化视图已删除。

实体化视图已创建。

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\_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: 表 "YANGTK"."DIM\_A" 不带实体化视图日志

这个错误说明十分明确，可以直接定位错误原因。  
  
**物化视图的快速刷新（二）**

下面描述包含聚集的物化视图的快速刷新的限制，并包含了正确设置快速刷新的例子。

可以参考：

物化视图的快速刷新（一）：[http://blog.itpub.net/post/468/14245][http_blog.itpub.net_post_468_14245]

物化视图的快速刷新（三）：[http://blog.itpub.net/post/468/16496][http_blog.itpub.net_post_468_16496]

**包含聚集的物化视图：**

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;

实体化视图日志已创建。

实体化视图已创建。

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

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: 表 "YANGTK"."FACT" 不带实体化视图日志

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: 不能使用 "YANGTK"."FACT" 上实体化视图日志中的过滤器列

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: 不能使用 "YANGTK"."FACT" 上实体化视图日志中的过滤器列

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: "YANGTK"."FACT" 上的实体化视图日志没有新值

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: 不能使用 "YANGTK"."DIM\_A" 上实体化视图日志中的 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> 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: 实体化视图 "YANGTK"."MV\_FACT" 无效, 必须进行完全刷新   
ORA-06512: 在 "SYS.DBMS\_SNAPSHOT", line 794  
ORA-06512: 在 "SYS.DBMS\_SNAPSHOT", line 851  
ORA-06512: 在 "SYS.DBMS\_SNAPSHOT", line 832  
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: 实体化视图 "YANGTK"."MV\_FACT\_ERR" 无效, 必须进行完全刷新   
ORA-06512: 在 "SYS.DBMS\_SNAPSHOT", line 794  
ORA-06512: 在 "SYS.DBMS\_SNAPSHOT", line 851  
ORA-06512: 在 "SYS.DBMS\_SNAPSHOT", line 832  
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;

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;

实体化视图日志已创建。  
  
**物化视图的快速刷新（三）**

本文描述包含UNION ALL的物化视图和嵌套物化视图的快速刷新的限制，并包含了正确设置快速刷新的例子。

可以参考：

物化视图的快速刷新（一）：[http://blog.itpub.net/post/468/14245][http_blog.itpub.net_post_468_14245]

物化视图的快速刷新（二）：[http://blog.itpub.net/post/468/16456][http_blog.itpub.net_post_468_16456]

**包含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  union all  
  6  select f.rowid f\_rowid, b.rowid row\_id, b.name name, num, 'b' marker  
  7  from fact f, dim\_b b   
  8  where f.bid = b.id;

实体化视图已创建。

如果不满足UNION ALL物化视图的第一个条件，则会出现下面的情况：

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, sequence (id, name)   
  2  including new values;

实体化视图日志已创建。

SQL> create materialized view log on dim\_b with rowid, sequence (id, name)   
  2  including new values;

实体化视图日志已创建。

SQL> create materialized view log on fact with rowid, sequence (aid, bid, num)   
  2  including new values;

实体化视图日志已创建。

SQL> create materialized view mv\_fact\_err refresh fast as   
  2  select name, count(\*) from   
  3  (  
  4  select f.rowid f\_rowid, a.rowid row\_id, a.name name, num, 'a' marker  
  5  from fact f, dim\_a a  
  6  where f.aid = a.id  
  7  union all  
  8  select f.rowid f\_rowid, b.rowid row\_id, b.name name, num, 'b' marker  
  9  from fact f, dim\_b b   
 10  where f.bid = b.id  
 11  )  
 12  group by name;  
from fact f, dim\_a a  
     \*  
ERROR 位于第 5 行:  
ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

SQL> truncate table mv\_capabilities\_table;

表已截掉。

SQL> begin  
  2  dbms\_mview.explain\_mview('select name, count(\*) from   
  3  (  
  4  select f.rowid f\_rowid, a.rowid row\_id, a.name name, num, ''a'' marker  
  5  from fact f, dim\_a a  
  6  where f.aid = a.id  
  7  union all  
  8  select f.rowid f\_rowid, b.rowid row\_id, b.name name, num, ''b'' marker   
  9  from fact f, dim\_b b   
 10  where f.bid = b.id  
 11  )  
 12  group by name');  
 13  end;  
 14  /

PL/SQL 过程已成功完成。

SQL> col msgtxt format a60  
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 视图中带 UNION ALL 的 MV 太复杂  
REFRESH\_FAST\_AFTER\_INSERT      N 视图合并后, 子查询或已命名的视图仍在 FROM 列表中  
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行。

第二条限制是很正常的，不在描述，如果没有满足第三个条件：

SQL> create materialized view mv\_fact\_err refresh fast as   
  2  select f.rowid f\_rowid, a.rowid row\_id, a.name name, num   
  3  from fact f, dim\_a a  
  4  where f.aid = a.id  
  5  union all  
  6  select f.rowid f\_rowid, b.rowid row\_id, b.name name, num   
  7  from fact f, dim\_b b   
  8  where f.bid = b.id;  
from fact f, dim\_a a  
     \*  
ERROR 位于第 3 行:  
ORA-12015: 不能从复杂查询中创建一个可快速刷新的实体化视图

SQL> truncate table mv\_capabilities\_table;

表已截掉。

SQL> begin  
  2  dbms\_mview.explain\_mview('select f.rowid f\_rowid, a.rowid row\_id, a.name name, num   
  3  from fact f, dim\_a a  
  4  where f.aid = a.id  
  5  union all  
  6  select f.rowid f\_rowid, b.rowid row\_id, b.name name, num   
  7  from fact f, dim\_b b   
  8  where f.bid = b.id');  
  9  end;  
 10  /

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 实体化视图没有 UNION ALL 标记列  
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行。

如果查询包含了外连接等，则会出现下面的情况：

SQL> create materialized view mv\_fact\_err 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  union all  
  6  select f.rowid f\_rowid, b.rowid row\_id, b.name name, num, 'b' marker   
  7  from fact f, dim\_b b   
  8  where f.bid = b.id;

实体化视图已创建。

SQL> truncate table mv\_capabilities\_table;

表已截掉。

SQL> begin  
  2  dbms\_mview.explain\_mview('MV\_FACT\_ERR');  
  3  end;  
  4  /

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     Y  
REFRESH\_FAST\_PCT               N PCT 不可能在实体化视图中的任何从表上

已选择6行。

物化视图可以成功建立，而且EXPLAIN\_MVIEW过程也没有显示任何信息。下面我们检查一下，这个物化视图是否真正对所有操作都可以快速刷新：

SQL> select \* from mv\_fact\_err where name = 'a1';

F\_ROWID            ROW\_ID             NAME                  NUM M  
\------------------ ------------------ -------------- ---------- -  
AAACDbAAFAAAACIAAF AAACDXAAFAAAAAYAAA a1                     12 a  
AAACDbAAFAAAACIAAL AAACDXAAFAAAAAYAAA a1                     24 a  
AAACDbAAFAAAACIAAR AAACDXAAFAAAAAYAAA a1                     36 a  
AAACDbAAFAAAACIAAX AAACDXAAFAAAAAYAAA a1                     48 a  
AAACDbAAFAAAACIAAd AAACDXAAFAAAAAYAAA a1                     60 a  
AAACDbAAFAAAACIAAj AAACDXAAFAAAAAYAAA a1                     72 a  
AAACDbAAFAAAACIAAp AAACDXAAFAAAAAYAAA a1                     84 a  
AAACDbAAFAAAACIAAv AAACDXAAFAAAAAYAAA a1                     96 a

已选择8行。

SQL> delete fact where rowid = 'AAACDbAAFAAAACIAAF';

已删除 1 行。

SQL> commit;

提交完成。

SQL> select \* from mv\_fact\_err where name = 'a1';

F\_ROWID            ROW\_ID             NAME                  NUM M  
\------------------ ------------------ -------------- ---------- -  
AAACDbAAFAAAACIAAL AAACDXAAFAAAAAYAAA a1                     24 a  
AAACDbAAFAAAACIAAR AAACDXAAFAAAAAYAAA a1                     36 a  
AAACDbAAFAAAACIAAX AAACDXAAFAAAAAYAAA a1                     48 a  
AAACDbAAFAAAACIAAd AAACDXAAFAAAAAYAAA a1                     60 a  
AAACDbAAFAAAACIAAj AAACDXAAFAAAAAYAAA a1                     72 a  
AAACDbAAFAAAACIAAp AAACDXAAFAAAAAYAAA a1                     84 a  
AAACDbAAFAAAACIAAv AAACDXAAFAAAAAYAAA a1                     96 a

已选择7行。

SQL> select \* from mv\_fact where name = 'a1';

已选择7行。

SQL> update dim\_a set name = 'a11' where name = 'a1';

已更新 1 行。

SQL> commit;

提交完成。

SQL> select \* from mv\_fact\_err where name = 'a1';

未选定行

SQL> select \* from mv\_fact\_err where name = 'a11';

F\_ROWID            ROW\_ID             NAME                  NUM M  
\------------------ ------------------ -------------- ---------- -  
AAACDbAAFAAAACIAAL AAACDXAAFAAAAAYAAA a11                    24 a  
AAACDbAAFAAAACIAAR AAACDXAAFAAAAAYAAA a11                    36 a  
AAACDbAAFAAAACIAAX AAACDXAAFAAAAAYAAA a11                    48 a  
AAACDbAAFAAAACIAAd AAACDXAAFAAAAAYAAA a11                    60 a  
AAACDbAAFAAAACIAAj AAACDXAAFAAAAAYAAA a11                    72 a  
AAACDbAAFAAAACIAAp AAACDXAAFAAAAAYAAA a11                    84 a  
AAACDbAAFAAAACIAAv AAACDXAAFAAAAAYAAA a11                    96 a

已选择7行。

SQL> select \* from mv\_fact where name = 'a11';

已选择7行。

根据测试，发现文档描述的不严谨，针对只包含连接操作构成的UNION ALL物化视图是可以支持外连接的。

SQL> create materialized view log on dim\_a with rowid, sequence (id, name)   
  2  including new values;

实体化视图日志已创建。

SQL> create materialized view log on dim\_b with rowid, sequence (id, name)   
  2  including new values;

实体化视图日志已创建。

SQL> create materialized view log on fact with rowid, sequence (aid, bid, num)   
  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, '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;

实体化视图已创建。

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 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;

实体化视图已创建。

[Link 1]: http://www.blogjava.net/allan-oy/articles/57769.html
[http_blog.itpub.net_post_468_16456]: http://blog.itpub.net/post/468/16456
[http_blog.itpub.net_post_468_16496]: http://blog.itpub.net/post/468/16496
[http_blog.itpub.net_post_468_13318]: http://blog.itpub.net/post/468/13318
[http_blog.itpub.net_post_468_14245]: http://blog.itpub.net/post/468/14245