物化视图的快速刷新（二）
来源：互联网 发布：mac使用netkeeper上网 编辑：程序博客网 时间：2024/05/18 19:47
下面描述包含聚集的物化视图的快速刷新的限制，并包含了正确设置快速刷新的例子。
可以参考：
物化视图的快速刷新（一）：http://yangtingkun.itpub.net/post/468/14245
物化视图的快速刷新（三）：http://yangtingkun.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;
实体化视图日志已创建。
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: 表 "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> 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: 实体化视图 "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;
*
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> 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;
实体化视图日志已创建。
第一次自己回复 [回复]居然没有贴全，郁闷。
向您请教一个问题 [回复]CREATE MATERIALIZED VIEW ANALYZE.GOODSACCT_MV
TABLESPACE BBGLG
PARALLEL
PARTITION BY RANGE (Month)
(PARTITION P2005 VALUES LESS THAN ('200501'),
PARTITION P20051 VALUES LESS THAN ('200507'),
PARTITION P20052 VALUES LESS THAN ('200601'),
PARTITION P20061 VALUES LESS THAN ('200607'),
PARTITION P20062 VALUES LESS THAN ('200701'),
PARTITION P20071 VALUES LESS THAN ('200707'),
PARTITION P20072 VALUES LESS THAN ('200801'),
)
BUILD DEFERRED
REFRESH FAST WITH ROWID
ON COMMIT
ENABLE QUERY REWRITE
AS
select
Month
,ScateID
,McateID
,LcateID
,SOrgID
,MOrgID
,Grouping_ID(Month,ScateID,McateID,LCateID,SOrgID,MOrgID) Gid
,sum(TransQty) as TransQty
,sum(TransQty*TransPrice) as TransSum
,sum(TransQty*(TransPrice-CostPrice)) as ProfitSum
,sum(TransQty*(UNIONSALEPRICE-NORMALSALEPRICE)) as PriceDiscount
,sum(TransQty*(NORMALSALEPRICE-TransPrice)) as PmtDiscount
,count(TransQty) as c_TransQty
,count(TransQty*TransPrice) as c_TransSum
,count(TransQty*(TransPrice-CostPrice)) as c_ProfitSum
,count(TransQty*(UNIONSALEPRICE-NORMALSALEPRICE)) as c_PriceDiscount
,count(TransQty*(NORMALSALEPRICE-TransPrice)) as c_PmtDiscount
,count(*) as cnt
from Times T,Category_T C,Organization_T O,GoodsAcct G
where (G.TransDate=T.Day)
and (G.shopid=O.Sorgid)
and (G.cateid=C.SCateid)
group by
Month
,Rollup(ScateID,McateID,LcateID)
,Rollup(SOrgID,MOrgID)
我这个物化视图为什么不能快速刷新，(注：如果没有分区的那段只有select 段可以实现快速刷新)谢谢
- 物化视图的快速刷新（二）
- 物化视图的快速刷新（二）
- 物化视图的快速刷新（一）
- 物化视图的快速刷新（三）
- 物化视图的快速刷新（一）
- 物化视图的快速刷新（三）
- 物化视图的快速刷新
- 物化视图的快速刷新
- 物化视图的快速刷新
- 物化视图的快速刷新
- ORACLE物化视图--物化视图的快速刷新
- ORACLE物化视图--循序渐进MView（二） MView Log的结构与快速刷新
- 物化视图快速刷新的一个问题
- 52、物化视图的快速刷新
- 物化视图快速刷新的限制
- 自己创建包含复杂查询的快速刷新的物化视图（物化视图日志的另类用法）
- ORACLE物化视图--定位导致物化视图无法快速刷新的原因
- ORA-12052,不能建立快速刷新物化视图的解决
- 文件空洞产生的原因分析
- 物化视图的快速刷新（一）
- C++构造函数调用顺序
- 在RHEL6中用BASH脚本搭建DNS域名解析服务器
- 当乐网JAVA工程师笔试经历
- 物化视图的快速刷新（二）
- 在线重定义更改主键字段类型/ORA-23539
- Spring CommonsMultipartResolver 上传文件
- ssh2概要
- u-boot源码之start.S分析
- 物化视图的快速刷新（三）
- 可延迟函数、内核微线程以及工作队列
- 《深入理解linux内核》笔记二--内存寻址
- C++转换成C的为什么在VC++6.0会运行错误
