Oracle物化视图创建全过程

Oracle物化视图创建全过程我们如果遇到需要从其它系统的数据库中取数据进行统计分析的问题，可疑选择使用ORACLE的ODI工具进行抽数，但是对方提供的数据库用户下没有任何对象，只是有查询所有表的权限，因此无法做数据反向。

于是决定使用物化视图，把对方数据库中的数据拿过来，虽然数据量比较大，但是每月只拿一次，而且如果设置成增量更新，也不会太慢。

现在记录下物化视图的创建过程（以一张表为例）。

一、准备条件以及备注假设双方数据库都是ORACLE10g，需要同步过来的表名叫：GG_ZLX_ZHU，对方数据库用户名：username，密码：password，SID：CPEES。

二、开始干活1、首先要创建DB_LINKCREATE DATABASE LINK to_cpeesCONNECT TO "username" identified by "password"using "CPEES";或者create database link to_cpeesconnect to username identified by "password"using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =IP地址)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = cti)))';其中CPEES为新建的到对方数据库的TNS。

执行，现在我们就已经创建了到对方数据库的DB_LINK TO_CPEES。

2、创建Oracle物化视图快速刷新日志因为上面说过，以后视图的刷新将采用增量刷新的方式，因此，为配合增量刷新，ORACLE要求要在住表上建立物化视图日志。

CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHUWITH PRIMARY KEYINCLUDING NEW VALUES;(上面的SQL要在远程数据库上执行，不能在本地执行)3、创建Oracle物化视图Oracle物化视图，从名字上面来开，它应该是属于视图，但是确实物化。

其物化是针对普通视图并没有真正的物理存储而言，其实可以简单的把物化视图看做一个物理表。

CREATE MATERIALIZED VIEW GG_ZLX_ZHU --创建物化视图BUILD IMMEDIATE --在视图编写好后创建REFRESH FAST WITH PRIMARY KEY --根据主表主键增量刷新（FAST，增量）ON DEMAND -- 在用户需要时，由用户刷新ENABLE QUERY REWRITE --可读写ASSELECT * FROM GG_ZLX_ZHU@TO_CPEES; --查询语句4、视图刷新根据业务需要，每月不定时刷新，所以不能是JOB，而且数量多，所以也不能一个一个刷新。

根据以上条件，选择使用ORACLE 自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。

该方法有两个参数，第一个参数是需要刷新的物化视图名称，第二个参数是刷新方式。

我们可以写存储过来，对每个物化视图调用一次REFRESH方法，也可以使用“，”把物化视图连接以来，一次刷新。

如下：CREATE OR REPLACE PROCDURE P_MVIEW_REFRESHASBEGINDBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f');END P_MVIEW_REFRESH;或者使用CREATE OR REPLACE PROCDURE P_MVIEW_REFRESHASBEGINDBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');END P_MVIEW_REFRESH;注意:1、如果需要同时刷新多个物化视图，必须用逗号把各个物化视图名称连接起来，并对每个视图都要指明刷新方式（f、增量刷新，c、完全刷新，?、强制刷新）。

2、当日志和物化视图创建好后，删除日志，则需要重新创建物化视图，否则无法增量刷新。

3、因为上面写的物化视图时根据主键进行更新，因此，主表必须有主键。

忘了写删除方法了，日志和物化视图要分开删除DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;DROP MATERIALIZED VIEW GG_ZLX_ZHU;一、------------------------------------------------------------------------------------------物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

物化视图存储基于远程表的数据，也可以称为快照。

物化视图可以查询表，视图和其它的物化视图。

通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。

对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。

如果你想修改本地副本，必须用高级复制的功能。

当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。

对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

本篇我们将会看到怎样创建物化视图并且讨论它的刷新选项。

在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。

1.主键物化视图：下面的语法在远程数据库表emp上创建主键物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_pkREFRESH FAST START WITH SYSDATENEXT SYSDATE + 1/48WITH PRIMARY KEYAS SELECT * FROM emp@remote_db;Materialized view created.注意：当用FAST选项创建物化视图，必须创建基于主表的视图日志,如下:SQL> CREATE MATERIALIZED VIEW LOG ON emp;Materialized view log created.2.Rowid物化视图：下面的语法在远程数据库表emp上创建Rowid物化视图SQL> CREATE MATERIALIZED VIEW mv_emp_rowidREFRESH WITH ROWIDAS SELECT * FROM emp@remote_db;Materialized view log created.3.子查询物化视图：下面的语法在远程数据库表emp上创建基于emp和dept表的子查询物化视图SQL> CREATE MATERIALIZED VIEW mv_empdeptAS SELECT * FROM emp@remote_db eWHERE EXISTS (SELECT * FROM dept@remote_db d WHERE e.dept_no = d.dept_no);Materialized view log created.REFRESH 子句[refresh [fast|complete|force][on demand | commit][start with date] [next date][with {primary key|rowid}]]Refresh选项说明:a. oracle用刷新方法在物化视图中刷新数据.b. 是基于主键还是基于rowid的物化视图c. 物化视图的刷新时间和间隔刷新时间Refresh方法-FAST子句增量刷新用物化视图日志（参照上面所述）来发送主表已经修改的数据行到物化视图中.如果指定REFRESH FAST子句，那么应该对主表创建物化视图日志SQL> CREATE MATERIALIZED VIEW LOG ON emp;对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

Refresh方法-COMPLETE子句完全刷新重新生成整个视图，如果请求完全刷新，oracle会完成完全刷新，即使增量刷新可用。

Refresh Method – FORCE 子句当指定FORCE子句，如果增量刷新可用Oracle将完成增量刷新，否则将完成完全刷新,如果不指定刷新方法(FAST, COMPLETE, or FORCE),Force选项是默认选项主键和ROWD子句WITH PRIMARY KEY选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是ROWID(对应于ROWID子句). PRIMARY KEY是默认选项,为了生成PRIMARY KEY子句，应该在主表上定义主键，否则应该用基于ROWID的物化视图.主键物化视图允许识别物化视图主表而不影响物化视图增量刷新的可用性。

Rowid物化视图只有一个单一的主表，不能包括下面任何一项:n Distinct 或者聚合函数.n Group by，子查询，连接和SET操作刷新时间START WITH子句通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点, NEXT 子句说明了刷新的间隔时间.SQL> CREATE MATERIALIZED VIEW mv_emp_pkREFRESH FASTSTART WITH SYSDATENEXT SYSDATE + 2WITH PRIMARY KEYAS SELECT * FROM emp@remote_db;Materialized view created.在上面的例子中，物化视图数据的第一个副本在创建时生成，以后每两天刷新一次.二、------------------------------------------------------------------------------------------Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。

物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

物化视图可以分为以下三种类型：包含聚集的物化视图；只包含连接的物化视图；嵌套物化视图。

三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。