Oracle物化视图的优缺点

转自https://zhuanlan.zhihu.com/p/352909525

在OLAP的应用场景中经常使用物化视图

业务描述: 外围系统需要访问ERP系统中的数据(两个数据库),可用dblink将两个数据库联接,访问方式有两种:

1. 在目的数据库建立访问源数据库的synonym

优点: 及时性好,只要erp中数据更改后,在目的数据库立即就可以反映出来

缺点: 通过dblink直接访问速度较慢,有时无法使用ebs数据库中索引或是一些奇怪的问题

2. 在目的数据库建立访问源数据库的物化视图

优点: 访问速度快,可以对物化视图加索引,以加快查询速度

缺点: 数据及时性不好,如果服务器配置较高此缺点可以忽略

物化视图语法:

1. 语法: CREATE MATERIALIZED VIEW viewname

[refresh [fast/complete/force]
[on demand/commit]
[start with date]

[next date]
[with {primary key/rowid}]] AS subquery

2. 语法说明:

refresh fast: 增量更新,需要在源数据表中建立日志表

refersh complete: 全表更新,如果数据天中数据量较少可以采用此方式建立物化视图

refersh force: 如果增量更新可能则进行增量更新,不可用则进行全表更新.

注: 如果不指定 refersh 则默认为 refersh force

on demand: 按照指定的时间频率进行更新,较常用的一种方法

on commit: 源数据表中更新数据commit后就刷新物化视图,此方法及时性较好,但对服务器配置、性能要求相对较高,很少采用

start with date: 此物化视图在何时开始第一次刷新

next date: 指定物化视图刷新的频率

with primary key: 如果源数据表中有单个栏位的 primary key,可以用此方式建立物化视图

with rowid: 如果源数据表中没有单个栏位的primary key,则需使用此方式建立物化视图

3. 创建源数据表的日志表:

CREATE MATERIALIZED VIEW LOG ON owner.tablename;

注: 在建立此log时,一定要使用和数据表相同的owner,建立好后可在 all_mview_logs view 中找到创建的相关信息

物化视图

物化视图是包括一个查询结果 的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以基于表查 询，视图和其它的物化视图。通常情况下，在复制环境下，物化视图被称为主表，在数据仓库中称为明细表。
对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。
对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

物化视图日志
如果你想要采用快速刷新的话，必须建立基于主表的物化视图日志。物化视图日志可以记录主表被更新记录的主键、ROWID或对象标识或者二者全部。物化视图日志也可以包含其他列用以支持带子查询的物化视图的快速刷新。
物化视图日志表的名称为MLOG$_后面跟主体对象的名称。物化视图日志和主体对象建立在相同的用户模式下。一个物化视图日志可以支持多个基于主体对象的物化视图。
有下列几种物化视图日志：
主键：物化视图日志在记录主体对象发生变化的记录时，是根据主键定位的。
ROWID：物化视图日志在记录主体对象发生变化的记录时，是根据ROWID定位的。
对象标识：物化视图日志在记录主体对象发生变化的记录时，是根据对象标识定位的。
组 合类型：物化视图日志在记录主体对象发生变化的记录时，是根据上面三种类型的组合来定位的。如果物化视图日志根据主键、ROWID和对象标识定位变化的数 据，则这种物化视图日志同时支持主键、ROWID和对象物化视图，这种物化视图日志对于一个包含三种类型物化视图的环境是很有用的。组合类型物化视图日志 和其他类型物化视图日志工作方式相同，只不过多记录了一、二种类型。
虽然主键物化视图日志和ROWID物化视图日志的差别很小，但是这些差别在实际使用中有着很大的影响。ROWID物化视图日志会在进行重组和截断时十分困难。例如，如果你对主表进行了重组和截断，那么ROWID物化视图必须执行完全刷新，这是因为ROWID发生了变化。

物化视图实践
Oracle提供以下几种不同类型的物化视图，以满足各种复制环境的需要：主键物化视图和ROWID物化视图。
主键物化视图
主 键物化视图是默认的物化视图。在复制环境下，如果主键物化视图是作为物化视图组的一部分建立的，如果指定了FOR UPDATE语句，那么这个物化视图是可更新的，且这个物化视图组必须和主站点中复制组的同名。另外，可更新物化视图必须和主复制组在不同的数据库中。当 修改发生后，修改的数据以行级为单位被传播，每行数据由主键确定。
主键物化视图可以包含一个子查询，因此你可以在建立物化视图时，建立所有数据的一个子集，也就是说，建立物化视图时可以只选取你需要的数据行。如果主站点中的主对象建立了物化视图日志表，那么一些包含特定类型子查询的主键物化视图仍然可以快速（增量）刷新。

物化视图的优缺点

优点：
1，物化视图的最大的优势是可以提高性能：Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
2， 物化视图有很多方面和索引很相似
3，通过预先计算好答案存储起来，可以大大地减少机器的负载
A，更少的物理读–扫描更少的数据
B，更少的写–不用经常排序和聚集
C。减少CPU的消耗–不用对数据进行聚集计算和函数调用
D，显著地加快响应时间–在使用物化视图查询数据时(与主表相反)，将会很快的返回查询结果
缺点：
1，物化视图用于只读或者“精读”环境下工作最好 ，不用于联机事务处理系统(OLTP)环境，在事实表等更新时会导致物化视图行锁，从而影响系统并发性。
2，物化视图有出现无法快速刷新，导致查询数据不准确的现象
3，Rowid物化视图（创建的物化视图通常情况下有主键，rowid,和子查询视图）只有一个单一的主表，不能包括下面任何一项:
A，Distinct 或者聚合函数.
B，Group by，子查询，连接和SET操作
4，物化视图会增加对磁盘资源的需求，即需要永久分配的硬盘空间给物化视图来存储数据
5，物化视图的工作原理受一些可能的约束，比如主键，外键等。