查看: 1711|回复: 4
【SQL性能优化】物化视图和普通视图哪个更快？为什么更快？
认证徽章论坛徽章:3
& &今天接触了个表，数据上亿，要对比两个表中的5个字段，找出差异数据。
&&刚开始用了视图来搞定，结果发现一查询就是半小时没了，后来建了物化视图，查询基本上在10分钟以内能完成，速度快了n倍，请问下小伙伴们，这是咋回事儿？
&&在什么情况下普通视图查询更快，什么情况下物化视图查询更快呢？
认证徽章论坛徽章:191
mv 占了你的存储上的地儿了，空间换的时间。
什么时候 view 比 mv 快。。。。 这个，一台小机上的 view 应该 一台 386 上的 mv 要快，没试过。
认证徽章论坛徽章:40
个人理解：
物化视图相当于把你查询的结果存成一个中间表，对查询结果的读取和处理，是直接在这个中间表上进行的。当组成查询的逻辑关系较复杂时（比如多表关联，查出来的的数据还要经过转换等），那么用物化视图就会比较快。但如果组成查询的逻辑关系很简单（比如只是显示一个表中的部分字段），那么速度上的差异将很小。
认证徽章论坛徽章:3
bfc99 发表于
个人理解：
物化视图相当于把你查询的结果存成一个中间表，对查询结果的读取和处理，是直接在这个中间表上 ...
超赞，非常感谢，应该是这样的，我的表空间文件没其他数据存放就放了一个物化视图，发现直接打到29.2g,查询的话有索引，相对比较快
认证徽章论坛徽章:3
wmxcn2000 发表于
mv 占了你的存储上的地儿了，空间换的时间。
什么时候 view 比 mv 快。。。。 这个，一台小机上的 view&&...
嗯嗯，完全是拿空间换时间，创物化视图要半小时多一点，普通视图1分钟不到就好了
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号1281人阅读
oracle（17）
临时表跟永久表最大的区别就是表中的数据不会永远的存在
Oracle临时表分为会话级临时表和事务级临时表。
会话临时表，结束或中断会话时清空数据。
create global temporary table XXX()
on commit preserve rows;
事务临时表，commit之后清空数据。
on commit delete rows;
临时表不会为它们的块生成redo。因此，对临时表的操作不是“可恢复的” 。修改临时表中的一个块时，不会将这个修改记录到重做日志文件中。不过，临时表确实会生成 undo，而且这个 undo 会计入日志。因此，临时表也会生成一些redo。
这是因为你能回滚到事务中的一个 SAVEPOINT。由于undo数据必须建立日志，因此临时表会为所生成的undo生成一些重做日志。这样似乎很糟糕。但是，在临时表上运行的 SQL 语句主要是 INSERT 和SELECT。幸运的是，INSERT 只生成极少的 undo（需要把块恢复为插入前的“没有”状态，而存储“没有”不需要多少空间），另外SELECT根本不生成undo。
Oracle的临时表还保证了多用户操作的独立性：对于使用同一张临时表的不同用户，ORACLE都会分配一个独立的Temp Segment，这样就避免了多个用户在对同一张临时表操作时发生交叉，从而保证了多个用户操作的并发性和独立性；
3) 应用优化
当多表关联，且存在小表时。可以采用将大表关联得到比较小的结果集合存放在临时表中，再用临时表去关联小表。
如果某个数据集在这个会话期间需多次使用，建议使用临时表。
2. 物化视图
视图是一个虚拟表（也可以认为是一条语句），基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。
物化视图的类型：ON DEMAND、ON COMMIT
根据需要（ON DEMAND）：物化视图会在显式请求的情况下进行刷新（可以通过手工调用，也可以通过运行按照指定的时间间隔的任务)。这意味着从基础表修改到物化视图刷新这段时间内，物化视图中可能包含失效的数据。
在提交时（ON COMMIT)：物化视图会在基础表修改所在的同一个事务里进行自动刷新，也就是说，物化视图总是包含最新的数据；（这种方式比较少用）
完全刷新（COMPLETE）
会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），然后根据物化视图中查询语句的定义重新生成物化视图。
快速刷新（FAST）
采用增量刷新的机制，只将自上次刷新以后对基表进行的所有操作刷新到物化视图中去。
这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件，如果满足则进行快速刷新，否则进行完全刷新。
create materialized view view_name
refresh [fast|complete|force]
on [commit|demand] |
start with (start_time) next (next_time)
创建MATERIALIZED VIEW：
create materialized view mv_materialized_test refresh force on demand start with sysdate next
to_date(concat(to_char(sysdate+1,'dd-mm-yyyy'),'10:25:00'),'dd-mm-yyyy hh24:mi:ss') as
select * from user_
修改刷新时间：
alter materialized view mv_materialized_test refresh force on demand start with sysdate
next 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 sysdate
next trunc(sysdate,'dd')+1+1/24;
&&相关文章推荐
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：385895次
积分：4545
积分：4545
排名：第5816名
原创：109篇
转载：33篇
译文：17篇
评论：58条
(1)(1)(1)(14)(7)(5)(1)(7)(4)(10)(9)(3)(2)(2)(2)(7)(4)(3)(1)(30)(5)(9)(28)(2)(1)扫二维码下载作业帮
1.75亿学生的选择
下载作业帮安装包
扫二维码下载作业帮
1.75亿学生的选择
物化视图与一般视图有何区别
扫二维码下载作业帮
1.75亿学生的选择
物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的.普通视图是虚拟表,应用的局限性大,任何对视图的查询,Oracle都实际上转换为视图SQL语句的查询.这样对整体查询性能的提高,并没有实质上的好处.
1、物化视图的类型：ON DEMAND、ON COMMIT
二者的区别在于刷新方法的不同,ON DEMAND顾名思义,仅在该物化视图“需要”被刷新了,才进行刷新(REFRESH),即更新物化视图,以保证和基表数据的一致性；而ON COMMIT是说,一旦基表有了COMMIT,即事务提交,则立刻刷新,立刻更新物化视图,使得数据和基表一致.
2、ON DEMAND物化视图
物化视图的创建本身是很复杂和需要优化参数设置的,特别是针对大型生产数据库系统而言.但Oracle允许以这种最简单的,类似于普通视图的方式来做,所以不可避免的会涉及到默认值问题.也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的.
物化视图的特点：
(1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表),这通过其可以被user_tables查询出来,而得到佐证；
(2) 物化视图也是一种段(segment),所以其有自己的物理存储属性；
(3) 物化视图会占用数据库磁盘空间,这点从user_segment的查询结果,可以得到佐证；
创建语句：create materialized view mv_name as select * from table_name
默认情况下,如果没指定刷新方法和刷新模式,则Oracle默认为FORCE和DEMAND.
物化视图的数据怎么随着基表而更新?
Oracle提供了两种方式,手工刷新和自动刷新,默认为手工刷新.也就是说,通过我们手工的执行某个Oracle提供的系统级存储过程或包,来保证物化视图与基表数据一致性.这是最基本的刷新办法了.自动刷新,其实也就是Oracle会建立一个job,通过这个job来调用相同的存储过程或包,加以实现.
ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别,即前者不刷新(手工或自动)就不更新物化视图,而后者不刷新也会更新物化视图,——只要基表发生了COMMIT.
创建定时刷新的物化视图：create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 （指定物化视图每天刷新一次）
上述创建的物化视图每天刷新,但是没有指定刷新时间,如果要指定刷新时间（比如每天晚上10：00定时刷新一次）：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物化视图,需要再增加个参数即可.
为您推荐：
其他类似问题
扫描下载二维码物化视图的优化模型研究及其应用--《大连理工大学》2014年硕士论文
物化视图的优化模型研究及其应用
【摘要】：随着信息技术和网络的发展,现代社会中的数据能够较好地被记录和存储,然而也产生了海量的数据,换句话说,我们每天被数据——科学数据、医疗数据、人口统计数据、金融数据和销售数据——所淹没,这些存储数据或瞬态数据的爆炸性增长已经激发了对信息管理的更高的要求。近年来,企业对于信息的重视程度越来越高,对于信息的管理和加工也有着越来越迫切的需求,亟需从现有的业务数据中挖掘出有用的信息和知识。然而,对于海量数据的加工往往需要消耗较多的时间,查询统计效率低下,为下一步的数据挖掘造成了很大的阻碍,因此如何提高查询统计的效率,实现查询的快速响应是一个亟需解决的问题。
物化视图作为一种查询优化的工具,已经成为一个研究热点。本文综合查询性能、维护成本和空间成本提出了一种物化视图的优化算法。首先从目标查询入手,经过查询预处理和聚集操作分解等过程找到查询特征集合；然后对查询特征进行谱系划分,构造谱系聚集图,并在每个谱系中确定一个最优的候选物化视图；最后按照物化视图的单位空间收益对其进行排序,根据存储空间的限制对物化视图进行选择,进而确定能够最大程度提高查询的响应速度的最终的物化视图集。
以某医院的数据库作为背景,通过对大量医疗数据进行查询统计,说明了查询优化的必要性。将该模型应用于医疗服务数据的分析中,证明了该模型具有良好的优化性能,能够大幅度提高系统的查询效率,同时具有实际的应用价值,对医院物流系统的规划以及医疗服务的改进具有一定的贡献。
【关键词】：
【学位授予单位】：大连理工大学【学位级别】：硕士【学位授予年份】：2014【分类号】：TP311.13【目录】：
摘要4-5Abstract5-81 绪论8-12 1.1 研究背景与研究意义8-9 1.2 论文研究内容9-10 1.3 论文结构与技术路线10-122 相关研究综述12-20 2.1 物化视图概述12 2.2 物化视图的选择方法概述12-18
2.2.1 视图关系模型13-15
2.2.2 现有算法研究15-18 2.3 现有研究总结18-203 物化视图的优化模型20-26 3.1 问题描述20 3.2 基本概念20-23
3.2.1 维、层级和粒度20-21
3.2.2 数据表和数据结构模型21-23
3.2.3 聚集函数与聚集操作23 3.3 变量定义23-25 3.4 优化模型25 3.5 本章小结25-264 物化视图的优化算法设计26-46 4.1 查询预处理26-33
4.1.1 查询的相关描述26-28
4.1.2 查询特征分析28-33 4.2 谱系架构设计33-37
4.2.1 谱系33
4.2.2 谱系聚集图33-36
4.2.3 谱系评价36-37 4.3 基于PAG的物化视图的选择算法37-44
4.3.1 仅考虑查询性能的算法37-39
4.3.2 考虑查询性能与维护成本的算法39-40
4.3.3 考虑查询性能与维护成本以及空间约束的算法40-44 4.4 优化流程44-45 4.5 本章小结45-465 应用算例及结果分析46-58 5.1 案例背景46 5.2 查询优化的必要性46-47 5.3 物化视图的确定47-51
5.3.1 查询预处理47-50
5.3.2 物化视图的选择50-51 5.4 应用效果对比51-53 5.5 数据结果分析53-57
5.5.1 数据结果在医院资源配置优化中的应用53-55
5.5.2 数据结果在医院设施布置规划中的应用55-57 5.6 本章小结57-58结论58-60参考文献60-63致谢63-64
欢迎：、、)
支持CAJ、PDF文件格式
【参考文献】
中国期刊全文数据库
崔晓军;薛永生;张东站;黄宗毅;;[J];计算机科学;2006年02期
张晓辉,袁愿,虞健飞,张恒喜;[J];计算机应用;2003年07期
夏小玲;张红;;[J];计算机科学与探索;2010年05期
谭红星,周龙骧;[J];软件学报;2002年06期
林小静;薛永生;;[J];计算机工程与设计;2007年13期
吕晓;陈耿;朱玉全;;[J];计算机工程与设计;2009年15期
黄立炜;;[J];数字技术与应用;2012年09期
彭成;佟秋利;;[J];计算机工程与设计;2013年01期
【共引文献】
中国期刊全文数据库
乔宝进;康迎华;高贞彦;刘东波;;[J];电工技术;2008年08期
裴丽鹊;吕晓华;;[J];闽江学院学报;2008年05期
姜云飞;;[J];长春大学学报;2014年02期
王瑞;费树岷;;[J];电脑知识与技术;2014年05期
吴建源;;[J];广东培正学院学报;2014年01期
周霞;王华军;;[J];电脑与电信;2014年Z1期
李静滨;杨柳;陈宁江;;[J];广西大学学报(自然科学版);2014年02期
夏春艳;崔广才;蔡春华;丁蕊;;[J];中国农机化学报;2014年03期
黄书城;钟家栋;亓文娟;;[J];安阳工学院学报;2014年04期
艾贝贝;赵雯华;万幼;;[J];地理信息世界;2014年04期
中国重要会议论文全文数据库
;[A];第四届全国信息检索与内容安全学术会议论文集（下）[C];2008年
王元珍;钱铁云;;[A];第二十届全国数据库学术会议论文集（研究报告篇）[C];2003年
戴文海;邵琦洪;陈红;;[A];第二十一届中国数据库学术会议论文集（技术报告篇）[C];2004年
张伟;薛永生;匡天祺;林子雨;;[A];第二十一届中国数据库学术会议论文集（研究报告篇）[C];2004年
张柏礼;孙志挥;孙翔;;[A];第二十一届中国数据库学术会议论文集（研究报告篇）[C];2004年
薛永生;林子雨;段江娇;吕晓华;张伟;;[A];第二十一届中国数据库学术会议论文集（研究报告篇）[C];2004年
卢艳民;焦有章;陈红;;[A];第二十二届中国数据库学术会议论文集（研究报告篇）[C];2005年
黄宗毅;薛永生;文娟;蔡劲;翁伟;;[A];第二十二届中国数据库学术会议论文集（研究报告篇）[C];2005年
钟静华;冯少荣;段江娇;林子雨;薛永生;;[A];第二十二届中国数据库学术会议论文集（技术报告篇）[C];2005年
任家东;李志国;宗俊省;;[A];第二十三届中国数据库学术会议论文集（研究报告篇）[C];2006年
中国博士学位论文全文数据库
甘亮;[D];国防科学技术大学;2011年
师智斌;[D];北京交通大学;2010年
梁银;[D];中国矿业大学;2009年
段成;[D];华北电力大学;2012年
高雅卓;[D];合肥工业大学;2012年
杨欢;[D];西安电子科技大学;2014年
曾强;[D];燕山大学;2014年
郝连旺;[D];燕山大学;2014年
陈佳;[D];武汉大学;2010年
王云升;[D];东北大学;2011年
中国硕士学位论文全文数据库
张宇航;[D];辽宁工程技术大学;2010年
甄晓敏;[D];河北工业大学;2011年
张倩;[D];华中科技大学;2011年
骆伟忠;[D];中南大学;2004年
李睿;[D];中南大学;2004年
毕然;[D];东南大学;2004年
白崇毅;[D];哈尔滨理工大学;2005年
王云峰;[D];中南大学;2005年
张娟;[D];南昌大学;2006年
李凌燕;[D];东南大学;2006年
【二级参考文献】
中国期刊全文数据库
魏新军;;[J];中国储运;2007年01期
祁文文,徐彬,谭红星;[J];河南大学学报(自然科学版);2001年01期
张东站;黄宗毅;薛永生;;[J];计算机研究与发展;2008年05期
杜明义,吉寿松,郭达志;[J];计算机工程与应用;2000年11期
周丽娟,刘大昕,柳池;[J];计算机工程与应用;2003年34期
冯少荣;肖文俊;;[J];计算机工程;2007年23期
李才华;;[J];计算技术与自动化;2009年04期
张晓辉,袁愿,虞健飞,张恒喜;[J];计算机应用;2003年07期
陈启买;贺超波;刘海;;[J];计算机应用;2009年01期
姜楠;高巍;张丽秋;;[J];机械设计与制造;2007年04期
中国博士学位论文全文数据库
李泽海;[D];吉林大学;2005年
【相似文献】
中国期刊全文数据库
孙科芬;顾伟;金辉;倪志华;王莉杨;况华;;[J];软件产业与工程;2011年05期
;[J];;年期
;[J];;年期
;[J];;年期
;[J];;年期
;[J];;年期
;[J];;年期
;[J];;年期
;[J];;年期
;[J];;年期
中国重要会议论文全文数据库
商弘;廉维;田江晖;;[A];中华医学会医学工程学分会第十次学术年会暨2009中华临床医学工程及数字医学大会论文集[C];2009年
杜显峰;;[A];中华医学会医学工程学分会第十次学术年会暨2009中华临床医学工程及数字医学大会论文集[C];2009年
中国重要报纸全文数据库
张凌辉?南京医药苏北物流管理中心副总监;[N];医药经济报;2007年
九州通集团业务总裁
耿鸿武;[N];医药经济报;2010年
张凌辉;[N];医药经济报;2007年
张凌辉;[N];医药经济报;2007年
刘景峰;[N];医药经济报;2009年
王文良;[N];现代物流报;2005年
中国硕士学位论文全文数据库
张泽月;[D];北京交通大学;2008年
韩嘉;[D];西南交通大学;2014年
赵阳;[D];天津大学;2007年
王嘉萌;[D];大连理工大学;2014年
&快捷付款方式
&订购知网充值卡
400-819-9993
《中国学术期刊（光盘版）》电子杂志社有限公司
同方知网数字出版技术股份有限公司
地址：北京清华大学 84-48信箱 大众知识服务
出版物经营许可证 新出发京批字第直0595号
订购热线：400-819-82499
服务热线：010--
在线咨询：
传真：010-
京公网安备75号