物化视图概述:
物化视图语法:
create materialized view materialized_view_name
build [immediate|deferred]
refresh [complete|fast|force|never]
on [commit|demand]
start with (start_date)
next (interval_date)
with [primary key|rowid]
ENABLE QUERY REWRITE
as
select statement;
1.创建方式默认是immediate,立即;deferred,延迟,至第一次refresh时,才生效。
2.物化视图刷新方式默认为force,意思是:如果可以快速刷新,就执行快速刷新;否则,执行完全刷新。
3.on commit,基表有commit动作时,刷新视图,不能跨库执行(因为不知道别的库的提交动作);on demand,在需要时刷新,即,根据后面设定的起始时间和时间间隔进行刷新,或者,手动调用dbms_mview包中的过程刷新时再执行刷新。
4和5.即开始刷新时间和下次刷新的时间间隔。如:start with sysdate next sysdate+1/1440表示马上开始,刷新间隔为1分钟。(与 on commit选项冲突)
6.基于基表的主键或者rowid创建。如果是基于rowid,不能对基表执行分组函数、多表连结等需要把多个rowid合成一行的操作(理由很简单:到底以哪个rowid为准呢?)。
7.如果设置了初始化参数query_rewrite_enabled=true则默认就会启用查询重写。但是,数据库默认该参数为false。并且,不是什么时候都应该启用查询重写。所以,该参数应该设置为false,而在创建特定物化视图时,根据需要开启该功能。另外,如果选择使用了上面第4‘5选项,则不支持查询重写功能(原因很简单,所谓重写,就是将对基表的查询定位到了物化视图上,而4、5选项会造成物化视图上部分数据延迟,所以,不能重写)。
创建物化试图主要选项说明
创建物化视图时可以指定多种选项,下面对几种主要的选项进行简单说明:
名称 | Build Clause | 创建方式 |
描述 | 包括BUILD IMMEDIATE和BUILD DEFERRED两种 | |
取值 | BUILD IMMEDIATE | 在创建物化视图的时候就生成数据 |
BUILD DEFERRED | 在创建时不生成数据,以后根据需要在生成数据 | |
默认 | BUILD IMMEDIATE |
名称 | Refresh | 刷新子句 |
描述 | 当基表发生了DML操作后,物化视图何时采用哪种方式和基表进行同步 | |
语法 | [refresh [fast | complete | force] ] | |
简介 | FAST | 采用增量刷新,只刷新自上次刷新以后进行的修改;对于增量刷新选项,如果在子查询中存在分析函数,则物化视图不起作用。 |
COMPLETE | 对整个物化视图进行完全的刷新即完全刷新重新生成整个视图 | |
FORCE(默认) | Oracle在刷新时会去判断是否可以进行快速刷新,如果可以则采用Fast方式,否则采用Complete的方式,Force选项是默认选项 | |
| ||
ON DEMAND(默认) | 物化视图在用户需要的时候进行刷新,可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新,也可以通过JOB定时进行刷新 | |
ON COMMIT | 物化视图在对基表的DML操作提交的同时进行刷新 | |
| ||
START WITH | 通知数据库完成从主表到本地表第一次复制的时间,应该及时估计下一次运行的时间点,因此它是和next成组出现 | |
NEXT | 刷新时间间隔 | |
| ||
WITH PRIMARY KEY(默认) | 生成主键物化视图,也就是说物化视图是基于表的主键,而不是ROWID(对应于ROWID子句)。为了生成PRIMARY KEY子句,应该在表上定义主键,否则应该用基于ROWID的物化视图。主键物化视图允许识别物化视图表而不影响物化视图增量刷新的可用性 | |
WITH ROWID | 只有一个单一的主表,不能包括下面任何一项: |
名称 | Query Rewrite | 查询重写 |
描述 | 包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,而直接从已经计算好的物化视图中读取数据 | |
简介 | ENABLE QUERY REWRITE | 支持查询重写 |
DISABLE QUERY REWRITE | 不支持查询重写 | |
默认 | DISABLE QUERY REWRITE |
创建实体化视图日志主要选项说明 :
名称 | WITH Clause | |
描述 | | |
取值 | OBJECT ID | 如果是对象物化视图(object materialized view),则只能采用该方式 |
PRIMARY KEY | | |
ROWID | | |
SEQUENCE | | |
默认 | |
实体举例 :
创建增量刷新的物化视图时应先创建存储的日志空间:
--在hs_his.hisfuassettot表上创建物化视图日志 |
然后创建物化视图:
方法一: create materialized view hs_his.hisfuassettot_mv 方法二: create materialized view hs_his.hisfuassettot_mv |
删除物化视图日志:
|
删除物化视图:
|
--------------物化视图与普通视图区别:占用磁盘空间,需要自动刷新或者手动刷新的,对物化视图的更新不会更新到基表上
先看一个网上的例子:
--创建物化视图时应先创建存储的日志空间
CREATE MATERIALIZED VIEW LOG ON mv_lvy_levytaxbgtdiv
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydetaildata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH ROWID,sequence(LEVYDETAILDATAID);
CREATE MATERIALIZED VIEW LOG ON tb_lvy_levydata
tablespace ZGMV_DATA --日志保存在特定的表空间
WITH rowid,sequence(LEVYDATAID);
然后创建物化视图:
--创建物化视图
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'