Oracle 物化视图

1.创建物化视图

 

--基表有提交就立即更新
create materialized view MV_TEST
refresh force on commit
as
select count(*) as total from test;

 

--每分钟刷新一下物化视图
create materialized view mv_test refresh force on demand
start with sysdate next sysdate+1/1440
as select count(*) as total from test;

  

 

 

以下转自：http://www.itpub.net/thread-1614812-1-1.html

 

一、物化视图的一般用法物化视图是一种特殊的物理表，“物化”(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的查询结果，可以得到佐证；
创建语句：
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指物化视图不进行任何刷新。
对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图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;
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
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;
  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); 
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、删除物化视图日志物化视图日志经常会由于物化视图长时间没有刷新，或者基表的一次批量数据更改而变得很大，这会影响物化视图的刷新性能，因此对于这种情况需要对物化视图日志进行处理，降低物化视图日志表的高水位线。
物化视图日志会记录下基表所有的增、删、改操作，而物化视图执行完快速刷新操作后，会从物化视图日志中将本物化视图刷新过且其他物化视图所不需要刷新的记录删除掉。如果其中一个物化视图一直不刷新，那么物化视图日志就会变得越来越大。
还有一种情况，比如表中插入了大量的数据，或者删除了大量的数据，或者将表中的某一列统一更新为一个值，这种操作都会在物化视图日志中产生大量的记录。
而物化视图日志的增大必然影响物化视图的刷新速度。一方面，物化视图在刷新的时候要扫描物化视图日志，另一方面，物化视图在刷新介绍后，也要清除物化视图日志中的记录，仍然要扫描物化视图日志，因此物化视图日志的大小直接会影响物化视图快速刷新的速度。更重要的是，物化视图日志的高水位一旦增长到一个很高的位置，即使以后物化视图日志中记录很少，甚至没有记录存在，物化视图在刷新的时候仍然需要较长的时间。
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) 决定是否要保持视图与基础表数据同步。
如果不同步，可选择如下三种刷新方式：
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数据刷新的时间
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;