Oracle MATERIALIZED VIEW -- 创建物化视图

一、ORACLE：materialized view和普通VIEW有什么区别

materialized view(MV)是自动刷新或者手动刷新的，View不用刷新 MV也可以直接update，但是不影响base table，对View的update反映到base table上 MV主要用于远程数据访问，mv中的数据需要占用磁盘空间，view中不保存数据

 

二、物化视图概述物化视图概述

  Oracle的物化视图提供了强大的功能，可以用在不同的环境中。在不同的环境中，物化视图的作用也不相同。数据仓库中的物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。在数据仓库中，还经常使用查询重写（query rewrite）机制，这样不需要修改原有的查询语句，Oracle会自动选择合适的物化视图进行查询，完全对应用透明。物化视图和表一样可以直接进行查询。物化视图可以基于分区表，物化视图本身也可以分区。除了在数据仓库中使用，物化视图还用于复制、移动计算等方面。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

 

三、创建物化试图主要选项说明

名称	ON PREBUILD TABLE
描述	将已经存在的表注册为物化视图。同时还必须提供描述创建该表的查询的 SELECT 子句。可能无法始终保证查询的精度与表的精度匹配。为了克服此问题，应该在规范中包含 WITH REDUCED PRECISION 子句。

 

名称	Build Clause	创建方式
描述	包括BUILD IMMEDIATE和BUILD DEFERRED两种
取值	BUILD IMMEDIATE	在创建物化视图的时候就生成数据
BUILD DEFERRED	在创建时不生成数据，以后根据需要在生成数据
默认	BUILD IMMEDIATE

 

名称	Refresh	刷新子句
描述	当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步
语法	[refresh [fast | complete | force]          [on demand | commit]          [start with date]          [next date]          [with {primary key | rowid}] ]
取值	FAST	采用增量刷新，只刷新自上次刷新以后进行的修改
COMPLETE	对整个物化视图进行完全的刷新
FORCE(默认)	Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用Fast方式，否则采用Complete的方式，Force选项是默认选项
ON DEMAND(默认)	物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新
ON COMMIT	物化视图在对基表的DML操作提交的同时进行刷新
START WITH	第一次刷新时间
NEXT	刷新时间间隔
WITH PRIMARY KEY(默认)	生成主键物化视图,也就是说物化视图是基于表的主键，而不是ROWID(对应于ROWID子句)。 为了生成PRIMARY KEY子句，应该在表上定义主键，否则应该用基于ROWID的物化视图。主键物化视图允许识别物化视图表而不影响物化视图增量刷新的可用性
WITH ROWID	只有一个单一的主表，不能包括下面任何一项: ●Distinct ●聚合函数 ●Group by ●子查询 ●连接 ●SET操作

 

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

 

四、创建实体化试图日志主要选项说明

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

 

名称	WITH Clause
描述
取值	OBJECT ID	如果是对象物化视图(object materialized view)，则只能采用该方式
PRIMARY KEY
ROWID
SEQUENCE
默认

 

 

四、例子

创建物化视图时应先创建存储的日志空间

 

create materialized view log on table1    
   
tablespace ts_data --日志保存在特定的表空间     

with rowid;   

 

CREATE MATERIALIZED VIEW LOG ON table 
WITH ROWID, SEQUENCE(id, name,STATUS)
INCLUDING NEW VALUES;

 

 

然后创建物化视图

 

create materialized view mv_table1         

on prebuild table --将物化视图建立在一个已经存在的表上         

tablespace ts_data --保存表空间         

build deferred --延迟刷新不立即刷新         

refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新         

on demand --按照指定方式刷新         

as select * from table1;  

 

CREATE MATERIALIZED VIEW mv_table 
BUILD IMMEDIATE 
REFRESH FAST 
ON COMMIT 
AS SELECT pre_id,SUM(AMOUNT) as A_AMOUNT 
FROM table WHERE STATUS =1 GROUP BY pre_id;

 

 

删除物化视图日志

drop materialized view log on table1;   

 删除物化视图

drop materialized view mv_table1;

 

 ------------------------------------------------------------------------------------------------------------------------------------

说明2

http://blog.sina.com.cn/s/blog_5b2470430100ek3o.html

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

一、            关于物化视图日志：

查询物化视图日志文件格式：desc mlog$_lzwmvtest;

创建物化视图时默认指定物化视图中存在主键，如果不指定，那么创建的物化视图日志文件的基表必须存在主键，否则会报错

Demo:对一个表test

创建日志：create materialized view log on test;

那么会报：表'LZWMVTEST'不包含主键约束条件

这种情况下，就必须指定日志文件结构

比如：create materialized view log on test with rowid（具体的针对日志内容方面的在另外一个专题里说明，这里就简述到此）

二、            关于生成数据和刷新：

1>生成数据

两大选项：build immediate   build deferred

Build immediate:在创建物化视图的同时根据主表生成数据

Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh(‘MV_name’,’C’)，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。

2>关于刷新

²        刷新方式：complete fast force

Complete :完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新

Ø         Fast:当有数据更新时依照相应的规则对物化视图进行更新（此时必须创建物化视图日志（物化视图日志记录了数据更新的日志），关于日志的说明，参照“物化视图日志文件介绍”）

Ø         Force:当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）

不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下（在创建物化视图语句中，可能某些限制查询的条件，导致了增量刷新无法使用，这个是需要注意的，具体是哪类语句导致fast刷新不可用，有待总结…..）

²        刷新时间：on demand on commit start with/ next

On demand:在需要刷新时进行刷新（人工判断）

On commit:在基表上有提交操作时，进行更新

Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）

Next:刷新的周期时间

三、            基于主键的物化视图和ROWID的物化视图的说明

创建物化视图日志时，指定了记录更新的原则即with 后面的primary 或者rowid 或者object id等等，后面，默认是以primary key为记录更新，在物化视图内也是以此为更新的原则。

例如：1、如果日志内使用的是primary key 则在创建物化视图时指定rowid来更新，则会报ORA-12032:不能使用 "TEST" 上实体化视图日志中的 rowid 列

2、如过日志内使用的是rowid 则在创建物化视图时指定primary或者默认指定，则会报ORA-23415: "GIS"."LZWMV" 的实体化视图日志不记录主键

四、            关于物化视图存放的的表空间

直接在创建物化视图时指定日志存放的表空间和物化视图的表空间。

例子：create materialized view MV_TEST tablespace test ----表空间名称

五、            关于查询重写和更新

在创建查询重写时，基表中必须有主键约束，视图里是无法创建主键的，不过其继承了基表的主键约束。（关于视图的创建的一些技巧有待总结……）下面给个例子

Create materialized view MV_TEST

Refresh fast ----前提是必须创建基表日志,可以忽略该项

Enable query rewrite ----前提是基表上必须存在主键约束

As

Select * from TEST; ----物化视图数据生成

六、            关于创建物化视图的例子：

1、 使用增量刷新的物化视图的写法

创建物化视图日志，必须创建日志

Create materialized view log on TEST ----TEST为表名

----注：(TEST为表名或者视图名，关于视图上建立物化视图，见基于视图的物化视图

----创建物化视图语句：

Create materialized view MV_TEST

----MVTEST为物化视图名

Build immediate

----创建时生成数据对应的是build deferred

Refresh fast

----增量刷新

On commit

----在基表有更新时提交，这里该句对视图无效

With rowid

----这里创建基于rowid的物化视图，对应的是 primary key

As

Select * from TEST;

----生成物化视图数据语句

七、            小的知识点

创建主键约束语句：alter table table_name add(constraint constraint_name primary key(columes));

建议使用job来定期刷新物化视图