物化视图的创建和快速刷新.docx
- 文档编号:28105166
- 上传时间:2023-07-08
- 格式:DOCX
- 页数:11
- 大小:123.03KB
物化视图的创建和快速刷新.docx
《物化视图的创建和快速刷新.docx》由会员分享,可在线阅读,更多相关《物化视图的创建和快速刷新.docx(11页珍藏版)》请在冰豆网上搜索。
物化视图的创建和快速刷新
物化视图的创建和快速刷新
1.物化视图简介
物化视图,它是用于预先计算并保存表连接或聚集等耗时较多的操作的结果,这样,在执行查询时,就可以避免进行这些耗时的操作,从而快速的得到结果。
物化视图对应用透明,增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性;物化视图需要占用存储空间;当基表发生变化时,物化视图也应当刷新。
其中物化视图有三种:
聚集物化视图、包含连接物化视图、嵌套物化视图。
但三种物化视图的快速刷新的限制条件有很大区别,而其他方面则区别不大。
2.物化视图的创建和参数说明
2.1物化视图实例
Ø南昌营运数据为5000万条左右,每日新增20w条左右。
根据报表的实际需求,在物化视图中按照车牌,车队,天汇总统计后的数据为80万条。
这样大大提高了查询的效率。
Ø创建物化视图的语句如下:
creatematerializedviewBUSINESS_DATA_CAR_MV
BUILDDEFERRED–在创建时不刷新,按照用户设定的时间刷新
refreshforce–如果可以快速刷新则进行快速刷新,否则进行完全刷新
ondemand–按照指定的方式刷新
startwithto_date('14-07-201113:
41:
16','dd-mm-yyyyhh24:
mi:
ss')--第一次刷新时间
nextTRUNC(SYSDATE+1)+2/24--刷新时间间隔
as
select
to_char(date_up,'yyyy-MM-dd')asday,
taxi_group,
taxi_company,
dispatch_car_no,
service_no,
sum(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,1,-1,0))ascardTime,
count(*)astimes,
sum(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM,-1,0))ascardSum,
sum(sum)assum,
sum(distance+free_distance)astotal_distance,
sum(decode(sign(distance),1,distance,-1,0,distance))asdistance,
sum(free_distance)asfree_distance,
sum(decode(sign(date_down-date_up),1,(date_down-date_up)*24,-1,0))asworkTime,
sum(waiting_hour*60+waiting_second)aswaiting_time,
sum(decode(to_char(date_up,'HH24'),'02',1,'03',1,'04',1,0))asoverTimeTimes,
fromSINGLE_BUSINESS_DATA_BSbus
wheredate_up<=date_downand(date_down-date_up)<0.5anddistance>=0anddistance<100andfree_distance<500
groupbyservice_no,dispatch_car_no,to_char(date_up,'yyyy-MM-dd'),taxi_group,taxi_company;,
2.2参数介绍
2.2.1BUILD
ØBUILDIMMEDIATE:
是在创建物化视图的时候就生成数据,。
ØBUILDDEFERRED:
则在创建时不生成数据,以后根据需要在生成数据。
Ø默认为BUILDIMMEDIATE。
2.2.2REFRESH
ØFAST:
增量刷新用物化视图日志,来发送主表已经修改的数据行到物化视图中。
ØCOMPLETE:
完全刷新重新生成整个视图,如果请求完全刷新,oracle会完成完全刷新即使增量刷新可用。
ØFORCE:
如果增量刷新可用Oracle将完成增量刷新,否则将完成完全刷新,如果不指定刷新方法(FAST,COMPLETE,orFORCE)。
Ø默认选项是Force。
2.2.3ON
ØONDEMAND:
指物化视图在用户需要的时候进行刷新。
ØONCOMMIT:
指出物化视图在对基表的DML操作提交的同时进行刷新。
Ø默认是ONDEMAND。
2.2.4STARTWITH
Ø通知数据库完成从主表到本地表第一次复制的时间。
2.2.5NEXT
Ø说明了刷新的间隔时间。
注:
根据下一次刷新的时间=上一次执行完成的时间+时间间隔。
为了保证在用户需要的时间点刷新,一般使用TRUNC()命令对时间取整到天数,然后加上时间。
如例子中的刷新是每天的凌晨2点开始执行物化视图的刷新。
3.物化视图的快速刷新
物化视图的快速刷新采用了增量的机制,在刷新时,只针对基表上发生变化的数据进行刷新。
因此快速刷新是物化视图刷新方式的首选。
但是快速刷新具有较多的约束,而且对于采用ONCOMMIT模式进行快速刷新的物化视图更是如此。
对于包含聚集和包含连接的物化视图的快速刷新机制并不相同,而且对于多层嵌套的物化视图的快速刷新更是有额外的要求。
3.1所有类型的快速刷新物化视图都必须满足的条件
Ø物化视图不能包含对不重复表达式的引用,如SYSDATE和ROWNUM;
Ø物化视图不能包含对LONG和LONGRAW数据类型的引用。
3.2只包含连接的物化视图的快速刷新条件
Ø必须满足所有快速刷新物化视图都满足的条件;
Ø不能包括GROUPBY语句或聚集操作;
Ø如果在WHERE语句中包含外连接,那么唯一约束必须存在于连接中内表的连接列上;
Ø如果不包含外连接,那么WHERE语句没有限制,如果包含外连接,那么WHERE语句中只能使用AND连接,并且只能使用“=”操作。
ØFROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。
ØFROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。
3.3包含聚集的物化视图的快速刷新条件
Ø必须满足所有快速刷新物化视图都满足的条件;
Ø物化视图查询的所有表必须建立物化视图日志,且物化视图日志必须满足下列限制:
a.包含物化视图查询语句中的所有列,包括SELECT列表中的列和WHERE语句中的列;
b.必须指明ROWID和INCLUDINGNEWVALUES;
c.如果对基本的操作同时包括INSERT、UPDATE和DELETE操作(即不是只包含INSERT操作),那么物化视图日志应该包括SEQUENCE。
Ø允许的聚集函数包括:
SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX;
Ø必须指定COUNT(*);
Ø如果指明了除COUNT之外的聚集函数,则COUNT(expr)也必须存在;
Ø比如:
包含SUM(a),则必须同时包含COUNT(a)。
Ø如果指明了VARIANCE(expr)或STDDEV(expr),除了COUNT(expr)外,SUM(expr)也必须指明;
ØOracle推荐同时包括SUM(expr*expr)。
ØSELECT列表中必须包括所有的GROUPBY列
Ø当物化视图属于下面的某种情况,则快速刷新只支持常规DML插入和直接装载,这种类型的物化视图又称为INSERT-ONLY物化视图:
a.物化视图包含MIN或MAX聚集函数;
b.物化视图包含SUM(expr),但是没有包括COUNT(expr);
c.物化视图没有包含COUNT(*)。
d.注意:
如果建立了这种物化视图且刷新机制是ONCOMMIT的,则会存在潜在的问题。
当出现了UPDATE或DELETE语句,除非手工完全刷新解决这个问题,否则物化视图至此以后都不再自动刷新,且不会报任何错误。
Ø如果包含inlineviews、outerjoins、selfjoins或groupingset,则兼容性的设置必须在9.0以上;
Ø如果物化视图建立在视图或子查询上,则要求视图必须可以完全合并的。
Ø如果没有外连接,则对WHERE语句没有限制。
如果包含外连接,则要求WHERE语句只能包括AND连接和“=”操作。
对于包含外连接的聚集物化视图,快速刷新支持outer表的修改。
且inter表的连接列上必须存在唯一约束。
Ø对于包含了ROLLUP、CUBE、GROUPINGSET的物化视图必须满足下列限制条件:
a.SELECT语句列表中应该包含GROUPING标识符:
可以是GROUPBY表达式中所有列的GROUPING_ID函数,也可以是GROUPBY表达式中每一列的GROUPING函数;
b.例如:
GROUPBY语句为:
GROUPBYCUBE(a,b),则SELECT列表应该包括GROUPING_ID(a,b)或者GROUPING(a)和GROUPING(b)。
c.GROUPBY不能产生重复的GROUPING。
d.比如:
GROUPBYa,ROLLUP(a,b)则不支持快速刷新,因为包含了重复的GROUPING:
(a),(a,b),(a)。
3.4包含UNIONALL的物化视图的快速刷新条件
ØUNIONALL操作必须在查询的顶层。
可以有一种情况例外:
UNIONALL在第二层,而第一层的查询语句为SELECT*FROM;
Ø被UNIONALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件;
ØSELECT列表中必须包含一列维护列,叫做UNIONALL标识符,每个UNIONALL分支的标识符列应包含不同的常量值;
Ø不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询;
Ø不支持基于分区改变跟踪(PCT)的刷新;
Ø兼容性设置应设置为9.2.0。
Ø嵌套物化视图的每层都必须满足快速刷新的限制条件;
Ø对于同时包含聚集和连接的嵌套物化视图,不支持ONCOMMIT的快速刷新。
4.快速刷新实例
在南昌使用物化视图的过程中发现,物化视图的刷新方式为全刷新。
当基表的数据量不断的上升,快速刷新的效率越来越慢。
由于物化视图刷新时,使用物化视图的报表无法进行查询,且物化视图刷新时也会对数据库造成压力和产生大量的日志,影响到系统的使用。
快速刷新如此多的限制一般很难记全,当建立物化视图失败时,Oracle给出的错误信息又过于简单,有时无法使你准确定位到问题的原因。
Oracle提供的DBMS_MVIEW.EXPLAIN_MVIEW过程可以帮助你快速定位问题的原因。
下面通过上面的例子来说明,如果通过这个过程来解决问题。
1.通过上图的sql语句看到2.1例子中的物化视图(BUSINESS_DATA_CAR_MV)执行的是complete及全刷新。
2.下面我们通过oracle提供的dbms_mview.explain_mview来帮助我们查找快速刷新的问题。
SQL>EXECdbms_mview.explain_mview('BUSINESS_DATA_CAR_MV','123');
SQL>SELECTcapability_name,possible,msgtxtFROMmv_capabilities_tableWHEREstatement_id='123'ORDERBYseq;
结果如下:
Oracle提示我们‘详细信息表没有实体视图日志’
3.修改物化视图
a.在基表中增加日志
SQL>CREATEMATERIALIZEDVIEWLOGONSINGLE_BUSINESS_DATA_BS
WITHSEQUENCE,ROWID(taxi_group,taxi_company,dispatch_car_no,service_no,OYSTER_CARD_ORIGIN_SUM,OYSTER_CARD_LEFT_SUM,sum,distance,free_distance,date_down,date_up,waiting_hour,waiting_second)INCLUDINGNEWVALUES;
b.然后在基表中新增一条记录。
c.执行一次快速刷新
SQL>execdbms_mview.refresh('BUSINESS_DATA_CAR_MV','F');
结果如下:
从结果中可以看到此时该物化视图执行快速刷新。
4.我们再执行oracle的物化视图分析工具
图中可以看到在基表做insert操作时,物化视图时可以做快速刷新了。
由于计价器数据只是新增,所以就满足了该报表的使用。
5.根据提示对DML情况下快速刷新的条件的修改
creatematerializedviewBUSINESS_DATA_CAR_MV
refreshforceondemand
startwithto_date('14-07-201113:
41:
16','dd-mm-yyyyhh24:
mi:
ss')nextSYSDATE+1/1440
as
select
to_char(date_up,'yyyy-MM-dd')asday,
taxi_group,
taxi_company,
dispatch_car_no,
service_no,
sum(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,1,-1,0))ascardTime,
count(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,1,-1,0))asccardTime,
count(*)astimes,
sum(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM,-1,0))ascardSum,
count(decode(sign(OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM),1,OYSTER_CARD_ORIGIN_SUM-OYSTER_CARD_LEFT_SUM,-1,0))asccardSum,
sum(sum)assum,
count(sum)ascsum,
sum(distance+free_distance)astotal_distance,
count(distance+free_distance)asctotal_distance,
sum(decode(sign(distance),1,distance,-1,0,distance))asdistance,count(decode(sign(distance),1,distance,-1,0,distance))ascdistance,
sum(free_distance)asfree_distance,
count(free_distance)ascfree_distance,sum(decode(sign(date_down-date_up),1,(date_down-date_up)*24,-1,0))asworkTime,count(decode(sign(date_down-date_up),1,(date_down-date_up)*24,-1,0))ascworkTime,
--sum((date_down-date_up)*24)asworkTime,
sum(waiting_hour*60+waiting_second)aswaiting_time,
count(waiting_hour*60+waiting_second)ascwaiting_time,
sum(decode(to_char(date_up,'HH24'),'02',1,'03',1,'04',1,0))asoverTimeTimes,count(decode(to_char(date_up,'HH24'),'02',1,'03',1,'04',1,0))ascoverTimeTimes
fromSINGLE_BUSINESS_DATA_BSbus
wheredate_up<=date_downand(date_down-date_up)<0.5anddistance>=0anddistance<100andfree_distance<500
groupbyservice_no,dispatch_car_no,to_char(date_up,'yyyy-MM-dd'),taxi_group,taxi_company;
再执行下oracle物化视图分析工具:
图中可以看到DML操作可以做快速刷新了,以前做过测试的,这里就不做测试了。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 物化 视图 创建 快速 刷新