【听海日志】之 ORACLE 物化视图

一、物化视图的一般用法
物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普
通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图 SQL 语
句的查询。这样对整体查询性能的提高，并没有实质上的好处。
1、物化视图的类型
ON DEMAND、ON COMMIT。二者的区别在于刷新方法的不同，ON DEMAND 顾名思
义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证
和基表数据的一致性；而 ON COMMIT 是说，一旦基表有了 COMMIT，即事务提交，则立
刻刷新，立刻更新物化视图，使得数据和基表一致。
物化视图可以分为以下三种类型：包含聚集的物化视图；只包含连接的物化视图；嵌套
物化视图。三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。
创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明：
创建方式（Build Methods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD
IMMEDIATE 是在创建物化视图的时候就生成数据，而 BUILD DEFERRED 则在创建时不生
成数据，以后根据需要在生成数据。默认为 BUILD IMMEDIATE。
查询重写(Query Rewrite)：包括 ENABLE QUERY REWRITE 和 DISABLE QUERY
REWRITE 两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图
的基表进行查询时，Oracle 会自动判断能否通过查询物化视图来得到结果，如果可以，则避
免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为 DISABLE QUERY
REWRITE。
在建立物化视图的时候可以指定 ORDER BY 语句，使生成的数据按照一定的顺序进行保
存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。
2、ON DEMAND 物化视图
物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统
而言。但 Oracle 允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及
到默认值问题。也就是说 Oracle 给物化视图的重要定义参数的默认值处理是我们需要特别注
意的。物化视图的特点：
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可
以被 user_tables 查询出来，而得到佐证；
(2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；
(3) 物化视图会占用数据库磁盘空间，这点从 user_segment 的查询结果，可以得到佐
证；
创建语句：
【听海日志】之 ORACLE 物化视图
第 2 页 共 10 页
SQL> create materialized view mv_name as select * from table_name;
默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。
物化视图的数据怎么随着基表而更新？
Oracle 提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们
手工的执行某个 Oracle 提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这
是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用
相同的存储过程或包，加以实现。
ON DEMAND 物化视图的特性及其和 ON COMMIT 物化视图的区别，即前者不刷新(手
工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了
COMMIT。
创建定时刷新的物化视图(指定物化视图每天刷新一次)：
SQL> create materialized view mv_name refresh force on demand start with sysdate next
sysdate+1;
上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每
天晚上 10：00 定时刷新一次）：
SQL> create materialized view mv_name refresh force on demand start with sysdate next
to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');
3、ON COMMIT 物化视图
ON COMMIT 物化视图的创建，和上面创建 ON DEMAND 的物化视图区别不大。因为
ON DEMAND 是默认的，所以 ON COMMIT 物化视图，需要再增加个参数即可。
需要注意的是，无法在定义时仅指定 ON COMMIT，还得附带个参数才行。创建 ON
COMMIT 物化视图：
SQL> create materialized view mv_name refresh force on commit as select * from
table_name;
备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）。
4、物化视图的刷新
刷新(Refresh)：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行
同步。
刷新的模式有两种：ON DEMAND 和 ON COMMIT。
刷新的方法有四种：FAST、COMPLETE、FORCE 和 NEVER。FAST 刷新采用增量刷
新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整个物化视图进行完全的刷
新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以
则采用 FAST 方式，否则采用 COMPLETE 的方式。NEVER 指物化视图不进行任何刷新。
【听海日志】之 ORACLE 物化视图
第 3 页 共 10 页
对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图 mv_name 的刷新
方式修改为每天晚上 10 点刷新一次：
SQL> alter materialized view mv_name refresh force on demand start with sysdate next
to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss');
5、物化视图日志
如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的
快速刷新的需要，可以建立为 ROWID 或 PRIMARY KEY 类型的。还可以选择是否包括
SEQUENCE、INCLUDING NEW VALUES 以及指定列的列表。
可以指明 ON PREBUILD TABLE 语句将物化视图建立在一个已经存在的表上。这种情况
下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询
重写要求参数 QUERY_REWRITE_INTEGERITY 必须设置为 trusted 或者 stale_tolerated。
6、物化视图分区
而且基于分区的物化视图可以支持分区变化跟踪(PCT)。具有这种特性的物化视图，当基
表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在 GROUP
BY 列表中使用 CUBE 或 ROLLUP，来建立不同等级的聚集物化视图。
二、物化视图与数据迁移
Oracle 的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较
多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到
结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化
视图对应用透明，增加和删除物化视图不会影响应用程序中 SQL 语句的正确性和有效性；物
化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。
如如何建立在特定的表空间上，这些在其他的物化视图上面几乎都没有任何介绍的。主
要以我做的一个例子来操作，如果对物化视图的基本概念清楚了就比较明白在那里写特定的
表空间存储了。
1、简单试验
在 master site 上创建表和 mview log
SQL> create table stu (id varchar2(10) primary key ,name varchar2(20));
Table created.
SQL> create materialized view log on stu;
Materialized view log created.
在 mv site 上创建 mview
SQL> create materialized view stu_mv refresh fast start with sysdate next sysdate+1/1440 with
primary key as select * from stu@to_vm9;
【听海日志】之 ORACLE 物化视图
第 4 页 共 10 页
Materialized view created.
SQL> select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs;
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC
INTERVAL WHAT
--- --------- ----------- --------- ----------- ---------- -------------- -----------------------------------------
21 SEAGULL 2008-2-18 1 14:41:43 2008-2-18 1 14:42:43 sysdate+1/1440
dbms_refresh.refresh('"SEAGULL"."STU_MV"');
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
STU_MV TABLE
在 master site 上对 master table 做更新：
SQL> INSERT INTO STU(ID,NAME) VALUES('56','555555555555');
1 row created.
SQL> commit;
等 1 分钟后在 mv site 上检查
SQL> select * from stu_mv;
ID NAME
---------- --------------------
56 555555555555
2、跨版本数据迁移
利用 prebuilt mv 实现跨平台，跨版本数据迁移。该方法的实现原理是对于要迁移的表对
象，需要有一个主键，用于 mv 的刷新，对于符合该要求的表，在源表上创建 mv 日志，再在
目标数据库上创建结构一样的表，然后在目标表上采用 prebuilt 方式创建 mv，第一次采用完
全刷新，之后采用增量刷新，等真正要切换的时候，只需要刷新完增量的日志，删除 mv，保
留目标表即可。基本思路的例子：
在源库上创建表和 mview log
SQL> create table big_t1 as select * from dba_objects;
Table created.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
【听海日志】之 ORACLE 物化视图
第 5 页 共 10 页
SQL> create materialized view log on big_t1;
Materialized view log created.
在目标数据库上创建与该表一样的表，并在该表上创建 prebuilt mv:
SQL> create table big_t1 as select * from big_t1@to_vm9 where 1=2;
Table created.
SQL> select count(1) from big_t1;
COUNT(1)
----------
0
SQL> create materialized view big_t1 on prebuilt table refresh fast as select * from
big_t1@to_vm9;
Materialized view created.
做完全刷新和增量刷新
SQL> exec dbms_mview.refresh('BIG_T1','Complete');
PL/SQL procedure successfully completed.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
此时模拟在做完全刷新过程中，源库的表又发生了变化
SQL> insert into big_t1(object_id,owner) values(99991,'test');
1 row created.
SQL> commit;
Commit complete.
再做增量刷新
SQL> select count(1) from big_t1;
COUNT(1)
----------
6170
SQL> exec dbms_mview.refresh('BIG_T1');
PL/SQL procedure successfully completed.
SQL> select count(1) from big_t1;
【听海日志】之 ORACLE 物化视图
第 6 页 共 10 页
COUNT(1)
----------
6171
停机切换,做最后一次刷新，然后删除源库的 mview log 和目标库的 mview：
SQL> exec dbms_mview.refresh('BIG_T1');
PL/SQL procedure successfully completed.
SQL> drop materialized view big_t1;
Materialized view dropped.
SQL> select count(1) from big_t1;
COUNT(1)
----------
6171
这里删除的 mview(big_t1)是 prebuilt mv，所以删除该 mview,并不删除相应的表。如果删
除了 mvnew(stu_mv),由于是普通 mv,则删除了该 mview,就没有对应的表了。
SQL> drop materialized view stu_mv;
Materialized view dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIG_T1 TABLE
3、创建存储的日志空间
SQL> CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID ;
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID,sequence(LEVYDETAILDATAID);
SQL> CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH rowid,sequence(LEVYDATAID);
【听海日志】之 ORACLE 物化视图
第 7 页 共 10 页
4、然后创建物化视图
SQL> create materialized view MV_LVY_LEVYDETAILDATA
TABLESPACE ZGMV_DATA --保存表空间
BUILD DEFERRED --延迟刷新不立即刷新
refresh force --如果可以快速刷新则进行快速刷新，否则完全刷新
on demand --按照指定方式刷新
start with to_date('24-11-2005 18:00:10', 'dd-mm-yyyy hh24:mi:ss') --第一次刷新时间
next TRUNC(SYSDATE+1)+18/24 --刷新时间间隔
as
SELECT levydetaildataid, detaildatano, taxtermbegin, taxtermend,
......
ROUND(taxdeduct * taxpercent1, 2) - ROUND(taxdeduct * taxpercent2, 2) -
ROUND(taxdeduct * taxpercent3, 2) - ROUND(taxdeduct * taxpercent4, 2) -
ROUND(taxdeduct * taxpercent5, 2) taxdeduct, ROUND(taxfinal * taxpercent1, 2) -
ROUND(taxfinal * taxpercent2, 2) - ROUND(taxfinal * taxpercent3, 2) -
ROUND(taxfinal * taxpercent4, 2) - ROUND(taxfinal * taxpercent5, 2) taxfinal,
a.levydataid, a.budgetitemcode, taxtypecode,
......
FROM tb_lvy_levydetaildata a, tb_lvy_levydata c, MV_LVY_LEVYTAXBGTDIV b
WHERE a.levydataid = c.levydataid
AND a.budgetdistrscalecode = b.budgetdistrscalecode
AND a.budgetitemcode = b.budgetitemcode
AND c.incomeresidecode = b.rcvfisccode
AND C.TAXSTATUSCODE='08'
AND C.NEGATIVEFLAG!='9'
5、删除物化视图日志
物化视图日志经常会由于物化视图长时间没有刷新，或者基表的一次批量数据更改而变
得很大，这会影响物化视图的刷新性能，因此对于这种情况需要对物化视图日志进行处理，
降低物化视图日志表的高水位线。
【听海日志】之 ORACLE 物化视图
第 8 页 共 10 页
物化视图日志会记录下基表所有的增、删、改操作，而物化视图执行完快速刷新操作后，
会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果
其中一个物化视图一直不刷新，那么物化视图日志就会变得越来越大。
还有一种情况，比如表中插入了大量的数据，或者删除了大量的数据，或者将表中的某
一列统一更新为一个值，这种操作都会在物化视图日志中产生大量的记录。
而物化视图日志的增大必然影响物化视图的刷新速度。一方面，物化视图在刷新的时候
要扫描物化视图日志，另一方面，物化视图在刷新介绍后，也要清除物化视图日志中的记录，
仍然要扫描物化视图日志，因此物化视图日志的大小直接会影响物化视图快速刷新的速度。
更重要的是，物化视图日志的高水位一旦增长到一个很高的位置，即使以后物化视图日志中
记录很少，甚至没有记录存在，物化视图在刷新的时候仍然需要较长的时间。
SQL> DROP materialized view log on mv_lvy_levytaxbgtdiv;
SQL> DROP materialized view log on tb_lvy_levydetaildata;
SQL> DROP materialized view log on tb_lvy_levydata;
6、删除物化视图
SQL> drop materialized view MV_LVY_LEVYDETAILDATA;
基本和对表的操作一致，物化视图由于是物理真实存在的，故可以创建索引，创建方式
和对普通表创建方式相同。
三、ORACLE 物化视图总结
物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生
成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图
可以查询表，视图和其它的物化视图。主要用在数据仓库和决策支持系统。
通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。对于复
制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副
本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中
抽取。对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视
图。
物化视图把他的物理结构存储在自己的段中，该段可以被索引和分区。查询不必完全匹
配用来创建物化视图的SQL语句，优化程序可以动态重写一个与原定义相近的查询，以便物
化视图用来代替实际的表，这种查询重写自动发生，对用户是透明的。
1、使用物化视图前的几个配置步骤
(1) 确定那些语句要创建物化视图。
(2) 决定是否要保持视图与基础表数据同步。
如果不同步，可选择如下三种刷新方式：
【听海日志】之 ORACLE 物化视图
第 9 页 共 10 页
COMPLETE：刷新启动时，先 truncate 物化视图，再从基础表重新插入填充数据。
FAST：只刷新基础表上次刷新后改变的数据。使用视图的日志数据或 ROWID 完成。
FORCE：默认的方式。先使用 FAST，不行就使用 COMPLETE 方式。
(3) 设置 init.ora 的参数：
JOB_QUEUE_PROCESSES，必须设置大于 1。
QUERY_REWRITE_ENABLED，设置为 TRUE 时，允许动态重写查询。
QUERY_REWRITE_INTEGRITY，确定访问物化视图时数据一致性要遵守的程度。
OPTIMIZER_MODE，必须设置成 CBO 的某种方式。
使用一个物化视图，用户只需在基础表上拥有权限即可。
2、创建物化视图
SQL>create materialized view emp_by_district
Tablespace mview_data
Build immediate
Refresh fast
Enable query rewrite
As
Select d.id,count(e.last_name) from distributor dist,district d,employee e
Where e.id = dist.manager_id
And d.id dist.district_id
Group by d.id;
以下是 Oracle 创建物化视图时的常用语法,各参数的含义如下:
1、refresh [fast|complete|force] 视图刷新的方式
fast: 增量刷新.假设前一次刷新的时间为 t1,那么使用 fast 模式刷新物化视图时,只向视图
中添加 t1 到当前时间段内,主表变化过的数据.为了记录这种变化，建立增量刷新物化视图还
需要一个物化视图日志表。create materialized view log on （主表名）。
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用 fast 模式时，数据刷新将采用 fast 方式；
否则使用 complete 方式。
2、MV 数据刷新的时间
【听海日志】之 ORACLE 物化视图
第 10 页 共 10 页
on demand:在用户需要刷新的时候刷新，这里就要求用户自己动手去刷新数据了（也可
以使用 job 定时刷新）
on commit:当主表中有数据提交的时候，立即刷新 MV 中的数据；
start ……：从指定的时间开始，每隔一段时间（由 next 指定）就刷新一次；
3、Build immediate 一共有三个选项
(1) Build immediate：建立物化视图，并使用当前命令执行的数据马上填充视图数据。
(2) Build deferred：只建立物化视图，在第一次刷新之间不填充数据。
(3) No prebuilt table，使用事先已存在的，已含有视图定义中有现有数据的表，而不是
建立一个新结构来保存数据。
如果是 refresh fast on commit 或 refresh complete on commit 创建的，则在基础表提交的时
候都会得到刷新。启用或禁用物化视图，需要有 query rewrite 或 global query rewrite 权限。
3、刷新物化视图
自动刷新：
(1) 使用 commit 选项。
(2) 使用 dbms_mview 安排自动刷新时间。
手工刷新：
SQL>execute dbms_mview.refresh(‘EMP_BY_DISTRICT’); －－刷新指定的物化视图
SQL>execute dbms_mview.refresh_defresh_dependent(‘EMPLOYEE’); ――刷新利用了
该表的所有物化视图
SQL>execute dbms_mview.refresh_all_mviews; ――刷新该模式中，自上次刷新以来，
未得到刷新的所有物化视图。
4、禁用物化视图
－ 修改 init.ora 参数的 query_rewrite_enabled 参数设置成 flase，重启实例。
－ 使用 alter system set query_rewrite_enabled = flase;动态修改。
－ 使用 alter session set query_rewrite_enabled = flash;修改会话内。
－ 使用 norewrite 提示。
5、删除物化视图
SQL>drop materialized view emp_by_district;