合 Oracle优化中的物化视图

简介

物化视图（Materialized Views）是包括一个查询结果的数据库对象，用于减少那些汇总、集合和分组的信息的集合数量。它们通常适合于数据仓库和DSS系统（Decision Support System，决策支持系统），它是远程数据的的本地副本。物化视图存储基于远程表的数据，在以前的Oracle版本中也可以称为快照。物化视图可以查询表，视图和其它的物化视图。

物化视图有如下的一些特点：

①　视图并不真正的包含数据，而物化视图则真正的包含数据。

②　物化视图等于是对其基表的一种预处理。

③　物化视图中的数据可以随基表的变化而变化。

④　物化视图可以加快某些查询操作的速度，但它减慢了DML的速度。

数据字典

与物化视图有关的2个数据字典视图分别为DBA_MVIEWS和DBA_MVIEW_REFRESH_TIMES，可以查询物化视图上一次的刷新时间。

语法

物化视图

以上是创建物化视图的比较完整命令，其中的[]部分均是可选的，蓝色的为默认选项。下面分别对它们进行说明。

1.MV_NAME：是物化视图的名称一般是MV_XXX形式的；
2.ON PREBUILT TABLE：将已经存在的表注册为物化视图。同时还需要提供描述创建该表的查询的SELECT子句。可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。
3.TABLESPACE：指定物化视图存放的表空间。
4.BUILD子句：指定何时生成数据。
①　BUILD IMMEDIATE：指定在创建物化视图时就生成数据；
②　BUILD DEFERRED：指定在创建时并不生成数据，以后根据需要生成数据。默认为IMMEDIATE选项。
③　No prebuilt table：使用事先已存在的，已含有视图定义中有现有数据的表，而不是建立一个新结构来保存数据。
5.REFRESH子句：指定当基表的数据发生变化时，物化视图何时以何种方式和基表进行同步。
刷新方式
①　FAST指定增量刷新方式，也就是只刷新自上次刷新后被修改的数据，假设前一次刷新的时间为t1，那么使用fast模式刷新物化视图时，只向视图中添加t1到当前时间段内，主表变化过的数据.为了记录这种变化，建立增量刷新物化视图还必须建立一个物化视图日志表。create materialized view log on (主表名)。
②　COMPLETE 指定对整个物化视图进行完全刷新
③　FORCE是默认选项，指定在刷新数据时，先判断是否能够使用FAST方式进行刷新，如果可行，则采用FAST方式刷新，如果不可行，则使用COMPLETE方式刷新。
刷新时间
①　ON DEMAND指定物化视图在用户需要的时候进行刷新，可以手工刷新，通过使用DBMS_MVIEW.REFRESH等方式刷新，也可以通过JOB方式进行定时刷新，可以到表USER_JOBS中查看JOB的信息；
②　ON COMMIT指定物化视图在基表的DML操作提交的同时进行刷新。START WITH指定第一次刷新的时间；NEXT指定刷新的时间间隔。
不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用）
6.WITH PRIMARY KEY：指定生成主键物化视图，也就是物化视图是基于表的主键，而不是ROWID(对应于ROWID选项)。如果要使用WITH PRIMARY KEY选项，基表上应该定义了主键，否则只能使用ROWID选项。WITH PRIMARY是默认选项。RowID物化视图只有一个单一的主表，不能包括下面任何一项：Distinct或者聚合函数；Group by，子句，连接和Set操作。
☞： 基于主键的物化视图和ROWID的物化视图的说明
创建物化视图日志时，指定了记录更新的原则即with后面的primary或者rowid或者object id等等，后面，默认是以primary key为记录更新，在物化视图内也是以此为更新的原则。
例如：

1、如果日志内使用的是primary key 则在创建物化视图时指定rowid来更新，则会报ORA-12032: 不能使用 "TEST" 上实体化视图日志中的 rowid 列
2、如果日志内使用的是rowid 则在创建物化视图时指定primary或者默认指定，则会报ORA-23415: "GIS"."LZWMV" 的实体化视图日志不记录主键
7.ENABLE/DISABLE QUERY REWIRTE：指定是否支持查询重写，默认不支持。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。
8.SELECT子句：从基表取数据的查询语句，和普通的查询语句没有区别。
9.注意：如果采用fast方式创建，那么就要在select中加上所有关联表的rowid列，否则报错：

例如：

这里需要特别注意的是，如果需要进行快速刷新，则需要创建物化视图日志。

物化视图分区而且基于分区的物化视图可以支持分区变化跟踪(PCT)。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。

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

跨版本数据迁移利用prebuilt mv实现跨平台，跨版本数据迁移。该方法的实现原理是对于要迁移的表对象，需要有一个主键，用于mv的刷新，对于符合该要求的表，在源表上创建mv日志，再在目标数据库上创建结构一样的表，然后在目标表上采用prebuilt方式创建mv，第一次采用完全刷新，之后采用增量刷新，等真正要切换的时候，只需要刷新完增量的日志，删除mv，保留目标表即可。

查询重写(Query Rewrite)：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。

参数配置：

STALE_TOLERATED表示即使细目表中的数据已经发生了变化，也仍然使用物化视图。

TRUSTED表示物化视图未失效时才使用该视图。但是，查询改写可以使用信任关系，如那些由维度对象或尚未生效的约束所声明的关系。

ENFORCED（缺省）表示当物化视图保证能给出与使用细目表相同的结果时才使用它。使用这一参数意味着查询改写将不使用失效的物化视图或信任关系。

删除物化视图

虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除（在表上右键选择‘drop’并不能删除物化视图），可以使用语句来实现：drop materialized view mv_name

物化视图日志

物化视图日志是一个表，记录了对物化视图操作的历史记录。

物化视图日志的表名被保存在ALL_MVIEW_LOGS这张表中，可以通过主表名来查看它的物化视图日志。如以下语句：

SELECT*

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。