ORACLE中SQL查询优化研究
摘 要 数据库性能问题一直是决策者及技术人员共同关注的焦点，影响数据库性能的一个重要因素就是SQL查询语句的低效率。论文首先分析了导致SQL查询语句性能低下的四个常见原因以及SQL调优的一般步骤，然后分别针对如何降低I/O操作、在查询语句中如何避免对查询结果的高成本操作以及在多表连接时如何提高查询效率进行了分析。
关键词 ORACLE；SQL；优化；连接
1 引言
随着网络应用不断发展，系统性能已越来越引起决策者的重视。影响系统性能的因素很多，低效的SQL语句就是其中一个不可忽视的重要原因。论文首先分析导致SQL性能低下的常见原因，然后分析SQL调优应遵循的一般步骤，最后从如何降低I/O、避免对查询结果的高成本操作和多表连接中如何提高SQL性能进行了研究。鉴于目前ORACLE在数据库市场上的主导地位，论文将只针对ORACLE进行讨论。
2 影响SQL性能的原因
影响SQL性能的因素很多，如初始化参数设置不合理、导入了不准确的系统及模式统计数据从而影响优化程序(CBO)的正确判断等，这些往往和DBA密切相关。纯粹从SQL语句出发，笔者认为影响SQL性能不外乎以下四个重要原因：
(1)在大记录集上进行高成本操作，如使用了引起排序的谓词等。
(2)过多的I/O操作(含物理I/O与逻辑I/O)，最典型的就是未建立恰当的索引，导致对查询表进行全表扫描。
(3)处理了太多的无用记录，如在多表连接时过滤条件位置不当导致中间结果集包含了太多的无用记录。
(4)未充分利用数据库提供的功能，如查询的并行化处理等。
第(4)个原因处理起来相对简单。论文将针对前三个原因论述如何提高SQL查询语句的性能。
3 SQL优化的一般步骤
SQL优化一般需经过发现问题、分析问题、提出解决措施、应用措施、测试性能几个步骤，如图1所示。“发现问题就是解决问题的一半”，因此在SQL调优过程中，定位问题SQL是非常重要的一步，一般可借助于ORACLE自带的性能优化工具如STATSPACK、TKPROF、AUTOTRACE等辅助用户进行，同时还应该重视动态性能视图如V$SQL、V$MYSTAT、V$SYSSTAT等的研究。
图1 SQL优化的一般步骤
4 SQL语句的优化
4.1 优化排序操作
排序的成本十分高昂，当在查询语句中使用了引起结果集排序的谓词时，SQL性能必然受到影响。
4.1.1 排序过程分析
当待排序数据集不是太大时，服务器在内存(排序区)完成排序操作，如果排序需要更多的内存空间，服务器将进行如下处理：
(1) 将数据分成多个小的集合，对每一集合进行排序。
(2) 服务器向磁盘申请临时空间，将排好序的中间结果写入临时段，再对另外的集合进行排序。
(3) 在所有的集合均排好序后，服务器再将它们进行合并得到最终的结果，如果排序区尺寸太小，合并无法一次完成时，将分多次进行。
从上述分析可知，排序是一种十分昂贵的操作，它消耗大量的CPU时间和内存，触发磁盘分页和交换操作，因此只要有可能，我们就应该在SQL语句中尽量避免排序操作。
4.1.2 SQL中引起排序的操作
SQL查询语句中引起排序的操作大致有：ORDER BY 和GROUP BY 从句；DISTINCT修饰符；UNION、INTERSECT、MINUS集合操作符；多表连接时的排序合并连接(SORT MERGE JOIN)等。
4.1.3 如何避免排序
1)建立恰当的索引
对经常进行排序和连接操作的字段建立索引。在建立索引后，当服务器向这些字段发出排序请求时，将直接引用索引而不进行排序操作；当进行等值连接查询操作时，若建立连接的字段未建立索引，服务器进行的是排序合并连接(SORT MERGE JOIN)，连接操作的过程如下：
对进行连接的两个或多个表分别进行全扫描；
对每一个表中的行集分别进行全排序；
合并排序结果。
如果建立连接的字段已建立索引，服务器进行嵌套循环连接(NESTED LOOP JOINS)，该连接方式不需要任何排序，其过程如下：
对驱动表进行全表扫描；
对返回的每一行利用连接字段值实施索引惟一扫描；
利用从索引扫描中返回的ROWID值在从表中定位记录；
合并主、从表中的匹配记录。
因此，建立索引可避免多数排序操作。
2)用UNIION ALL替换UNION
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。大部分应用中是不会产生重复记录的，最常见的是过程表与历史表UNION 。因此，采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
4.2 优化I/O
过多的I/O操作会占用CPU时间、消耗大量内存和占用过多的栓锁，因此有必要对SQL的I/O进行优化。优化I/O的最有效方式就是用索引扫描代替全表扫描。
4.2.1 应用基于函数的索引
基于函数的索引(FUNCTION BASED INDEX，简记为FBI)提供了索引计算列并在查询中使用这些索引的能力。FBI的实质是对查询所需中间结果进行预处理。如果一个FBI与查询语句中的内嵌函数完全匹配，CBO在生成查询计划时，将自动启用索引范围扫描(INDEX RANGE SCAN)替换全表扫描(FULL TABLE SCAN)。考察下面的代码段并用AUTOTRACE观察创建FBI前后执行计划的变化。
select * from emp where upper(ename)=’SCOTT’
创建FBI前，很明显是全表扫描。
Execution Plan
……
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=1 Bytes=22)
idle>CREATE INDEX EMP_UPPER_FIRST_NAME ON EMPLOYEES(UPPER(FIRST_NAME))；
索引已创建。
再次运行相同查询，
Execution Plan
……
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEES' (Cost=1 Card=1 Bytes=22)
2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_FIRST_NAME' (NON-UNIQUE) (Cost=1 Card=1)
这一简单的例子充分说明了FBI在SQL查询优化中的作用。FBI所用的函数可以是用户自己创建的函数，该函数越复杂，基于该函数创建FBI对SQL查询性能的优化作用越明显。
4.2.2 应用物化视图和查询重写
物化视图是一个预计算结果集，其中通常包含聚集与多表连接等复杂操作。数据库自动维护物化视图，且随用户的要求进行刷新。查询重写机制就是用数据库中的替代对象(如物化视图)将用户提交的查询重写为完全不同但功能等价的查询。查询重写对用户透明，用户完全按常规编写访问数据库的查询语句，优化程序(CBO)自动决定是否对用户提交的查询进行重写。查询重写是提高查询性能的一种非常有效的方法，尤其是在数据仓库环境中针对汇总、多表连接以及其它高成本的操作方面。
下面以一个非常简单的例子来演示物化视图和查询重写在优化SQL查询性能方面的作用。
select dept.deptno，dept.dname，count(*)
from emp，dept
where emp.deptno=dept.deptno
group by dept.deptno，dept.dname
查询计划及主要统计数据如下：
执行计划：
-----------------------------------------
……
2 1 HASH JOIN (Cost=5 Card=14 Bytes=224)
3 2 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=4 Bytes=52)
4 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=42)
主要统计数据：
-----------------------------------------
305 recursive calls
46 consistent gets
创建物化视图EMP_DEPT：
create materialized view emp_dept build immediate
refresh on demand
enable query rewrite
as
select dept.deptno，dept.dname，count(*)
from emp，dept
where emp.deptno=dept.deptno
group by dept.deptno，dept.dname
/
再次执行查询，执行计划及主要统计数据如下：
执行计划：
-------------------------------------
……
1 0 TABLE ACCESS (FULL) OF 'EMP_DEPT' (Cost=2 Card=327 Bytes=11445)
主要统计数据：
------------------------------------
79 recursive calls
28 consistent gets
可见，在建立物化视图之前，首先执行两个表的全表扫描，然后进行HASH连接，再进行分组排序和选择操作；而建立物化视图后，CBO自动将上述复杂操作转换为对物化视图EMP_DEPT的全扫描，相关的统计数据也有了很大的改善，递归调用(RECURSIVE CALLS)由305降到79，逻辑I/O(CONSISTENT GETS)由46降为28。
4.2.3 将频繁访问的小表读入CACHE
逻辑I/O总是快于物理I/O。如果数据库中存在被应用程序频繁访问的小表，可将这些表强行读入KEEP池，从而避免物理I/O的发生。
4.3 多表连接优化
最能体现查询复杂性的就是多表连接，多表连接操作往往要耗费大量的CPU时间和内存，因此多表连接查询性能优化往往是SQL优化的重点与难点。
4.3.1 消除外部连接
通过消除外部连接，不仅使得到的查询更易于读取，而且性能也经常可以得到改善。一般的思路是，有以下形式的查询：
SELECT …，OUTER_JOINED_TABLE.COLUMN
FROM SOME_TABLE，OUTER_JOINED_TO_TABLE
WHERE …=OUTER_JOINED_TO_TABLE(+)
可转换为如下形式的查询：
SELECT …，(SELECT COLUMN FROM OUTER_ JOINED_TO_TABLE WHERE …)FROM SOME_TABLE；
4.3.2 谓词前推，优化中间结果
多表连接的性能低下多数是因为连接操作与过滤操作的次序不合理，大多数用户在编写多表连接查询时，总是先进行连接操作再应用过滤条件，这导致服务器做了太多的无用功。针对这类问题，其优化思路就是尽可能将过滤谓词前推，使不符合条件的记录提前被筛选掉，只对符合条件的少数记录进行连接处理，这样可成倍的提高SQL查询效能。
标准连接查询如下：
Select a.prod_name，sum(b.sale_quant)，
sum(c.sale_quant)，sum(d.sale_quant)
From product a，tele_sale b，online_sale c，store_sale d
Where a.prod_id=b.prod_id and a.prod_id=c.prod_id
and a.prod_id=d.prod_id And a.order_date>sysdate-90
Group by a.prod_id；
启用内嵌视图，且将条件a.order_date>sysdate-90前移，优化后代码如下：
Select a.prod_name，b.tele_sale_sum，c.online_sale_sum，d.store_sale_sum From product a，
(select sum(sal_quant) tele_sale_sum from product，tele_sale
Where product.order_date>sysdate-90 and product.prod_id =tele_sale.prod_id) b，
(select sum(sal_quant) online_sale_sum
from product，tele_sale
Where product.order_date>sysdate-90 and product.prod_id =online_sale.prod_id) c，
(select sum(sal_quant) store_sale_sum
from product，store_sale
Where product.order_date>sysdate-90 and product.prod_id =store_sale.prod_id) d，
Where a.prod_id=b.prod_id and
a.prod_id=c.prod_id and a.prod_id=d.prod_id；
5 结束语
SQL语言在数据库应用中占有非常重要的地位，其性能的优劣直接影响着整个信息系统的可用性。论文从影响SQL性能的最主要的三个方面入手，分析了如何优化SQL查询的I/O、避免高成本的排序操作和优化多表连接。需要强调的一点是，理解SQL语句所解决的问题比SQL调优本身更重要，因此SQL调优需要系统分析人员、开发人员和数据库管理员密切协作。
参考文献
[1]Thomas Kyte.Effective Oracle by Design：Design and Build High-performance Oracle Application[M]，The McGral- Hill Companies，Inc，2003
[2]Kevin Loney，George Koch，Oracle 9i：The Complete Reference[M]，The McGral-Hill Companies，Inc，2002
[3] Oracle9i SQL Reference release 2(9.2)[OL/M]，2002.10. http：//
[4] Oracle9i Data Warehousing Guide release 2(9.2) [OL/M]，2002.03. http：//
[5]Alexey Danchenkov，Donald Burleson，Oracle Tuning：The Definitive Reference[OL/M]，Rampant Techpress，2006.
[6] Oracle9i Database Concepts release 2(9.2) [OL/M]，2002.08. http：//
[7] Oracle9i supplied plsql packages and types reference release 2(9.2) [OL/M]，2002.12. http：// technology/
李昆，SQL SERVER2000课程设计案例精编，北京：中国水利水电出版社，2006 何文华, SQL Server 2000 应用开发教程，北京：电子工业出版社出版，2007 仝春灵，数据库原理与应用—SQL Server 2000，北京：电子工业出版社，20 够了吧 哎~都是被毕业论文逼得
计算机论文常用参考文献
在平平淡淡的日常中，大家都有写论文的经历，对论文很是熟悉吧，论文一般由题名、作者、摘要、关键词、正文、参考文献和附录等部分组成。写论文的注意事项有许多，你确定会写吗？下面是我整理的计算机论文常用参考文献，希望能够帮助到大家。
[1]刘韬,楼兴华.SQL Server2000 数据库系统开发实例导航. 北京:人民邮电出版社,2004.
[2]丁宝康,董健全. 数据库实验教程. 北京:清华大学出版社, 2003:125-170.
[3]孙强. 基于ASP.NET 的专题网站的研究与设计. 东北师范大学,2006.
[4]Michele Leroux Bustamants.Secure your ASP.NET Apps and WCF services with Windows CardSpace. MSDN Magazine,April 2007.
[5]肖建编. ASP.NET 编程实例与技巧集粹. 北京:北京希望电子出版社,2003.
[6]巴兹拉等. ASP.NET 安全性高级编程. 北京:清华大学出版社,2003.
[7]Jesse Libert.Programming C#中文版. 电子工业出版社,2006.
[8]米切尔的等编著. ASP.NET 权威指南. 北京:中国电力出版社,2003.
[9]曾登高编著..NET 系统架构与开发. 北京:电子工业出版社,2003.
[10]Jeffrey Richter. Applied Microsoft .NET Framework programming.北京:清华大学出版社, 2003.
[11]张海藩. 软件工程导论. 北京:清华大学出版社, 2003.
[11]周佩德.数据库原理及应用〔M〕.北京:电子工业出版社,2004.
[12]刘炳文等.VISUAL BASIC 程序设计--数据库篇〔M〕.北京:人民邮电出版社,1999.
[13]李光明.Visual Basic 6.0 编程实例大制作〔M〕.北京:冶金工业出版社,2002.
[14]王兴晶,赵万军等.Visual Basic 软件项目开发实例[M].北京:电子工业出版社,2004.
[15]陈艳峰,高文姬等.Visual basic 数据库项目案例导航[M].北京:清华大学出版社,2004.
[16]李红等.管理信息系统开发与应用〔M〕.北京:电子工业出版社,2003.
[17]周之英.现代软件工程〔M〕.北京:科学出版社,2000.
[18]张红军,王红.Visual Basic 6.0 中文版高级应用与开发指南〔M〕.北京:人民邮电出版社,2001.
[1]孙卫琴,李洪成.《Tomcat 与 JSP Web 开发技术详解》.电子工业出版社,2003年6月:1-205
[2]BruceEckel.《JSP编程思想》. 机械工业出版社,2003年10月:1-378
[3]FLANAGAN.《JSP技术手册》. 中国电力出版社,2002年6月:1-465
[4]孙一林,彭波.《JSP数据库编程实例》. 清华大学出版社,2002年8月:30-210
[5]LEE ANNE PHILLIPS.《巧学活用HTML4》.电子工业出版社,2004年8月:1-319
[6]飞思科技产品研发中心.《JSP应用开发详解》.电子工业出版社,2003年9月:32-300
[7]耿祥义,张跃平.《JSP实用教程》. 清华大学出版社,2003年5月1日:1-354
[8]孙涌.《现代软件工程》.北京希望电子出版社,2003年8月:1-246
[9]萨师煊,王珊.《数据库系统概论》.高等教育出版社,2002年2月:3-460
[10]Brown等.《JSP编程指南(第二版)》. 电子工业出版社 ,2003年3月:1-268
[11]清宏计算机工作室.《JSP编程技巧》. 机械工业出版社, 2004年5月:1-410
[12]朱红,司光亚.《JSP Web编程指南》.电子工业出版社, 2001年9月:34-307
[13]赛奎春.《JSP工程应用与项目实践》. 机械工业出版社, 2002年8月:23-
[14]刁仁宏.网络数据库原理及应用[J].情报理论与实践,2004,(1).
[15]张莉,王强.SQL Server 数据库原理及应用教程[M].清华:清华大学出版社出版,2003.
[16]郭瑞军,李杰,初晓璐. ASP.NET 数据库开发实例精粹[M].西安:电子工业出 版社出版,2003.
[17]宋昕.ASP.NET 网络开发技术实用教程入门与提高[J].情报杂志,2005,(7).
[18]顼宇峰.ASP.NET+SQL Server 典型网站建设案例[M].清华:清华大学出版社出版,2006.
[1]米琦.基于多维变换的无线传感器网络定位算法研究[D].上海交通大学2007
[2]汤文亮,曾祥元,曹义亲.基于ZigBee无线传感器网络的森林火灾监测系统[J].实验室研究与探索.2010(06)
[3]宋保业.无线传感器网络关键技术研究[D].青岛科技大学2008
[4]熊俊俏,冯进维,罗帆.基于JN5139的无线传感器网络节点设计与实现[J].武汉工程大学学报.2010(05)
[5]祝勇.基于LS-SVC的传感网络链路质量评估机制研究[D].南昌航空大学2014
[6]程春蕊,刘万军.高内聚低耦合软件架构的构建[J].计算机系统应用.2009(07)
[7]孙利民等编着.无线传感器网络[M].清华大学出版社,2005
[8]甄甫,刘民,董明宇.基于面向服务架构消息中间件的业务流程系统集成方法研究[J].计算机集成制造系统.2009(05)
[9]陆莹.基于无线传感器网络的组网可靠性研究[D].天津大学2007
[10]潘虎.煤矿安全监控无线传感器网络系统研究[D].华中科技大学2007
[11]张杉.无线传感器网络通信机制的研究[D].电子科技大学2008
[12]魏宝玲.利用无线传感器网络实施道路维护与监控[D].国防科学技术大学2006
[13]吴中博,樊小泊,陈红.基于能量水平的多Sink节点传感器网络路由算法[J].计算机研究与发展.2008(01)
[14]陈伟,吴健,胡正国.分布式监控组态系统实时数据传输模型[J].计算机工程.2006(22)
[15]原羿,苏鸿根.基于ZigBee技术的无线网络应用研究[J].计算机应用与软件.2004(06)
[16]任丰原,黄海宁,林闯.无线传感器网络[J].软件学报.2003(07)
[17]张雪平.使用SecureCRT实现网络管理自动化[J].内江师范学院学报.2005(02)
[1]江群斌.我国商业银行网络银行安全性研究[D].天津大学2012
[2]翟凤航.组织系统数字档案管理系统软件的设计及实现[D].天津大学2012
[3]张兴起.基于VPX标准和多核DSP阵列的信息处理平台设计[D].天津大学2012
[4]王璐.基于1553B总线的综合航电数据加载系统的设计与实现[D].天津大学2012
[5]孙树和.电力企业绩效管理系统研究与设计[D].天津大学2012
[6]郎桐.无源自组网络输电线路实时监测技术研究与应用[D].天津大学2014
[7]郭毅.部门预算管理系统的设计与实现[D].天津大学2014
[8]李灏.软件无线电平台上空时编码的实现和测量[D].天津大学2014
[9]谢国聪.基于.NET技术和多层架构的出租屋和流动人口信息管理系统的设计与实现[D].天津大学2014
[10]高宜文.基于Wi-Fi的智能无线网络视频监控系统视频采集与处理平台的设计[D].天津大学2012
[11]毛延超.无线传感器网络中分簇多信道传输协议研究[D].天津大学2012
[12]夏梓峻.LED-AODV:基于链路预测的车辆网络路由算法研究[D].天津大学2012
[13]尹超.无线网络视频传输性能评测工具的设计与实现[D].天津大学2009
[14]童曦.基于.NET技术和多层架构的人事信息管理系统[D].天津大学2014
[15]王广彧.基于历史轨迹预测的车辆自组织网络混合路由算法[D].天津大学2014
[16]王伟海.龙口矿业集团电网调度自动化系统设计与实现[D].天津大学2012
[17]贺明.基于NC-OFDM的IEEE802.11n与ZigBee共存技术研究[D].天津大学2012
[18]叶君骄.基于SAT的长距离无线mesh网络管理平台[D].天津大学2012
[19]张松.基于IEEE802.11n的长距离无线链路性能实验研究[D].天津大学2012
[20]钟武汨.基于压缩感知的空间无线频谱感知与重构系统研究[D].天津大学2012
[21]高明飞.北皂煤矿海域下开采水情在线监测应用系统[D].天津大学2012
[22]邹宇.基于卫星授时的长距离无线Mesh网络MAC协议ST-TDMA[D].天津大学2014
[23]王为睿.山东省龙口矿业集团6000m~3/h制氧工程DCS设计与实现[D].天津大学2013
[24]莫志德.基于Web应用的停车管理系统开发和设计[D].天津大学2013
[1](美)BruceMolay着,杨宗源,黄海涛译.Unix/Linux编程实践教程[M].清华大学出版社,2004
[2]姜毅,王兆青,曹丽.基于HTTP的实时信息传输方法[J].计算机工程与设计.2008(10)
[3]崔文婧.数字娱乐产业中流行文化对于电子游戏的'影响[D].北京服装学院2010
[4]刘晓晖.SAP系统中不同物料分类的创建方法[D].上海交通大学2011
[5]封炜.基于GPS/GIS/GSM的物流信息监控系统的设计与实现[D].上海交通大学2011
[6]赵胤.基于SAP的离散制造型企业成本控制设计与实现[D].上海交通大学2011
[7]李长斌.驼峰空压站监控系统的设计与实现[D].上海交通大学2012
[8]闵国石.铁路工务作业安全控制系统的研究[D].上海交通大学2012
[9]龚俊.基于Javamail技术的企业Email安全管理系统的设计与实现[D].上海交通大学2012
[10]朱骁勇.基于SCCM的软件分发管理与软件封装模板助手[D].上海交通大学2013
[11]彭诚.基于GPS的物流车辆监控系统的设计和实现[D].上海交通大学2013
[12]苏政华.离散制造型企业的SAP系统FICO模块设计与实现[D].上海交通大学2013
[13]周昕毅.Linux集群运维平台用户权限管理及日志审计系统实现[D].上海交通大学2013
[14]徐朱平.SDP-21框架下项目管理在对日软件外包中的应用[D].上海交通大学2010
[15]刘进学.DeltaVDCS系统在丙烯均相聚合系统中的应用与研究[D].上海交通大学2010
[16]李立平.基于数据挖掘的勘探随钻分析系统[D].上海交通大学2010
[17]王平.半自动闭塞控制信息数字化传输系统的设计与实现[D].上海交通大学2012
[18]潘忠锐.铁路OA系统的设计与实现[D].上海交通大学2012
[19]黄怡君.银行业的存储虚拟化系统设计与实现[D].上海交通大学2012
[20]孙英.浅谈Flash与XML之间的通信[J].电脑知识与技术.2008(15)
[1]刘韬,楼兴华.SQL Server2000 数据库系统开发实例导航. 北京:人民邮电出版社,2004.
[2]丁宝康,董健全. 数据库实验教程. 北京:清华大学出版社, 2003:125-170.
[3]孙强. 基于ASP.NET 的专题网站的研究与设计. 东北师范大学,2006.
[4]Michele Leroux Bustamants.Secure your ASP.NET Apps and WCF services with Windows CardSpace. MSDN Magazine,April 2007.
[5]肖建编. ASP.NET 编程实例与技巧集粹. 北京:北京希望电子出版社,2003.
[6]巴兹拉等. ASP.NET 安全性高级编程. 北京:清华大学出版社,2003.
[7]Jesse Libert.Programming C#中文版. 电子工业出版社,2006.
[8]米切尔的等编著. ASP.NET 权威指南. 北京:中国电力出版社,2003.
[9]曾登高编著..NET 系统架构与开发. 北京:电子工业出版社,2003.
[10]Jeffrey Richter. Applied Microsoft .NET Framework programming.北京:清华大学出版社, 2003.
[11]张海藩. 软件工程导论. 北京:清华大学出版社, 2003.
