Oracle物化视图介绍(7)

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。

…… 此处隐藏：5115字，全部文档内容请下载后查看。喜欢就下载吧 ……