物化视图使用详解

一．

物化视图概述\o"Oracle知识库"Oracle的物化视图是包括一个查询结果的\o"MySQL知识库"数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。物化视图可以查询表，视图和其它的物化视图。通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。物化视图由于是物理真实存在的，故可以创建索引。1.1

物化视图可以分为以下三种类型（1）包含聚集的物化视图；（2）只包含连接的物化视图；（3）嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明：（1）创建方式（BuildMethods）：包括BUILDIMMEDIATE和BUILDDEFERRED两种。BUILDIMMEDIATE是在创建物化视图的时候就生成数据。BUILDDEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILDIMMEDIATE。（2）查询重写（QueryRewrite）：包括ENABLEQUERYREWRITE和DISABLEQUERYREWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERYREWRITE。（3）刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ONDEMAND和ONCOMMIT。ONDEMAND和ONCOMMIT物化视图的区别在于其刷新方法的不同，ONDEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新,即更新物化视图，以保证和基表数据的一致性;而ONCOMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。对基表，平常的COMMIT在0.01秒内可以完成，但在有了ONCOMMIT视图后，居然要6秒。速度减低了很多倍。ONCOMMIT视图对基表的影响可见一斑。1.2

物化视图，根据不同的着重点可以有不同的分类:1)按刷新方式分：FAST/COMPLETE/FORCE2)按刷新时间的不同：ONDEMAND/ONCOMMIT3)按是否可更新：UPDATABLE/READONLY4)按是否支持查询重写：ENABLEQUERYREWRITE/DISABLEQUERYREWRITE默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。注意：设置REFRESHONCOMMIT的物化视图不能访问远端对象。在建立物化视图的时候可以指定ORDERBY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。1.3

物化视图有三种刷新方式：COMPLETE、FAST和

FORCE。1)完全刷新（COMPLETE）会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。2)快速刷新（FAST）采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。3)采用FORCE方式，Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。Oracle物化视图的快速刷新机制是通过物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARYKEY类型的。还可以选择是否包括SEQUENCE、INCLUDINGNEWVALUES以及指定列的列表。1.4

物化视图Refresh子句的其他说明与示例REFRESH子句可以包含如下部分：[refresh[fast|complete|force][ondemand|commit][startwithdate][nextdate][with{primarykey|rowid}]]1.4.1

主键和ROWD子句：WITHPRIMARYKEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句).PRIMARYKEY是默认选项,为了生成PRIMARYKEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图.基于ROWID物化视图只有一个单一的主表，不能包括下面任何一项:（1）.Distinct或者聚合函数.（2）.Groupby，子查询，连接和SET操作--主键（PrimaryKey）物化视图示例：在远程数据库表emp上创建主键物化视图：[sql]

\o"viewplain"viewplain\o"copy"copyCREATEMATERIALIZEDVIEWmv_emp_pkREFRESHFASTSTARTWITHSYSDATENEXT

SYSDATE+1/48WITHPRIMARYKEYASSELECT*

FROM

emp@remote_db[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATEMATERIALIZEDVIEW

mv_emp_pk

REFRESHFASTSTARTWITHSYSDATE

NEXT

SYSDATE

+

1/48

WITHPRIMARYKEY

ASSELECT

*

FROM

emp@remote_db

--当用FAST选项创建物化视图，必须创建基于主表的视图日志,如下:[sql]

\o"viewplain"viewplain\o"copy"copyCREATEMATERIALIZEDVIEWLOGONemp;[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATEMATERIALIZEDVIEWLOGON

emp;

--Rowid物化视图示例：下面的语法在远程数据库表emp上创建Rowid物化视图[sql]

\o"viewplain"viewplain\o"copy"copyCREATEMATERIALIZEDVIEWmv_emp_rowidREFRESHWITHROWIDASSELECT*

FROM

emp@remote_db;Materializedviewlogcreated.[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATEMATERIALIZEDVIEW

mv_emp_rowid

REFRESHWITHROWID

ASSELECT

*

FROM

emp@remote_db;

Materializedviewlog

created.

--子查询物化视图示例：在远程数据库表emp上创建基于emp和dept表的子查询物化视图[sql]

\o"viewplain"viewplain\o"copy"copyCREATEMATERIALIZEDVIEWmv_empdeptASSELECT*

FROM

emp@remote_dbeWHEREEXISTS(SELECT

*

FROM

dept@remote_dbdWHEREe.dept_no=d.dept_no)[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATEMATERIALIZEDVIEW

mv_empdept

ASSELECT

*

FROM

emp@remote_db

e

WHEREEXISTS

(SELECT

*

FROM

dept@remote_db

d

WHEREe.dept_no

=

d.dept_no)

1.4.2

刷新时间STARTWITH子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点,NEXT子句说明了刷新的间隔时间.[sql]

\o"viewplain"viewplain\o"copy"copyCREATEMATERIALIZEDVIEWmv_emp_pkREFRESHFASTSTARTWITHSYSDATENEXT

SYSDATE+2WITHPRIMARYKEYASSELECT*

FROM

emp@remote_db;[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATEMATERIALIZEDVIEW

mv_emp_pk

REFRESHFAST

STARTWITHSYSDATE

NEXT

SYSDATE

+

2

WITHPRIMARYKEY

ASSELECT

*

FROM

emp@remote_db;

在上面的例子中，物化视图数据的第一个副本在创建时生成，以后每两天刷新一次.[sql]

\o"viewplain"viewplain\o"copy"copycreatematerializedviewMV_LVY_LEVYDETAILDATATABLESPACEZGMV_DATA

--保存表空间BUILDDEFERRED--延迟刷新不立即刷新refreshforce--如果可以快速刷新则进行快速刷新，否则完全刷新ondemand--按照指定方式刷新startwithto_date('24-11-200518:00:10',

'dd-mm-yyyyhh24:mi:ss')

--第一次刷新时间nextTRUNC(SYSDATE+1)+18/24--刷新时间间隔asSELECT

*

FROM

emp@remote_db;[sql]

\o"viewplain"viewplain

\o"copy"copy

creatematerializedviewMV_LVY_LEVYDETAILDATA

TABLESPACE

ZGMV_DATA

--保存表空间

BUILDDEFERRED--延迟刷新不立即刷新

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

ondemand--按照指定方式刷新

startwith

to_date('24-11-200518:00:10',

'dd-mm-yyyyhh24:mi:ss')

--第一次刷新时间

nextTRUNC(SYSDATE+1)+18/24--刷新时间间隔

as

SELECT

*

FROM

emp@remote_db;

1.5ONPREBUILDTABLE

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

物化视图分区物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在GROUPBY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。相关内容参考：Oracle物化视图日志与快速刷新说明/tianlesoftware/article/details/7720580Oracle物化视图详细错误描述查看方法/tianlesoftware/article/details/7719789Oracle物化视图快速刷新限制说明/tianlesoftware/article/details/7719679二．

物化视图操作示例1.

创建物化视图需要的权限：[sql]

\o"viewplain"viewplain\o"copy"copyGRANT

CREATE

MATERIALIZED

VIEW

TO

USER_NAME;[sql]

\o"viewplain"viewplain

\o"copy"copy

GRANT

CREATE

MATERIALIZED

VIEW

TO

USER_NAME;

2.

在源表建立物化视图日志[sql]

\o"viewplain"viewplain\o"copy"copyCREATE

MATERIALIZED

VIEW

LOG

ON

DAVETABLESPACE&BISONCU_SPACE

--日志空间WITH

PRIMARY

KEY;

--指定为主键类型[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATE

MATERIALIZED

VIEW

LOG

ON

DAVE

TABLESPACE&BISONCU_SPACE

--

日志空间

WITH

PRIMARY

KEY;

--

指定为主键类型

3.

授权给中间用户[sql]

\o"viewplain"viewplain\o"copy"copyGRANT

SELECT

ON

DAVE

TO

ANQING;GRANT

SELECT

ON

MLOG$_DAVE

TO

ANQING;[sql]

\o"viewplain"viewplain

\o"copy"copy

GRANT

SELECT

ON

DAVE

TO

ANQING;

GRANT

SELECT

ON

MLOG$_DAVE

TO

ANQING;

4.

在目标数据库上创建MATERIALIZEDVIEW[sql]

\o"viewplain"viewplain\o"copy"copyCREATE

MATERIALIZED

VIEW

AICS_DAVETABLESPACE&BISONCS_SPACEREFRESHFASTON

DEMAND--第一次刷新时间--STARTWITHto_date('2012-01-0120:00:00','yyyy-mm-ddhh24:mi:ss')START

WITH

SYSDATE--刷新时间间隔。每1天刷新一次，时间为凌晨2点--NEXTTRUNC(SYSDATE,'dd')+1+2/24NEXT

SYSDATE+1/24/20WITH

PRIMARY

KEY--USINGDEFAULTLOCALROLLBACKSEGMENTDISABLEQUERYREWRITE

ASSELECT

MODEL_ID,STATUS,MODEL_NAME,MANU_ID,DESCRIPTION,CREATE_TIME,UPDATE_TIME,SW_VERSIONFROM

AICS_DAVE@LINK_DAVE;[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATE

MATERIALIZED

VIEW

AICS_DAVE

TABLESPACE&BISONCS_SPACE

REFRESH

FAST

ON

DEMAND

--第一次刷新时间

--START

WITH

to_date('2012-01-01

20:00:00',

'yyyy-mm-dd

hh24:mi:ss')

START

WITH

SYSDATE

--刷新时间间隔。每1天刷新一次，时间为凌晨2点

--NEXT

TRUNC(SYSDATE,'dd')+1+2/24

NEXT

SYSDATE+1/24/20

WITH

PRIMARY

KEY

--USING

DEFAULT

LOCAL

ROLLBACKSEGMENT

DISABLE

QUERY

REWRITE

AS

SELECT

MODEL_ID,

STATUS,MODEL_NAME,

MANU_ID,

DESCRIPTION,

CREATE_TIME,

UPDATE_TIME,

SW_VERSION

FROM

AICS_DAVE@LINK_DAVE;

5.

在目标物化视图上创建索引[sql]

\o"viewplain"viewplain\o"copy"copyCREATE

INDEX

IDX_T_DV_CTON

AICS_DEV_INFO(CREATE_TIME,UPDATE_TIME)TABLESPACE&BISON_IDX;CREATE

INDEX

IDX_T_DV_UTON

AICS_DEV_INFO(UPDATE_TIME)TABLESPACE&BISON_IDX;CREATE

INDEX

I_T_DV_MSISDNON

AICS_DEV_INFO(MSISDN)TABLESPACE&BISON_IDX;[sql]

\o"viewplain"viewplain

\o"copy"copy

CREATE

INDEX

IDX_T_DV_CT

ON

AICS_DEV_INFO

(CREATE_TIME,

UPDATE_TIME)

TABLESPACE

&BISON_IDX;

CREATE

INDEX

IDX_T_DV_UT

ON

AICS_DEV_INFO

(UPDATE_TIME)

TABLESPACE

&BISON_IDX;

CREATE

INDEX

I_T_DV_MSISDN

ON

AICS_DEV_INFO

(MSISDN)

TABLESPACE

&BISON_IDX;

6.

物化视图刷新说明（1）使用dbms_mview.refresh

手工刷新如：[sql]

\o"viewplain"viewplain\o"copy"copyEXEC

DBMS_MVIEW.REFRESH('MV_DAVE');--完全刷新EXEC

DBMS_MVIEW.REFRESH(LIST=>

'MV_DAVE',METHOD=>

'c');EXEC

DBMS_MVIEW.REFRESH('MV_DAVE','C');--快速刷新EXEC

DBMS_MVIEW.REFRESH(LIST=>

'MV_DAVE',METHOD=>

'f');EXEC

DBMS_MVIEW.REFRESH('MV_DAVE','F');[sql]

\o"viewplain"viewplain

\o"copy"copy

EXEC

DBMS_MVIEW.REFRESH('MV_DAVE');

--完全刷新

EXEC

DBMS_MVIEW.REFRESH(LIST

=>

'MV_DAVE',METHOD

=>

'c');

EXEC

DBMS_MVIEW.REFRESH('MV_DAVE','C');

--快速刷新

EXEC

DBMS_MVIEW.REFRESH(LIST

=>

'MV_DAVE',METHOD

=>

'f');

EXEC

DBMS_MVIEW.REFRESH('MV_DAVE','F');

（2）使用dbms_refresh.refresh

过程来批量刷新MV如果我们在创建物化视图的过程指定start和nexttime的刷新时间，那么Oracle会自动创建刷新的job，并采用dbms_refresh.refresh的方式。使用这种方式刷新之前需要先makerefreshgroup，然后才可以刷新。Refreshmake的语法可以参考：/cd/B19306_01/server.102/b14227/rarrefreshpac.htm#i94057示例：假设存在物化视图MV_T1,

MV_T2,MV_T3.创建refreshgroup的语法如下：[sql]

\o"viewplain"viewplain\o"copy"copySQL>

EXEC

DBMS_REFRESH.MAKE('REP_TEST',

'MV_T1,MV_T2,MV_T3',SYSDATE,

'SYSDATE+1')--刷新整个refreshgroup组：SQL>

EXEC

DBMS_REFRESH.REFRESH('REP_TEST')[sql]

\o"viewplain"viewplain

\o"copy"copy

SQL>

EXEC

DBMS_REFRESH.MAKE('REP_TEST',

'MV_T1,MV_T2,MV_T3',

SYSDATE,