物化视图

最新推荐文章于 2024-05-23 06:59:48 发布

cjh81378066

最新推荐文章于 2024-05-23 06:59:48 发布

阅读量269

点赞数

物化视图创建语句

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

]

{创建物化视图用的查询语句} //语句对象可以来自表、同义词、视图，同义词和视图虽然只是定义没有数据，但是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

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)执行很慢、执行计划很复杂、且没有返回任何结果。