(1) 物化视图在某种意义上说就是一个 物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到确认;(2) 物化视图也是一种段(segment),所以其有自己的物理存储属性;(3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证;创建语句:create materialized view mv_name as select * from table_name因为物化视图由于是物理真实存在的,故可以创建索引。
分为两种: build immediate 和 build deferred,build immediate是在创建物化视图的时候就生成数据。build deferred则在创建时不生成数据,以后根据需要在生成数据。如果不指定,则默认为 build immediate。
物化视图有二种刷新模式:在创建时refresh mode是 on demand 还是 on commit。on demand 顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性;on commit 提交触发,一旦基表有了commit,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致。一般用这种方法在操作基表时速度会比较慢。创建物化视图时未作指定,则Oracle按 on demand 模式来创建。上面说的是刷新的模式,针对于如何刷新,则有三种刷新方法:完全刷新(COMPLETE): 会删除表中所有的记录(如果是单表刷新,可能会采用TRUNCATE的方式),然后根据物化视图中查询语句的定义重新生成物化视图。快速刷新(FAST): 采用增量刷新的机制,只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项,如果在子查询中存在分析函数,则物化视图不起作用。FORCE方式:这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件,如果满足则进行快速刷新,否则进行完全刷新。关于快速刷新:Oracle物化视图的 快速刷新机制是通过 物化视图日志完成的。Oracle通过一个物化视图日志还可以支持多个物化视图的快速刷新。物化视图日志根据不同物化视图的快速刷新的需要,可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。
包括 enable query rewrite 和 disable query rewrite 两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据。默认为 disable query rewrite。查询重写是一种发生在当一个查询或查询的一部分已经被保存为一个物化视图,转换器重写该查询以使用预先计算好的物化视图数据而不需要执行当前查询的转换。
/*+ REWRITE(MVIEW_NAME) */ 使用物化视图进行查询重写。
- SQL> select e.empno, e.ename, e.job
- 2 from emp e, dept d
- 3 where e.deptno = d.deptno
- 4 and d.dname = 'RESEARCH';
- EMPNO ENAME JOB
- ---------- ---------- ---------
- 7566 JONES MANAGER
- 7902 FORD ANALYST
- 7876 ADAMS CLERK
- 7369 SMITH CLERK
- 7788 SCOTT ANALYST
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 844388907
- ----------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ----------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5 | 170 | 6 (17)| 00:00:01 |
- | 1 | MERGE JOIN | | 5 | 170 | 6 (17)| 00:00:01 |
- |* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 2 (0)| 00:00:01 |
- | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
- |* 4 | SORT JOIN | | 14 | 294 | 4 (25)| 00:00:01 |
- | 5 | TABLE ACCESS FULL | EMP | 14 | 294 | 3 (0)| 00:00:01 |
- ----------------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("D"."DNAME"='RESEARCH')
- 4 - access("E"."DEPTNO"="D"."DEPTNO")
- filter("E"."DEPTNO"="D"."DEPTNO")
- SQL> set autotrace off;
- SQL> create materialized view sales_time_product_mv enable query rewrite as
- select e.empno, e.ename, e.job
- from emp e, dept d
- where e.deptno = d.deptno
- 5 and d.dname = 'RESEARCH';
- 实体化视图已创建。
- SQL> set autotrace on explain;
- SQL> select e.empno, e.ename, e.job
- 2 from emp e, dept d
- 3 where e.deptno = d.deptno
- 4 and d.dname = 'RESEARCH';
- EMPNO ENAME JOB
- ---------- ---------- ---------
- 7566 JONES MANAGER
- 7902 FORD ANALYST
- 7876 ADAMS CLERK
- 7369 SMITH CLERK
- 7788 SCOTT ANALYST
- 执行计划
- ----------------------------------------------------------
- Plan hash value: 663088863
- ------------------------------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- ------------------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 5 | 130 | 2 (0)| 00:00:01 |
- | 1 | MAT_VIEW REWRITE ACCESS FULL| SALES_TIME_PRODUCT_MV | 5 | 130 | 2 (0)| 00:00:01 |
- ------------------------------------------------------------------------------------------------------
- Note
- -----
- - dynamic sampling used for this statement (level=2)
我在物化视图中使用了一个rewrite提示来打开查询重写转换。
create materialized view view_name
refresh [ fast|complete|force ]
[
on [commit|demand ] |
start with (start_time) next (next_time)]AS subquery;
创建物化视图需要的权限:grant create materialized view to user_name;在源表建立物化视图日志:create materialized view log on test_tabletablespace test_space -- 日志空间with primary key; -- 指定为主键类型在目标数据库上创建MATERIALIZED VIEW:create materialized view mv_materialized_test refresh force on demand start with sysdate nextto_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') asselect * from user_info; --这个物化视图在每天10:25进行刷新修改刷新时间:alter materialized view mv_materialized_test refresh force on demand start with sysdatenext to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),' 23:00:00'),'dd-mm-yyyy hh24:mi:ss');或alter materialized view mv_materialized_test refresh force on demand start with sysdatenext trunc(sysdate,'dd')+1+1/24; -- 每天1点刷新
建立索引:create index IDX_MMT_IU_TESTon mv_materialized_test(ID,UNAME)tablespace test_space;
删除物化视图及日志:drop materialized view log on test_table; --删除物化视图日志:drop materialized view mv_materialized_test; --删除物化视图
***************************************************************************
1、on demand:使用DBMS_MVIEW包中的存储过程启用手工刷新(默认设置)
refresh [fast|complete|force] 视图刷新的方式:complete:全部刷新。相当于重新执行一次创建视图的查询语句。fast: 增量刷新.假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。create materialized view log on (主表名)。(多张表时,此语句也生效,创建后,原来的表中会多出两类视图表:MLOG$_table_name和RUPD$_table_name)force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。
2、on commit:在事务提交后刷新
使用情况
⑴仅用于快速刷新的物化视图
⑵需要on commit refresh对象权限
⑶如果刷新失败需要进行手工刷新
3、never:禁止物化视图刷新
在计划时间进行刷新:使用start with 和next选项。从指定的时间开始,每隔一段时间(由next指定)就刷新一次;
比如说我们要全刷新一张mv_test物化视图:begin
dbms_mview.refresh(TAB=>'MV_TEST',
METHOD=>'COMPLETE',
PARALLELISM=>8);
end;
/增量刷新就不需要使用什么并行了,通常情况下,是没有那个必要的。begin
dbms_mview.refresh(TAB=>'MV_TEST',
METHOD=>'FAST',
PARALLELISM=>1);
end;
/或者,也可以这样执行:exec dbms_mview.refresh('MV_TEST','F');create matherialized view emp_data
pctfree 5
tablespace example
storage (initial 50K next 50K)
refresh fast next sysdate + 7
as select ...;
create matherialized view emp_data
pctfree 5
tablespace example
using index storage (initial 25K next 25K)
refresh start with round(sysdate + 1) + 11/24
next next_day(trunc(sysdate),'MONDAY') + 15/24
as select * from sh.customers@remote union
select * from sh.customers@local;
***************************************************************************
**********************************************************************************************公司的项目中遇到需要从其它系统的数据库中取数据进行统计分析的问题,初步选择使用ORACLE的ODI工具进行抽数,但是对方提供的数据库用户下没有任何对象,只是有查询所有表的权限,因此无法做数据反向。于是决定使用物化视图,把对方数据库中的数据拿过来,虽然数据量比较大,但是每月只拿一次,而且如果设置成增量更新,也不会太慢。现在记录下物化视图的创建过程(以一张表为例)。
一、准备条件以及备注
假设双方数据库都是ORACLE10g,需要同步过来的表名叫:GG_ZLX_ZHU,对方数据库用户名:username,密码:password,SID:CPEES。
二、开始干活
1、首先要创建DB_LINK
CREATE DATABASE LINK to_cpees
CONNECT TO "username" identified by "password"
using "CPEES"
其中CPEES为新建的到对方数据库的TNS。执行,现在我们就已经创建了到对方数据库的DB_LINK TO_CPEES。
2、创建物化视图快速刷新日志
因为上面说过,以后视图的刷新将采用增量刷新的方式,因此,为配合增量刷新,ORACLE要求要在住表上建立物化视图日志。
CREATE MATERIALIZED VIEW LOG ON GG_ZLX_ZHU
WITH PRIMARY KEY
INCLUDING NEW VALUES;(上面的SQL要在远程数据库上执行,不能在本地执行)
3、创建物化视图
物化视图,从名字上面来开,它应该是属于视图,但是确实物化。其物化是针对普通视图并没有真正的物理存储而言,其实可以简单的把物化视图看做一个物理表(不再做具体解释)。
CREATE MATERIALIZED VIEW GG_ZLX_ZHU --创建物化视图
BUILD IMMEDIATE --在视图编写好后创建
REFRESH FAST WITH PRIMARY KEY --根据主表主键增量刷新(FAST,增量)
ON DEMAND -- 在用户需要时,由用户刷新
ENABLE QUERY REWRITE --可读写
AS
SELECT * FROM GG_ZLX_ZHU@TO_CPEES; --查询语句
4、视图刷新
根据业务需要,每月不定时刷新,所以不能是JOB,而且数量多,所以也不能一个一个刷新。根据以上条件,选择使用ORACLE自带工具DBMS_MVIEW工具包中REFRESH方法对物化视图进行刷新。该方法有两个参数,第一个参数是需要刷新的物化视图名称,第二个参数是刷新方式。我们可以写存储过来,对每个物化视图调用一次REFRESH方法,也可以使用“,”把物化视图连接以来,一次刷新。如下:
CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS
BEGIN
DBMS_MVIEW.REFRESH('GG_ZLX_ZHU','f');
END P_MVIEW_REFRESH;
或者使用
CREATE OR REPLACE PROCDURE P_MVIEW_REFRESH AS
BEGIN
DBMS_MVIEW.REFRESH('GG_ZLX_ZHU,GG_ZLX_FU','ff');
END P_MVIEW_REFRESH;
注意:
1、如果需要同时刷新多个物化视图,必须用逗号把各个物化视图名称连接起来,并对
每个视图都要指明刷新方式(f、增量刷新,c、完全刷新,?、强制刷新)。
2、当日志和物化视图创建好后,删除日志,则需要重新创建物化视图,否则无法增量
刷新。
3、因为上面写的物化视图时根据主键进行更新,因此,主表必须有主键。
4、以上文章中红色是为可替换的,大家可以根据自己项目需求来修改。
希望上面的内容对大家能有帮助。
忘了写删除方法了,日志和物化视图要分开删除
DROP MATERIALIZED VIEW LOG ON GG_ZLX_ZHU@TOCPEES;
DROP MATERIALIZED VIEW GG_ZLX_ZHU;
Oracle如何根据物化视图日志快速刷新物化视图
http://blog.itpub.net/4227/viewspace-68566/