Oracle物化视图详解

现实工作中会有多个数据源同步到一个数据库完成数据分析的场景，这些数据可以不是实时同步的，我们一般通过定时任务抽取数据到统计分析库给应用使用。

一般的同步方式可以通过时间戳做全量和增量数据同步（存在原数据变化可能，数据不一致的情况），也可以通过dblink做数据实时查询（较损耗线上数据库性能），一般最好的方式是通过建立物化视图，然后通过schedual job完成定时数据同步，这里就记录下物化视图的使用。

一、物化视图简介

物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。

1、物化视图分类

ON DEMAND：该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；

ON COMMIT：一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致；

默认情况创建物化视图不指定类型，则是按需刷新（on demand）

2、物化视图

 

二、物化视图使用

1、物化视图创建

物化视图的数据来源于基表，而刷新的起始点记录于物化视图日志，所以创建物化视图授权必须有基表——>物化视图日志（基于基表）——>物化视图

物化视图创建示例：

在dbtest下创建物化视图T，其中基表是scott用户下的dept表
（1）授权dbtest用户可以查询scott.dept
grant select on scott.dept to dbtest;
（2）在dbtest用户下创建表T （若创建物化视图加on prebuilt table） 
create table t as select * from scott.dept where 1=2; 
（3）在scott用户下创建物化视图日志，在dbtest下创建物化视图T 
创建物化视图日志： 
conn scott/tiger; 
create materialized view log on dept; 
grant select on MLOG$_DEPT to dbtest;
创建物化视图： 
conn dbtest/dbtest; 
create materialized view T
on prebuilt table
refresh fast on demand
as
select deptno,dname,loc,ACOLUMN from scott.dept;

##可以通过在 view T后加上BUILD IMMEDIATE参数立刻刷新物化视图，得到数据

REFRESH 子句可以包含如下部分：
   [refresh [fast|complete|force]
   [on demand | commit]
   [start with date] [next date]
   [with {primary key|rowid}]]

2、物化视图刷新

当基表有更新后（DML），如果不是on commit类型，物化视图需要刷新后数据才能保持和基表一致，刷新方式有全量刷新（COMPLETE）、快速刷新（增量FAST）、强制刷新（FORCE）、不刷新（NEVER）

FAST：增量快速刷新

exec dbms_mview.refresh('表名', 'F')  

exec dbms_mview.refresh('dbtest.t','F'); 

COMPLETE：全量刷新

exec dbms_mview.refresh('表名', 'C')   ;

exec dbms_mview.refresh('dbtest.t','C'); 

FORCE：刷新时判断否可以快速刷新，如果能快速刷新则执行fast刷新，如果不能则执行complete刷新

NEVER：不刷新

3、物化视图删除

drop MATERIALIZED VIEW  mview_name;

4、物化视图日志删除

物化视图日志是mlog$_basetablename命名格式

DROP MATERIALIZED VIEW LOG  on base_table_name;

MLOG$_DEPT 

 DEPTNO           主键列
 SNAPTIME$$       用于表示刷新时间
 DMLTYPE$$        用于表示dml操作类型，i表示insert，d表示delete，u表示update
 OLD_NEW$$        用于表示这个值是新值还是旧值。n（ew）表示新值（一般为delete操作），o（ld）表示旧值（一般为Insert操作），u表示update操作。
 CHANGE_VECTOR$$  表示修改矢量，用来表示被修改的是哪个或哪几个字段
 XID$$              

如果with后面跟了primary key，则物化视图日志中会包含主键列。
如果with后面跟了rowid，则物化视图日志中会包含： m_row$$：用来存储发生变化的记录的rowid。
如果with后面跟了object id，则物化视图日志中会包含：sys_nc_oid$：用来记录每个变化对象的对象id。
如果with后面跟了sequence，则物化视图日子中会包含：sequence$$：给每个操作一个sequence号，从而保证刷新时按照顺序进行刷新。
如果with后面跟了一个或多个column名称，则物化视图日志中会包含这些列。  

当基本表发生dml操作时，会记录到物化视图日志中，这时指定的时间4000年1月1日0时0分0秒（物化视图未被刷新）。
如果物化视图日志供多个物化视图使用，则一个物化视图刷新后会将它刷新的记录的时间更新为它刷新的时间。
只有建立快速刷新的物化视图才能使用物化视图日志，如果只建立一个物化视图，则物化视图刷新完会将物化视图日志清除掉

--当创建物化视图日志使用primary key时，oracle创建临时表 RUPD$_基础表

5、查看物化视图

set line 200;
set pagesize 20000;
col owner for a15;
col mview_name for a30;
col query for a60;

select owner,mview_name,refresh_method,last_refresh_date,compile_state from dba_mviews;

#如果要看具体语句，可以通过query字段查看

 

三、附录

 

物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Oracle都实际上转换为视图SQL语句的查询。这样对整体查询性能的提高，并没有实质上的好处。