Oracle物化视图

最新推荐文章于 2020-01-16 10:23:22 发布

magicv587

最新推荐文章于 2020-01-16 10:23:22 发布

阅读量179

点赞数

在项目中遇到一个需要汇总查询数据的问题，普通视图的连接在表增多和数据量比较大的情况下会导致速度十分缓慢，于是采用物化视图这一方案。

简介

Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。

物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

物化视图分类

物化视图，根据不同的着重点可以有不同的分类:

1) 按刷新方式分：FAST/COMPLETE/FORCE

2) 按刷新时间的不同：ON DEMAND/ON COMMIT

3) 按是否可更新：UPDATABLE/READ ONLY

4) 按是否支持查询重写：ENABLE QUERY REWRITE/DISABLEQUERY REWRITE

默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。

注意：设置REFRESH ON COMMIT的物化视图不能访问远端对象。

在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。

刷新模式：

物化视图有二种刷新模式：
在创建时refresh mode是 on demand 还是 on commit。
on demand 顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性;
on commit 提交触发，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。
创建物化视图时未作指定，则Oracle按 on demand 模式来创建。

上面说的是刷新的模式，针对于如何刷新，则有三种刷新方法：

完全刷新（COMPLETE）：会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
快速刷新（FAST）：采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。
FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

关于快速刷新：Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

主键问题

WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句). PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图.

基于ROWID物化视图只有一个单一的主表，不能包括下面任何一项:
（1）.Distinct 或者聚合函数.

（2）.Group by，子查询，连接和SET操作

物化视图操作示例

1. 创建物化视图需要的权限：

GRANT CREATE MATERIALIZED VIEW TO USER_NAME;

2. 在源表建立物化视图日志

CREATE MATERIALIZED VIEW LOG ON DAVE

3. 授权给中间用户

GRANT SELECT ON DAVE TO ANQING;

GRANT SELECT ON MLOG$_DAVE TO ANQING;

4. 在目标数据库上创建MATERIALIZED VIEW

create materialized view log on t_106 with ROWID; //通过ROWID创建日志
create materialized view mview_1
build immediate
refresh fast on commit //提交时快速刷新
as
select rowid as ENTRY_ID,106 as TABLE_ID,BT,MJ,BGQX,ZRZ,BZ,ND,BM,,SYS_CONTROL_STATE,SYS_DESTROY_STATE,SYS_RECORD_STATE from t_106
union all

select id rowid as ENTRY_ID,107 as TABLE_ID,BT,MJ,BGQX,ZRZ,BZ,ND,BM,,SYS_CONTROL_STATE,SYS_DESTROY_STATE,SYS_RECORD_STATE from t_107