物化视图使用详解

1、一 物化视图概述Oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。物化视图可以查询表，视图和其它的物化视图。通常情况下，物化

2、视图被称为主表（在复制期间）或明细表（在数据仓库中）。对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。物化视图由于是物理真实存在的，故可以创建索引。1.1 物化视图可以分为以下三种类型（1） 包含聚集的物化视图；（2） 只包含连接的物化视图；（3） 嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别，而对于其他方

3、面则区别不大。创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明：（1）创建方式（BuildMethods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据。BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。（2）查询重写（QueryRewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracl

4、e会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLEQUERY REWRITE。（3）刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND和ON COMMIT物化视图的区别在于其刷新方法的不同，ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新,即更新物化视图，以保证和基表数据的一致性;而ON CO

5、MMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。对基表，平常的COMMIT在0.01秒内可以完成，但在有了ON COMMIT视图后，居然要6秒。速度减低了很多倍。ON COMMIT视图对基表的影响可见一斑。1.2 物化视图，根据不同的着重点可以有不同的分类:1) 按刷新方式分：FAST/COMPLETE/FORCE2) 按刷新时间的不同：ON DEMAND/ON COMMIT3) 按是否可更新：UPDATABLE/READ ONLY4) 按是否支持查询重写：ENABLE QUERY REWRITE/DISABLEQUERY REW

6、RITE默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。注意：设置REFRESH ON COMMIT的物化视图不能访问远端对象。在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。1.3 物化视图有三种刷新方式：COMPLETE、FAST和 FORCE。1) 完全刷新（COMPLETE）会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。2) 快速刷新（FAST）采用增

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

8、列表。1.4 物化视图Refresh子句的其他说明与示例REFRESH 子句可以包含如下部分：refresh fast|complete|forceon demand | commitstart with date next datewith primary key|rowid1.4.1 主键和ROWD子句：WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句). PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图.基于ROW

9、ID物化视图只有一个单一的主表，不能包括下面任何一项:（1）.Distinct 或者聚合函数.（2） .Group by，子查询，连接和SET操作-主键（PrimaryKey）物化视图示例：在远程数据库表emp上创建主键物化视图：sql view plaincopy1. CREATEMATERIALIZEDVIEW mv_emp_pk2. REFRESHFASTSTARTWITHSYSDATE3. NEXT SYSDATE + 1/484. WITHPRIMARYKEY5. ASSELECT * FROM empremote_dbsql vie

10、w plain copy 1. CREATEMATERIALIZEDVIEW mv_emp_pk  2. REFRESHFASTSTARTWITHSYSDATE  3. NEXT  SYSDATE + 1/48  4. WITHPRIMARYKEY  5. ASSELECT * FROM empremote_db  -当用FAST选项创建物化视图，必须创建基于主表的视图日志,如下:sql 

11、;view plaincopy1. CREATEMATERIALIZEDVIEWLOGON emp;sql view plain copy 1. CREATEMATERIALIZEDVIEWLOGON emp;  -Rowid物化视图示例：下面的语法在远程数据库表emp上创建Rowid物化视图sql view plaincopy1. CREATEMATERIALIZEDVIEW mv_emp_rowid2. REFRESHWITHROWID3. ASSELECT * FROM empremote_db;4

12、. Materializedviewlog created.sql view plain copy 1. CREATEMATERIALIZEDVIEW mv_emp_rowid  2. REFRESHWITHROWID  3. ASSELECT * FROM empremote_db;  4. Materializedviewlog created.  -子查询物化视图示例：在远程数据库表emp上创建基于emp和dept表的子查询物化视

13、图sql view plaincopy1. CREATEMATERIALIZEDVIEW mv_empdept2. ASSELECT * FROM empremote_db e3. WHEREEXISTS4. (SELECT * FROM deptremote_db d5. WHEREe.dept_no = d.dept_no)sql view plain copy 1. CREATEMATERIALIZEDVIEW  mv_empdept  2. ASSELECT

14、 * FROM empremote_db e  3. WHEREEXISTS  4. (SELECT * FROM deptremote_db d  5. WHEREe.dept_no = d.dept_no)  1.4.2 刷新时间START WITH子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的间隔时间.sql view plai

15、ncopy1. CREATEMATERIALIZEDVIEW mv_emp_pk2. REFRESHFAST3. STARTWITHSYSDATE4. NEXT SYSDATE + 25. WITHPRIMARYKEY6. ASSELECT * FROM empremote_db;sql view plain copy 1. CREATEMATERIALIZEDVIEW mv_emp_pk  2. REFRESHFAST  3. STARTWITHSYSDATE  

16、4. NEXT  SYSDATE + 2  5. WITHPRIMARYKEY  6. ASSELECT * FROM empremote_db;  在上面的例子中，物化视图数据的第一个副本在创建时生成，以后每两天刷新一次.sql view plaincopy1. creatematerializedviewMV_LVY_LEVYDETAILDATA2. TABLESPACE ZGMV_DATA -保存表空间3. BUILDDEFERRED-延迟

17、刷新不立即刷新4. refreshforce-如果可以快速刷新则进行快速刷新，否则完全刷新5. ondemand-按照指定方式刷新6. startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi:ss') -第一次刷新时间7. nextTRUNC(SYSDATE+1)+18/24-刷新时间间隔8. as9. SELECT * FROM empremote_db;sql view plain copy 1. createmate

18、rializedviewMV_LVY_LEVYDETAILDATA  2. TABLESPACE ZGMV_DATA -保存表空间  3. BUILDDEFERRED-延迟刷新不立即刷新  4. refreshforce-如果可以快速刷新则进行快速刷新，否则完全刷新  5. ondemand-按照指定方式刷新  6. startwith to_date('24-11-200518:00:10', 'dd-mm-yyyyhh24:mi

19、:ss') -第一次刷新时间  7. nextTRUNC(SYSDATE+1)+18/24-刷新时间间隔  8. as  9. SELECT * FROM empremote_db;  1.5 ON PREBUILD TABLE 说明在创建物化视图时指明ON PREBUILD TABLE语句，可以将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_

20、INTEGERITY必须设置为trusted或者stale_tolerated。1.6 物化视图分区物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。相关内容参考：Oracle 物化视图日志 与 快速刷新 说明Oracle 物化视图 详细错误描述 查看方法Oracle 物化视图 快速刷新 限制 说明二 物化视图操作示例1. 创建物化视图需要的权限：sql

21、60;view plaincopy1. GRANT CREATE MATERIALIZED VIEW TO USER_NAME;sql view plain copy 1. GRANT CREATE MATERIALIZED VIEW TO USER_NAME;  2. 在源表建立物化视图日志sql view plaincopy1. CREATE MATERIALIZED VIEW LOG

22、0;ON DAVE2. TABLESPACE&BISONCU_SPACE - 日志空间3. WITH PRIMARY KEY; - 指定为主键类型sql view plain copy 1. CREATE MATERIALIZED VIEW LOG ON DAVE  2. TABLESPACE&BISONCU_SPACE         &#

23、160; - 日志空间  3. WITH PRIMARY KEY;                   - 指定为主键类型  3. 授权给中间用户sql view plaincopy1. GRANT SELECT ON DAVE TO ANQING;

24、2. GRANT SELECT ON MLOG$_DAVE TO ANQING;sql view plain copy 1. GRANT SELECT ON DAVE TO ANQING;  2. GRANT SELECT ON MLOG$_DAVE TO ANQING;  4. 在目标数据库上创建MATERIALIZED VIEWsql view plai

25、ncopy1. CREATE MATERIALIZED VIEW AICS_DAVE2. TABLESPACE&BISONCS_SPACE3. REFRESH FAST4. ON DEMAND5. -第一次刷新时间6. -START WITH to_date('2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')7. START WITH SYSDATE8. -刷新时间间隔。每1天刷新一次，时间为凌晨2点9. -NEXT TRUNC(SYSDATE,

26、9;dd')+1+2/2410. NEXT SYSDATE+1/24/2011. WITH PRIMARY KEY12. -USING DEFAULT LOCAL ROLLBACKSEGMENT13. DISABLE QUERY REWRITE AS14. SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_TIME, UPDATE_TIME, SW_VERSION15. FROM AICS_DAVELINK_DAVE;sql view p

27、lain copy 1. CREATE MATERIALIZED VIEW AICS_DAVE  2. TABLESPACE&BISONCS_SPACE  3. REFRESH FAST  4.    ON DEMAND  5.    -第一次刷新时间  6.    -START WITH to_date(&#

28、39;2012-01-01 20:00:00', 'yyyy-mm-dd hh24:mi:ss')  7.    START WITH SYSDATE  8.    -刷新时间间隔。每1天刷新一次，时间为凌晨2点  9.    -NEXT TRUNC(SYSDATE,'dd')+1+2/24  10.   &

29、#160;NEXT SYSDATE+1/24/20  11. WITH PRIMARY KEY  12. -USING DEFAULT LOCAL ROLLBACKSEGMENT  13. DISABLE QUERY REWRITE AS  14. SELECT MODEL_ID, STATUS,MODEL_NAME, MANU_ID, DESCRIPTION, CREATE_

30、TIME, UPDATE_TIME, SW_VERSION  15. FROM AICS_DAVELINK_DAVE;  5. 在目标物化视图上创建索引sql view plaincopy1. CREATE INDEX IDX_T_DV_CT2. ON AICS_DEV_INFO (CREATE_TIME, UPDATE_TIME)3. TABLESPACE &BISON_IDX;4.5. CREATE INDEX IDX_T_DV_UT6. ON

31、 AICS_DEV_INFO (UPDATE_TIME)7. TABLESPACE &BISON_IDX;8.9. CREATE INDEX I_T_DV_MSISDN10. ON AICS_DEV_INFO (MSISDN)11. TABLESPACE &BISON_IDX;sql view plain copy 1. CREATE INDEX IDX_T_DV_CT  2.    ON AICS_DEV_INFO (

32、CREATE_TIME, UPDATE_TIME)  3.    TABLESPACE &BISON_IDX;  4.    5. CREATE INDEX IDX_T_DV_UT  6.    ON AICS_DEV_INFO (UPDATE_TIME)  7.    TABLESPACE &BISON_IDX

33、;  8.    9. CREATE INDEX I_T_DV_MSISDN  10.    ON AICS_DEV_INFO (MSISDN)  11.    TABLESPACE &BISON_IDX;  6. 物化视图刷新说明（1）使用dbms_mview.refresh 手工刷新如：sql view plaincopy1. EXEC

34、 DBMS_MVIEW.REFRESH('MV_DAVE');2.3. -完全刷新4. EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'c');5. EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');6.7. -快速刷新8. EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD =&g

35、t; 'f');9. EXEC DBMS_MVIEW.REFRESH('MV_DAVE','F');sql view plain copy 1. EXEC DBMS_MVIEW.REFRESH('MV_DAVE');  2.    3. -完全刷新  4. EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD

36、 => 'c');  5. EXEC DBMS_MVIEW.REFRESH('MV_DAVE','C');  6.    7. -快速刷新  8. EXEC DBMS_MVIEW.REFRESH(LIST => 'MV_DAVE',METHOD => 'f');  9. EXEC DBMS_MVIEW

37、.REFRESH('MV_DAVE','F');  （2）使用dbms_refresh.refresh 过程来批量刷新MV如果我们在创建物化视图的过程指定start 和next time的刷新时间，那么Oracle 会自动创建刷新的job，并采用dbms_refresh.refresh 的方式。使用这种方式刷新之前需要先make refresh group，然后才可以刷新。Refreshmake 的语法可以参考：示例：假设存在物化视图MV_T1, MV_T2, MV_T3. 创建refresh group的语法如下：sql view plaincopy1. SQL> EXEC DBMS_REFRESH.MAKE('REP_TEST', 'MV_T1,MV_T2,MV_T3', SYSDATE, 'SYSDATE+ 1')2.3. -刷新整个refresh group 组：4. SQL> EXEC DBMS_REFRESH.REFRESH('REP_TEST')sql view p