Oracle数据库SQL语句优化要点分析

赵新民+++崔海艳

【摘要】针对Oracle数据库性能受到SQL语句制约的问题，本文针对SQL语句的优化要点进行了分析。首先详细描述了Oracle优化器，给出了影响执行计划的因素；其次对SQL语句可优化的类型进行了总结分析，并详细分析了优化的规则，举出了SQL实例进行比较。本文对于系统维护人员和数据库分析师都具有一定的积极作用。

【关键词】Oracle；数据库；SQL

1引言

随着信息化进程的不断加快，数据库的应用越来越广泛，信息系统的优劣与数据库系统的性能有着直接的关联。随着数据库规模的不断扩大，如何保持数据库应用系统高效地运行，受到人们越来越多的关注。

2优化器

对于信息管理系统来说，其最关键的核心是数据库系统，对于众多的应用系统来说，查询操作在整个系统中占据着相当大的比重，也就是说，查询速度的快慢直接影响着信息管理系统的性能。当数据库的规模越大，这个特性表现的越明显，良好的查询语句对系统性能的提高起着积极的作用。

2.1Oracle优化器

无论SQL语句的性能如何，最终都要在Oracle数据库中执行，Oracle数据库在执行SQL语句之前，首先通过优化器利用初始化的参数，并利用指定的优化方法对执行计划进行分析，并执行。

当前，Oracle优化器主要有RBO和CBO两种优化方式。其中RBO是基于规则的优化器，根据访问的路径和访问路径的等级去选择SQL语句的执行计划，假如一条SQL语句有多个路径可以通过，Oralce会自动选择等级最低的访问路径。RBO优化器仅含有几条在小表上低效利用的索引，无形之中增加了I/O，该优化方式效率相对较低；CBO是基于代价的优化器，其成本主要由可用访问路径、嵌入的提示、对象的统计信息等组成，CBO会选择成本代价最低的执行计划。当前，CBO优化器成为Oracle数据库优化的主要组成，CBO的构成如图1所示。

利用Oracle优化器对SQL语句进行分析，在所有的查询中，有一半经过RBO优化之后，执行的速度会快一点，而另一半则由CBO优化后，执行的速度最快。从Oracle8i版本以后，RBO优化已经不再发展RBO。

2.2影响执行计划的因素

影响执行计划的因素较多，总结起来主要有几种。

（1）连接顺序。当前，数据库进行数据查询，大部分情况下都需要由若干表连接，一般采取将查询结果只有一行记录的表优先。

（2）访问路径。对于路径的扫描，Oracle优化器的方式有簇扫描、索引扫描、行ID扫描、全表扫描、散列扫描等。由于Oracel数据库对I/O的评估原则是“块”在整个表中所占的比例来确定选取何种扫描路径。

（3）连接方式。查询优化的重点是连接操作，内表与外表之间进行连接的算法主要有归并连接、散列连接及嵌套循环连接，每种连接都有其自己的优点。

（4）成本估算。其成本的代价主要从I/O、CPU和通信三方面进行考虑，其中I/O是最主要的。在成本估算中占的比重最大。

3优化SQL语句

3.1可优化的SQL语句类型

优化器可以优化SQL语句的类型主要有几种。

（1）简单语句。对于数据表的操作，主要的操作动作有select、update、insert及delete语句，这种类型的语句主要包括from和where。一般来说，可以优化的地方主要在where中。其影响效率的问题主要集中在几个方面：分组或排序过程中包含了过多的中间结果集、对索引的列使用了全表扫描。

（2）连接语句。利用多个表相结合的方式查找相关的数据信息，这也是数据库最常用的方法，由from子句实现多个表的连接，利用where将相关的条件进行关联。其影响效率的问题主要集中在几个方面：表连接顺序不是最优、分组或排序过程包含了过多的中间结果集、索引列使用了全表扫描等。

（3）外部连接。该方式与连接语句有相同之处，同样涉及到多表连接的问题。其影响效率的问题也与连接语句相近。

（4）复杂语句。对于select、update、insert及delete语句中的子查询以select形式存在。该方式的问题是内部查询的效率对外部查询的效率有影响。

（5）复杂查询。利用组操作符将若干简单语句结合起来形成的语句，一般情况下，将语句拆分为上述的四种类型的语句之后再进行优化处理。

对于查询问题的分析，主要是要尽可能地减少子查询或者使用子查询返回的结果集要尽量地减少。

3.2优化的规则

（1）在索引列字段上尽可能地避免使用“！=”、“NULL”、“<>”及“not”等符号，尽可能地不要使用隐式类型的转换。这些符号的使用可能会对索引信息造成影响，进行转变为全表扫描，影响了数据库的性能。

（2）尽可能地不要使用“select * from 表名”。“*”符号代表需要返回所有列，就意味着要扫描所有的返回记录，收取所有的列名与列值。使Oracle不断地进行磁盘的读取及交换。如果需要，将“*”换成具体的列名。

（3）避免在索引列字段中使用改变列的函数。当函数改变了索引列的类型及内容时，可能使原来可以使用的索引值变得无法继续使用，从而影响了系统的效率。

（4）绑定变量进行传值。绑定变量进行传值与数据库收到语句解析后的内容是一致的，Oracle可以在下一条语句到来时直接存入缓存并执行，不必再进行解析和生成执行过程。

（5）合理建立索引。良好的索引机制，可以使系统查询速度更快。

（6）利用where代替having子句。where子句是在分组之前对条件进行筛选，而Having是分组后进行筛选，分组前筛选可以有效减少分组的时间和资源的消耗。endprint

比较下面两个SQL语句：

语句A：select name ， num from department where num not in （select num from school）；

语句B：select name，num from department where not exists（select num from school where department.num=school.num）。

通过在数据库中执行这两条语句，其结果是相同的，执行语句A时，Oracle首先对school表进行整个扫描，没有在school上建立num索引，语句B使用了联合查询，对school表进行部分扫描，利用了num列的索引。语句B的效率要高于语句A。

4结束语

本文对Oracle数据库的SQL语句优化要点进行了分析，由于篇幅所限，并没有给出具体的数据库系统，只给出了两个SQL语句的比较。有兴趣的读者，可以在本文的基础上，对数据库管理系统中的SQL语句进行优化处理操作，以达到提高系统效率的目的。

参考文献

[1] 赵梦勤，李秀兰.Oracle数据库应用系统的优化策略.计算机工程与应用，2003，（27）：213-215.

[2] 王能斌著.数据库系统原理（2000）.北京：电子工业出版社，2001.

[3] Edward Whalen， Mitchell Schroeter 著；高艳春，周兆确，唐艳军译.Oracle 性能调整与优化（第一版）.北京：人民邮电出版社，2002.

[4] 王晓春，赵雾，张岩.Windows 平台上 Oracle 数据库的系统性能优化.计算机工程，2004，30（09）：79-81.

[5] 郑谦益，居梯.Sybase SQL Server性能优化技术及其应用研究.微机发展，2003，（01）：31-33.

作者简介：

赵新民（1974-），男，沈阳师范学院，大学本科，法学学士，现就职于中国联通赤峰分公司信息化服务中心，从事系统维护、网络维护、数据库操作方面工作。

崔海艳（1975-），女，长春邮电学院，大学本科，学士学位，现就职于中国联通赤峰分公司信息化服务中心，从事系统维护、网络维护、数据库操作方面工作。endprint