浅析利用Oracle物化视图对查询优化

陶孝海

摘 要：随着大数据、物联网、云计算等概念的兴起， 关于数据的分析应用逐步从后台走向了前台， 生活中各类的软件开发、终端应用都离不开背后支撑整个环境的数据本身， 动辄上亿、甚至百千亿级别的数据亦不罕见， 面对如此庞大的数据量， 效率“二字”也被提升到了一个空前的高度上。Oracle作为目前使用最为广泛应用的关系数据库， 如何对其做性能优化， 提升工作效率， 去促成更强的决策能力、洞察力与最优化处理， 成为信息产业所有人聚焦的核心。本文从Oracle物化视图入手， 结合日常工作， 指出了基于Oracle物化视图性能优化方式。

关键词： Oracle; 物化视图; 优化

文章编号： 2095-2163（2019）03-0309-03 中图分类号： TP309.3 文献标志码： A

0 引 言

荆州市于2013年实现8个县市区（含市本级）社会保险数据库市级大集中，日常工作中涉及大量的数据查询及统计分析工作。在查询操作非常频繁的情况下， 数据库整体运行性能会受到严重影响。通过在本地创建物化视图，在实际工作中可以大大提高数据库查询效率。

物化视图（Materialized View）在9i以前的版本叫做快照（SNAPSHOT），从9i开始改名叫做物化视图。在设计上，是用于预先计算并保存表连接或聚集等耗时较多的操作的结果，如此一来，在执行查询时，就可以避免频繁调取这些耗时的操作，从而快速得到查询结果。

1 物化视图的特点与优势

和表一样，物化视图是数据库中的一种存储数据的对象。用于预先计算并保存表连接或者聚集等耗时较多的操作。这样，在执行查询的时候，就可以避开连接、聚集等耗时的操作，从而快速地得到结果。在使用时，可以查询表、视图、甚至是其他物化视图中的数据。物化视图有很多方面和索引很相似，诸如：使用物化视图的目的是为了提高查询性能;物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL 语句的正确性和有效性;物化视图需要占用存储空间;当基表發生变化时，物化视图也应当刷新。

简单说，物化视图不仅存储了SQL的定义，还存储了数据;此外，也是远程数据的本地副本，或者用来生成基于数据表求和的汇总表。

1.1 物化视图与普通视图的区别

Oracle物化视图与普通视图概念相似， 但是具有本质上的区别。现对其探讨表述如下。

（1）普通视图 （View） ， 也称为虚表，不存储任何数据，也不占用任何物理存储空间，且只有定义，在查询中是转换为对应的定义SQL去查询，视图本身的定义语句存储在数据字典里。而物化视图是将数据转换为一个表，实际存储着数据，也会占用数据库磁盘空间。故而在查询数据时，就无需用到大量表格，而且如果表很大的话，还会在临时表空间内展开大量的操作。

（2）普通视图可以简化设计，清晰编码，但其存在却只会降低性能。在每次使用时，视图都需要重新执行SQL， 这种操作的优点是总能获得最新的数据， 缺点是其运行性能依赖于视图所使用的查询语句优劣，此时如果视图所使用的SELECT语句连接了许多张表， 或者使用了基于非索引的连接， 则视图执行的性能就会比较差。物化视图则根据查询的需要周期性地更新数据。物化视图的优势是， 查询物化视图本质上是查询一张表， 而且这张表可以被索引。物化视图更新时，所有的连接都被完成， 这就省去了在每次使用SELECT语句时均需进行连接的较为耗时操作，从而可快速地得到结果。

1.2 物化视图的特点

研究可知，物化视图有3个特点，这里可做阐释分述如下。

（1） 在某种意义上说，物化视图就是一个物理表，而且不仅仅是一个物理表，这可通过其能被user_tables查询出来而得到确认。

（2） 物化视图也是一种段（segment），因此具有自己的物理存储属性。

（3） 物化视图会占用数据库磁盘空间。这点从user_segment的查询结果中，可以得到佐证;物化视图由于是物理真实存在的，故可以创建索引。创建语句为：

create materialized view mv_name as  select * from table_name

1.3 物化视图的优势

物化视图最大的优势是可以提高性能，通过预先计算好答案存储起来，从而大大地降低机器的负载，研究将分为4个方面对其加以剖析，详见如下。

（1）更少的物理读，扫描更少的数据。物化视图可以基于列级和行级子集复制数据， 从而复本仅是与特定需求有关的信息。

（2）减轻网络负载。通过使用多层架构的物化视图， 可以创建基于其他物化视图的物化视图， 进一步分散用户负载，将用户负载分散于多个数据库服务器，用户可以访问物化视图站点、而非主站点。

（3）更少的写，减少CPU的消耗。使用物化视图不用经常排序和聚集，不用对数据进行聚集计算和函数调用，当物化视图创建为主表或主物化视图的子集时， 还可以减少复制的数据量。

（4）显著地加快响应时间。在使用物化视图查询数据时（与主表相比），将会很快地返回查询结果。

2 物化视图的创建和使用

作为远程数据的的本地副本，或者用来生成基于数据表求和的汇总表，物化视图往往需要跨库访问数据库，远程的数据库可以通过同步更新本地的物化视图， 保证数据的一致性，如图1所示。

2.1 物化视图的创建

研究中，以创建个人账户刷卡消费金额统计为例，创建设计实例如图2所示。

物化视图有2种刷新模式，分别是：on demand 和 on commit。其中，on demand 顾名思义，仅在该物化视图需要被刷新时，才进行刷新（REFRESH），即更新物化视图，以保证和基表数据的一致性;on commit就是提交触发，一旦基表有了commit，即事务提交，则即时刷新，即时更新物化视图，使得数据和基表始终一致。一般情况下，使用这种方法在操作基表时，速度会比较慢。创建物化视图时未作指定，Oracle的默认定义即为 on demand 模式。

在此基础上，关于如何刷新，则有3种刷新方法。对各种方法可给出研究论述如下。

（1）完全刷新（COMPLETE）： 会删除表中所有的记录（如果是单表刷新，可能会采用TRUNCATE的方式），此后将根据物化视图中查询语句的定义重新生成物化视图。

（2）快速刷新（FAST）： 采用增量刷新的机制，只将自上次刷新后对基表进行的所有操作刷新到物化视图中去。FAST必须创建基于主表的视图日志。对于增量刷新选项，如果在子查询中存在分析函数，则物化视图不起作用。

（3）FORCE方式：这是默认的数据刷新方式。Oracle会自动判断是否满足快速刷新的条件。如果满足，将进行快速刷新，否则进行完全刷新。

特别地，Oracle物化视图的快速刷新机制是通过物化视图日志实现的。通过一个物化视图日志，Oracle还可以支持多个物化视图的快速刷新。根据不同物化视图的快速刷新的需要，物化视图日志可以建立为ROWID或PRIMARY KEY类型。此外，还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。

2.2 物化视图的应用

在建立的物化视图上， 用户可以进一步创建视图， 例如采用以下SQL语句创建视图vw_2017年个人账户刷卡：

create view vw_2017年個人账户刷卡 as select* from mv_sum_grzhsk where 年度=' 2017';

在物化视图基础上建立普通视图查询统计2017年个人账户刷卡用时0.024 s，如图3所示;普通视图查询统计用时221.894s，如图4所示。普通视图查询执行的本质内容与前述物化视图vw_2017年个人账户刷卡上的查询相同， 因此查询结果也与图3相同。两者对比可以看出物化视图查询统计，比普通视图速度快了许多。

3 结束语

综上所述， 本文研究了使用Oracle物理视图优化查询， 使查询执行时间得到了明显的降低， 数据库性能得到大幅提升， 证实了优化方法的有效性， 为数据库管理与应用工作提供了指引。数据库的优化是一个复杂的过程，本文关注的仅仅是应用层的一些表现， 数据库的运维还涉及底层的资源分配、网络层的流量控制和操作系统的构架， 甚至相同平台的不同类型数据， 其优化方式的原则都要随之改变， 而不是教条式的简单修改， 作为使用者而言， 应该勤加探索， 借助优化软件、跟踪分析， 加以人工辨别， 找到一个适合自己的行之有效优化方式。

参考文献

[1]魏玉芬， 王玥. 基于ORACLE成本优化器的SQL查询优化分析与应用[J]. 内蒙古农业大学学报 （自然科学版），2018， 39 （2）： 88-93.

[2] 童奕媛， 杨林. Oracle数据库性能优化实践应用分析—以某城市商业银行财务系统为例[J]. 金融科技时代， 2017 （1）： 31-35.

[3] 李伟，安永丽，胡雄. Oracle 11g SQL和PL/SQL编程指南[M]. 北京：清华大学出版社， 2014.

[4] 陶春江. 基于ORACLE数据库的SQL优化研究[J]. 数字技术与应用， 2016 （12）： 98.