oracle 物化视图基础知识整理

-----------------------------------------------2014-09-03--------------------------------------------------------------------
昨晚给一张普通表做分区表改造，报错ora-26563，查看发现是该表上创建有物化视图日志，需将其删除方能创建。
虽常听闻物化视图一词，亦曾多次尝试系统学习一番，终未静心做之。
而今万事归定，定心为之，自勉！


---------------------------------------------------------------------------------------------------------------

一、物化视图概述

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

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


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


   对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。
   在复制环境下，创建的物化视图通常情况下是主键，rowid，和子查询视图。
   
   物化视图由于是物理真实存在的，故而可以创建索引
   


1.1 物化视图可以分为以下三种类型


a、包含聚集的物化视图
b、只包含连接的物化视图
c、嵌套物化视图


三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。创建物化视图时可以指定多种选项，下面对几种主要的选项进行简单说明。


（1）创建方式：包括build immediate和build deferred两种。
               build immediate是在创建物化视图的时候就生成数据
               build deferred则在创建时不生成数据，以后根据需要再生成数据。默认为build immediate


（2）查询重写：包括enable query rewrite 和disable query rewrite两种。
               分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询的时候，oracle会自动判断能否通过查询物化视图来得到结果。
               如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为disable query rewrite
               
（3）刷新时间：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：on demand 和on commit
               on demand指物化视图在用户需要的时候进行刷新，可以手工通过dbms_mview.refresh等方法进行刷新，也可以通过job定时进行刷新，即更新物化试图，
               以保证和基表数据的一致性。
               on commit是说，一旦基表有了commit，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。
               对基表，平常的commit在0.01秒内可以完成，但在有了on commit视图后，居然会到6秒，速度降了很多。on commit视图对基表的影响可见一斑
               
（4）刷新方式：包括complete，fast，force，never
               complete：完全刷新。会删除表中所有的记录（如果是单表刷新，可能会采用truncate方式），然后根据物化视图中查询语句的定义重新生成物化视图
               fast：快速刷新。采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。fast必须创建基于主表的视图日志
                     对于增量刷新选项，如果子查询中存在分析函数，则物化视图不起作用
               force：oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。
               
               oracle物化视图的快速刷新机制是通过物化视图日志完成的。oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。
               物化视图日志根据不同物化视图的快速刷新的需要，可以建立为rowid或primary key类型的，还可以选择是否包括sequence，including new values以及指定列的列表
               
               never:从不刷新


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


REFRESH子句可以包含如下部分：


refresh [fast] [complete] [force]             
        [on demand] [commit]
        [start with date] [next date]
        [with [primary key] [rowid]]
        
        
1.2.1 主键和rowid子句
with primary key选项生成主键物化视图，也就是说物化视图是基于主表的主键，而不是rowid。
primary key是默认选项。为了生成primary子句，应该在主表上定义主键，否则应该使用基于rowid的物化视图


基于rowid物化视图只有一个单一的主表，不能包括下面任何一项：
a、distinct或聚合函数
b、group by，子查询，连接，和set操作




---主键物化视图示例：
在远程数据库emp上创建主键物化视图：


create materialized view mv_emp_pk
refresh fast 
start with sysdate
next sysdate + 1/48
as select * from emp@orcl;


---当用fast选项创建物化视图，必须创建基于主表的视图日志如下：


create materialized view log emp;



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


create materialized view mv_emp_rowid
refresh with rowid
as select * from emp@orcl;




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


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


create materialized view mv_empdept
as select * from emp@orcl e
where exists
(select * from dept@orcl where e.dept_no=d.dept_no);




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


create materialized view mv_emp_pk
refresh fast
start with sysdate
next sysdate + 2
with primary key
as select * from emp@orcl;


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




create materialized view mv_sample
tablespace users                                                     ----------指定表空间
build deferred                                                       ----------延迟刷新，不立即刷新
refresh force                                                        ----------可以进行快速刷新则执行快速刷新，否则执行完全刷新
on demand                                                            ----------按照指定方式刷新
start with to_date('2014-09-03 12:00:00','yyyy-mm-dd hh24:mi:ss')    ----------第一次刷新时间
next trunc(sysdate+1) + 18/24                                        ----------以后刷新时间的间隔
as select * from emp@orcl    



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

1.4 物化视图


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



--------------------------------------------------------------------------------------------------------------------------------------------------------------------


二、物化视图操作示例


1、创建物化视图需要的权限
grant create materialized view to user_name;


2、在源表上建立物化视图日志
create materialized view log on tablename
tablespace users
with primary key;


3、授权给中间用户
grant select on table_name to  other_user;
grant select on mlog$_tablename to other_user;


4、在目标物化视图上创建索引
create index idx_mv on mv_emp(id,name) tablespace users;  --------------和在普通表上创建没有区别




5、使用dbms_refresh.refresh 过程来批量刷新mv


如果在创建物化视图的过程指定start和next date的刷新时间，那么oracle会自动创建刷新的job，并采用dbms_refresh.refresh的方式
使用这种方式刷新之前需要先make refresh group，然后才可以刷新


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


exec dbms_refresh.make('REP_TEST','MV_T1','MV_T2','MV_T3',SYSDATE,'SYSDATE+1');


exec dbms_refresh.refresh('REP_TEST');



6、删除物化视图及日志
drop materialized view log on table_name;


drop materialized view mv_emp;


7、查看物化视图刷新状态信息：


select mview_name,last_refresh_date,staleness from dba_mviews;                    ----stale  陈旧的，过期的


--------------------------------------------------------------------------------------------------------------------------------------------------------


自己动手实验，创建一个物化视图：


create materialized view mv_king
build deferred                                 ----这个要放在refresh之前
refresh fast
on demand
start with sysdate
next sysdate + 1/1440
with primary key                               ----默认是主键方式，要求基表上要有主键
as select * from mv_test@zwj;                  ----事先要在mv_test上创建物化视图日志




create materialized view log on mv_test;



----------------------------------------------------------------------------------------


现在问题是创建完后压根看不到数据刷新。




手动刷新下MV:


exec dbms_MVIEW.refresh('MV_KING','C');


手动刷新后，物化视图可以自动刷新了。


--------------------------------------------------------------------------------------


删掉上面创建的MV ，重新创建一个，以build immediate方式


drop materialized view mv_king;
drop materialized view log on mv_test;


create materialized view mv_king
build immediate
refresh fast
on demand 
start with sysdate
next sysdate + 1/1440
with primary key

as select * from mv_test@zwj;

-------------------------------------------------------------