物化视图的快速刷新(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: 表 “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;

实体化视图日志已创建。

Tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *