合 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*

FROM all_mview_logs aml

WHERE aml.MASTER ='TAX';

查询主表为TAX的物化视图日志。结果中，一个字段叫做LOG_TABLE，对于表TAX来说，物化视图日志表名是MLOG$_TAX。现在就可以去查找表MLOG$_TAX来查看物化视图日志了。

要创建物化视图日志必须有CREATE TRIGGER，CREATE TABLE权限.

物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

创建物化视图日志的语法如下：

CREATE MATERIALIZED VIEW LOG ON [TABLE_NAME]

TABLESPACE[TABLESPACE_NAME]

WITH [OBJECTID|PRIMARY KEY|ROWID|SEQUENCE]

TABLE_NAME，TABLESPACE_NAME是指基表名称和物化视图日志存放的表空间。

WITH子句中，OBJECT ID指对象ID，如果是对象物化视图，则只能采用这个方式；

PRIMARY KEY是根据基表主键创建日志，ROWID是根据ROWID创建日志，SEQUENCE则是根据序列号创建日志。

物化视图日志如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

可以指明ON PREBUILD TABLE语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。

删除物化视图日志物化视图日志经常会由于物化视图长时间没有刷新，或者基表的一次批量数据更改而变得很大，这会影响物化视图的刷新性能，因此对于这种情况需要对物化视图日志进行处理，降低物化视图日志表的高水位线。

物化视图日志会记录下基表所有的增、删、改操作，而物化视图执行完快速刷新操作后，会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果其中一个物化视图一直不刷新，那么物化视图日志就会变得越来越大。

还有一种情况，比如表中插入了大量的数据，或者删除了大量的数据，或者将表中的某一列统一更新为一个值，这种操作都会在物化视图日志中产生大量的记录。

而物化视图日志的增大必然影响物化视图的刷新速度。一方面，物化视图在刷新的时候要扫描物化视图日志，另一方面，物化视图在刷新介绍后，也要清除物化视图日志中的记录，仍然要扫描物化视图日志，因此物化视图日志的大小直接会影响物化视图快速刷新的速度。更重要的是，物化视图日志的高水位一旦增长到一个很高的位置，即使以后物化视图日志中记录很少，甚至没有记录存在，物化视图在刷新的时候仍然需要较长的时间。

删除物化视图日志

删除物化视图日志的语句是

DROP MATERIAZLIED VIEW LOG ON[TABLE_NAME]；

1、物化视图的类型：ON DEMAND、ON COMMIT

二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

2、ON DEMAND物化视图

物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。

物化视图的特点：

(1)物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证；

(2)物化视图也是一种段(segment)，所以其有自己的物理存储属性；

(3)物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；

创建语句：create materialized view mv_name as select*from table_name;

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

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