物化视图的快速刷新(1)

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

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

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

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

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

 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;


实体化视图日志已创建。


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;


实体化视图已创建。


下面我们依次违反上面的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


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> 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_raw
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id
6 and f.bid = b.id;


实体化视图已创建。
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> 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> 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 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;


实体化视图已创建。


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” 不带实体化视图日志


这个错误说明十分明确,可以直接定位错误原因。

Tagged , . Bookmark the permalink.

Leave a Reply

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