物化视图的快速刷新（三）
来源：互联网 发布：西电工程优化课后答案 编辑：程序博客网 时间：2024/05/04 22:39
发表于: 2005.01.29 03:42
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/16496
---------------------------------------------------------------
本文描述包含UNION ALL的物化视图和嵌套物化视图的快速刷新的限制，并包含了正确设置快速刷新的例子。
可以参考：
物化视图的快速刷新（一）：http://yangtingkun.itpub.net/post/468/14245
物化视图的快速刷新（二）：http://yangtingkun.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';
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> 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';
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行。
根据测试，发现文档描述的不严谨，针对只包含连接操作构成的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 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;
实体化视图已创建。
- 物化视图的快速刷新（三）
- 物化视图的快速刷新（三）
- 物化视图的快速刷新（一）
- 物化视图的快速刷新（二）
- 物化视图的快速刷新（一）
- 物化视图的快速刷新（二）
- 物化视图的快速刷新
- 物化视图的快速刷新
- 物化视图的快速刷新
- 物化视图的快速刷新
- ORACLE物化视图--物化视图的快速刷新
- 物化视图快速刷新的一个问题
- 52、物化视图的快速刷新
- 物化视图快速刷新的限制
- 自己创建包含复杂查询的快速刷新的物化视图（物化视图日志的另类用法）
- ORACLE物化视图--定位导致物化视图无法快速刷新的原因
- ORA-12052,不能建立快速刷新物化视图的解决
- 定位导致物化视图无法快速刷新的原因
- window.open()的所有参数列表
- 物化视图的快速刷新（一）
- 移动互联网的成功商业盈利等模式解析
- 物化视图的快速刷新（二）
- 中文编码的转换
- 物化视图的快速刷新（三）
- Chinese Style Converter & Services
- JAVA学习提高之－－－－ List toArray学到的
- HTML+TIME2，纯HTML实现页面动画效果。
- weblogic、Tuxedo和Aqualogic系列产品免费License Keys
- Windows CE 6.0 启动过程分析(转摘)
- 2009年海外Web设计风潮（上）
- 深入 Microsoft.VisualBasic.Strings.StrConv 簡繁轉換
- K&R The C Programming Language(SecondEdition)中getchar()与EOF
