物化视图创建语句
create materialized view [view_name]
refresh [fast|complete|force]
[
on [commit|demand] |
start with (start_time) next (next_time)
with rowid //不加这一行,如果采用了fast更新的话,默认是使用源表主键对应的物化视图日志,所以必须有主键且物化视图日志必须建立在主键上
for update //加上了这一行才可以对物化视图进行update和delete,否则报错ORA-01732
]
as
{创建物化视图用的查询语句} //语句对象可以来自表、同义词、视图,同义词和视图虽然只是定义没有数据,但是select同义词和视图的时候出来的就是数据
案例1
create materialized view test_materializedview
refresh complete on demand
start with sysdate next sysdate+1/144
with rowid
for update //加上了这一行才可以对物化视图进行update和delete,否则报错ORA-01732
as
select * from test
案例2
create materialized view log on po.po_asl_attributes with rowid;
create materialized view cux.cux_suppliers_mv refresh fast with rowid on demand
as select * from po.po_asl_attributes;
手工再增加刷新频率
alter materialized view cux.cux_suppliers_mv refresh fast on demand Start with sysdate+30/(24*60*60) next sysdate+30/(24*60*60);
alter materialized view cux_system_items_mv refresh fast on demand Start with sysdate next TRUNC(sysdate)+1+4/(24);--每天凌晨4点运行
物化视图创建后,会自动创建一张和物化视图同名的表,会在dba_objects中出现两个对象,OBJECT_TYPE分别是TABLE、MATERIALIZED VIEW。user_mviews、user_tables中都会出现这个对象
USER_MVIEWS.STALENESS是NEEDS_COMPILE时,user_objects.object_type为MATERIALIZED VIEW的user_objects.status是INVALID,而user_objects.object_type为TABLE的user_objects.status是VALID,一般影响不大,物化视图还是可以继续查询出数据了,当然也可以重新编译一下ALTER MATERIALIZED VIEW MV_NAME COMPILE;
物化视图占用空间,空间大小和base table一样大,但是在 user_segments.segment_type 显示的值是 table 而不是 MATERIALIZED VIEW
MV也可以直接update、delete,但是不影响base table,当然MV执行update、delete后又会根据commit或demand next time自动恢复和base table表一致
refresh [fast|complete|force] MV刷新的方式:
fast:增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化,建立增量刷新物化视图源表必须建立物化视图日志。create materialized view log on tablename with rowid或create materialized view log on tablename WITH PRIMARY KEY;
多张表时此语句也生效,创建后,原来的表中会多出两类视图表:MLOG$_table_name和RUPD$_table_name
complete:全部刷新。相当于重新执行一次创建视图的查询语句。基于视图建立物化视图,则只能refresh complete。
force:这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。
MV数据刷新的时间:
on demand:在用户需要刷新的时候刷新,如果有start with则系统会自动建立job定时刷新
on commit:当主表中有数据提交的时候,立即刷新MV中的数据;
start with:从指定的时间开始,每隔一段时间(由next指定)就刷新一次;会自动建立job定时刷新
物化视图注意事项:
1. 创建物化视图必须在自己的schema用户下创建,如果有start with选项则创建后会在自己schema下创建一个自动刷新物化视图的job,不在自己用户创建物化视图,创建好后无法创建自动刷新视图的job。
比如在system用户下给A用户创建了一个物化视图,但是A用户下并不会出现对应的自动刷新物化视图的job
2. 如果是fast刷新,原表不能是视图,且源表必须有materialized view log,materialized view log可以建立在主键上或rowid上
创建materialized view log的标准语句如下(为什么一定要加INCLUDING NEW VALUES没有搞清楚,实验过不加的情况下主表update、delete后物化视图也是会刷新的)
CREATE MATERIALIZED VIEW LOG ON tablename WITH ROWID;
或
CREATE MATERIALIZED VIEW LOG ON tablename WITH PRIMARY KEY INCLUDING NEW VALUES;
删除materialized view log的语句如下
drop materialized view log ON tablename
A用户fast刷新方式创建物化视图时基表来自不同用户B,则不仅要授予A select B基表权限,也要授予A select B基表MATERIALIZED VIEW LOG权限
3. 如果是complete刷新,则源表没有任何要求,complete刷新时原表可以是视图
没有建立job自动刷新物化视图,则物化视图也可以手工(如下CUX_PER_PEOPLE_F_M是物化视图的名称)
begin
DBMS_MVIEW.REFRESH ('CUX_PER_PEOPLE_F_MV');
end;
以下start with后面的时间随便定义,不管定义成sysdate之前还是之后,时间都是执行create materialized语句的这个时刻
create materialized view test13
refresh force on demand
start with sysdate-1 next sysdate+1/360
as
select * from luxus.test1@xuniji
执行以上语句后马上再select last_refresh_date from user_mviews发现时间就是刚才执行create materialized语句的这个时刻
遇到复杂的系统视图,我们可以使用物化视图代替该系统视图的方法来提升性能,例如下面的案例
系统视图dba_network_acl_privileges很复杂,直接select * from dba_network_acl_privileges显示的执行计划就非常复杂调用了大量的系统对象。
select principal from dba_network_acl_privileges where acl='/sys/acls/mail_acl2.xml' and principal not in (select principal from dba_network_acl_privileges where acl='/sys/acls/mail_acl2.xml' and privilege='resolve' and principal is not null)执行很慢、执行计划很复杂、且没有返回任何结果。
使用同义词dba_acl代替dba_network_acl_privileges来执行上述语句依旧如此
CREATE SYNONYM dba_acl FOR dba_network_acl_privileges
使用物化视图dba_acl2代替dba_network_acl_privileges来执行上述语句后性能大大提升,执行很快、执行计划简单、且有返回结果,如果加上start with next条件还能定时更新
create materialized view dba_acl2
refresh complete on demand
as select * from dba_network_acl_privileges
当然使用新建一张表代替dba_network_acl_privileges来执行上述语句后性能也能大大提升,执行很快、执行计划简单、且有返回结果,但缺点是表无法实时或定时更新
create table dba_acl3
as select * from dba_network_acl_privileges
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-1983606/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30126024/viewspace-1983606/