物化视图详解

相关内容参考：
oracle 物化视图 说明：
https://www.cnblogs.com/xiaohuilong/p/5995596.html

 
一、物化视图概述
oracle的物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。

物化视图可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中sql语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。

物化视图可以查询表，视图和其它的物化视图。通常情况下，物化视图被称为主表（在复制期间）或明细表（在数据仓库中）。对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。在复制环境下，创建的物化视图通常情况下主键，rowid,和子查询视图。

materialized view和普通VIEW有什么区别：

materialized view(MV)是自动刷新或者手动刷新的，View不用刷新 MV也可以直接update，但是不影响base table，对View的update反映到base table上 MV主要用于远程数据访问，mv中的数据需要占用磁盘空间，view中不保存数据

 

物化视图由于是物理真实存在的，故可以创建索引。
 
1.1 物化视图可以分为以下三种类型

（1）包含聚集的物化视图；

（2）只包含连接的物化视图；

（3）嵌套物化视图。

三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明： （1）创建方式（buildmethods）：包括build immediate和build deferred两种。

build immediate是在创建物化视图的时候就生成数据。

build deferred则在创建时不生成数据，以后根据需要在生成数据。

默认为build immediate。

 
（2）查询重写（queryrewrite）：包括enable query rewrite和disable query rewrite两种。

分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。

默认为disablequery rewrite。

 
（3）刷新（refresh）：指当基表发生了dml操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：on demand和on commit。

on demand和on commit物化视图的区别在于其刷新方法的不同，on demand指物化视图在用户需要的时候进行刷新，可以手工通过dbms_mview.refresh等方法来进行刷新，也可以通过job定时进行刷新,即更新物化视图，以保证和基表数据的一致性;而on commit是说，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

对基表，平常的commit在0.01秒内可以完成，但在有了on commit视图后，居然要6秒。速度减低了很多倍。on commit视图对基表的影响可见一斑。

默认情况下，如果没指定刷新方法和刷新模式，则oracle默认为force和demand。

 
1.2 物化视图，根据不同的着重点可以有不同的分类:

1) 按刷新方式分：fast/complete/force
   
2) 按刷新时间的不同：on demand/on commit
   
3) 按是否可更新：updatable/read only
   
4) 按是否支持查询重写：enable query rewrite/disablequery rewrite

默认情况下，如果没指定刷新方法和刷新模式，则oracle默认为force和demand。

注意：设置refresh on commit的物化视图不能访问远端对象。

在建立物化视图的时候可以指定order by语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。

 
1.3 物化视图有三种刷新方式：complete、fast和 force。

1) 完全刷新（complete）会删除表中所有的记录（如果是单表刷新，可能会采用truncate的方式），然后根据物化视图中查询语句的定义重新生成物化视图。

2) 快速刷新（fast）采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。fast必须创建基于主表的物化视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

3) 采用force方式，oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。

oracle物化视图的快速刷新机制是通过物化视图日志完成的。o racle通过一个物化视图日志还可以支持多个物化视图的快速刷新。

物化视图日志根据不同物化视图的快速刷新的需要，可以建立为rowid或primary key类型的。还可以选择是否包括sequence、including new values以及指定列的列表。

默认情况下，如果没指定刷新方法和刷新模式，则oracle默认为force和demand。

 
1.4 物化视图refresh子句的其他说明与示例

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

 
1.4.1 主键和rowd子句：

with primary key选项生成主键物化视图,也就是说物化视图是基于主表的主键，而不是rowid(对应于rowid子句). primary key是默认选项, 为了生成primary key子句，应该在主表上定义主键，否则应该用基于rowid的物化视图.

注意：创建物化视图时默认指定物化视图中存在主键，如果不指定，那么创建的物化视图日志文件的基表必须存在主键，否则会报错。

基于rowid物化视图只有一个单一的主表，不能包括下面任何一项:

（1）distinct 或者聚合函数.
（2）group by，子查询，连接和set操作

--主键（primarykey）物化视图示例：

在远程数据库表emp上创建主键物化视图：
create materialized view mv_emp_pk refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;
    
源库：192.168.56.12 wang
目标库：192.168.56.20 slient

源库：
sql> show user
user is "scott"
sql> create database link scottlink connect to scott identified by tiger using '  (description =
  2      (address = (protocol = tcp)(host = 192.168.56.20)(port = 1521))
  3      (connect_data =
  4        (server = dedicated)
  5        (service_name = service3 )
  6      )
  7    )';

database link created.

sql> select * from dba_db_links;

owner      db_link         username        host                                                                   created
---------- --------------- --------------- ---------------------------------------------------------------------- ------------
scott      scottlink       scott             (description =                                                       27-apr-18
                                               (address = (protocol = tcp)(host = 192.168.56.20)(port = 1521))
                                               (connect_data =
                                                 (server = dedicated)
                                                 (service_name = service3 )
                                               )
                                             )

sql> create materialized view MV_EMP_PK refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;
create materialized view mv_emp_pk refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink
*
error at line 1:
ora-23413: table "scott"."emp" does not have a materialized view log

报错，显示要创建物化视图日志；

SQL> create materialized view log on emp@SCOTTLINK;
create materialized view log on emp@SCOTTLINK
                                   *
ERROR at line 1:
ORA-00949: illegal reference to remote database

显示快速刷新无法创建目标库基本的物化视图日志，所以改一下刷新方式：由fast改为force,如下：

sql> create materialized view mv_emp_pk refresh force start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;

materialized view created.

方法二：显示可以在目标库创建基于基本的物化视图日志，再在源库创建物化视图，如下：
源库操作：
SQL> create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;
create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink
                                                                                                                           *
ERROR at line 1:
ORA-23413: table "SCOTT"."EMP" does not have a materialized view log

目标库操作：
SQL> create materialized view log on emp;

Materialized view log created.

源库再次创建：成功
SQL> create materialized view mv_emp_pk_2 refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp@scottlink;

Materialized view created. --当用fast选项创建物化视图，必须创建基于主表的视图日志,如下:

SQL> create materialized view MV_EMP refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp;
create materialized view mv_emp refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp
                                                                                                                      *
ERROR at line 1:
ORA-23413: table "SCOTT"."EMP" does not have a materialized view log

报错显示创建本地物化视图快速刷新机制要先建物化视图日志，如下:

SQL> create materialized view log on emp;

Materialized view log created.

SQL> create materialized view mv_emp refresh fast start with sysdate next sysdate + 1/48 with primary key as select * from emp;

Materialized view created.

成功创建。。。

--rowid物化视图示例：

下面的语法在远程数据库表emp上创建rowid物化视图

SQL> create materialized view MV_EMP_ROWID  refresh with rowid  as select * from emp@scottlink;  

Materialized view created.
 
注意默认的刷新条件是 force,默认情况下，如果没指定刷新方法和刷新模式，则oracle默认为force和demand。
 

--子查询物化视图示例：

在远程数据库表emp上创建基于emp和dept表的子查询物化视图

SQL> CREATE MATERIALIZED VIEW  MV_EMPDEPT AS SELECT * FROM emp@scottlink e WHERE EXISTS (SELECT * FROM dept@scottlink d WHERE e.DEPTNO = d.DEPTNO);

Materialized view created.

 
1.4.2 刷新时间

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

SQL> create materialized view MV_EMP_PK_1 refresh complete start with sysdate next sysdate + 2 with primary key as select * from emp@scottlink;  

Materialized view created.

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

create materialized view mv_lvy_levydetaildata  
tablespace users --保存表空间  
build deferred--延迟刷新,不立即刷新  
refresh force--如果可以快速刷新则进行快速刷新，否则完全刷新  
on demand--按照指定方式刷新  
start with to_date('24-11-200518:00:10', 'dd-mm-yyyyhh34:mi:ss') --第一次刷新时间  
next trunc(sysdate+1)+18/24--刷新时间间隔  
as  
select * from emp@scottlink;   1.5 on prebuild table 说明

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

 
1.6 物化视图分区

物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（pct）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。对于聚集物化视图，可以在group by列表中使用cube或rollup，来建立不同等级的聚集物化视图。
二、物化视图操作示例

1. 创建物化视图需要的权限：

grant create materialized view to user_name;  

2.  在源表建立物化视图日志  

    create materialized view log on dave  
    tablespace&bisoncu_space           -- 日志空间  
    with primary key;                   -- 指定为主键类型  

3.  授权给中间用户  

    grant select on dave to anqing;  
    grant select on mlog$_dave to anqing;  

4.  在目标数据库上创建materialized view  

    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 hh34: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.  在目标物化视图上创建索引

    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 手工刷新

如：
    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 和next time的刷新时间，那么oracle 会自动创建刷新的job，并采用dbms_refresh.refresh 的方式。

使用这种方式刷新之前需要先make refresh group，然后才可以刷新。

refreshmake 的语法可以参考：
Http://docs.oracle.com/cd/b19306_01/server.102/b14227/rarrefreshpac.htm#i94057

 
示例：
假设存在物化视图mv_t1, mv_t2, mv_t3. 创建refresh group的语法如下：

sql> exec dbms_refresh.make('rep_test', 'mv_t1,mv_t2,mv_t3', sysdate, 'sysdate+ 1')  
   
--刷新整个refresh group 组：  
sql> exec dbms_refresh.refresh('rep_test')   7. 删除物化视图及日志

--删除物化视图日志：  
drop materialized view log on dave;  

--删除物化视图  
drop materialized view mv_dave;  

物化视图删除的顺序：
http://blog.itpub.net/4227/viewspace-242683/   8. 查看物化视图刷新状态信息
--查询物化视图信息：
set lines 200
col OWNER for a10
col MVIEW_NAME a10
col QUERY for a30
col MASTER_LINK for a15   
col eligible_for_fast_refree for a25
col REWRITE_ENABLED for a15
col REFRESH_MODE for a15
col REFRESH_METHOD for a15
select a.OWNER,a.MVIEW_NAME,a.QUERY,a.MASTER_LINK,a.REWRITE_ENABLED,a.REFRESH_MODE,a.REFRESH_METHOD,a.BUILD_MODE,a.FAST_REFRESHABLE eligible_for_fast_refree,a.LAST_REFRESH_TYPE Method_for_recent_refresh,a.LAST_REFRESH_DATE,a.STALENESS Relationship,a.COMPILE_STATE Validity  from dba_mviews a where a.MVIEW_NAME in('MV_EMP_PK','MV_EMP','MV_EMP_ROWID','MV_EMPDEPT','MV_EMP_PK_1','MV_EMP_PK_2'); select * from dba_mview_refresh_times;  

--查询物化视图日志信息：
select * from dba_mview_logs
9. 查询物化视图日志：
select * from mlog$_dave;  

oracle物化视图日志结构:
http://blog.itpub.net/31397003/viewspace-2146803/