Oracle数据库查询语句的优化研究

吴健

【摘 要】相比较一般数据库，Oracle的性能更加优秀，存储的级别更大，使用的范围更加广泛，因而对Oracle数据库的研究才更显得有必要。对于Oracle数据库性能的研究中， 查询语句的执行效率十分重要， 特别对于大型数据库系统， SQL查询语句效率的高低相比可能有上百倍之差。下文将重点介绍了Oracle数据库SQL查询语句的处理过程， 再对不同优化器工作原理进行了解析， 分析若干方法去提升Oracle数据库查询优化性能， 同时也能使我们进一步去了解Oracle数据库，方便我们的工作生活。

【关键词】Oracle数据库；SQL语句；优化；效率

0 导言

数据库技术是计算机科学技术发展最快、应用最为广泛的信息技术之一，数据库技术已经从相对传统的商业领域不断扩大到许多新的领域，如医疗、多媒体、经济、政治、科研等领域。其中Oracle数据库是应用最为广泛的数据库，其中在通信领域范围内，全球20家排名顶尖的通信公司都是使用Oracle产品；在IT服务公司中，中国前100大的IT公司（HP、IBM、凯建、埃森哲、东软等）全部都用Oracle相关技术。对Oracle数据库的优化研究就是进行有目的性的优化其组件以改善Oracle数据库的性能，即增加数据库的吞吐量和减少数据库的响应时间，因而从大多数Oracle系统的应用实例来观察，其中数据库的查询操作在各种操作中所占的比率是最大的，查询效率成为了影响Oracle数据库各项性能的关键因素。所以本文将介绍Oracle数据库查询语句的一些优化研究方案。

1 Oracle数据库SQL语句处理过程

一般来说，数据库处理SQL语句都要经过三个过程：分析、执行、返回结果，比如Congnos ReportNet通过拖放完成表现层后，能够自动地生成SQL语句，然后将生成的SQL语句传递到Oracle数据库中进行数据处理。

1.1 分析

分析是处理SQL语句的第一步，它包含了以下几个方面：

（1）语法分析：Oracle是采用数据库常用的自下向上的分析方法，包含检验语法规范，命名规范，它是处理SQL语句中消耗时间做多且代价最高的步骤；增加Oracle数据库的查询效率可以主要表现在绑定变量和存储过程两个方面：

A.绑定变量：如果使用SQL语句查询的频率越高，系统硬件消耗的资源也将会变大，以至于了减少系统的资源使用空间，导致用户的访问数量降低，另外同时其它查询语句也将被从共享池中踢出， 而使用绑定变量，提交给相同对象的同样的查询的用户唯一使用就能够被重复使用，其效率能大大地增强。

B.存储过程：存储过程只会是在创建时编译，然后的每次执行都再也不需要进行编译，而普通的SQL语句执行一次都需要进行编译执行，相比较，尽量使用存储过程就会增强Oracle数据库的执行效率。

（2）语义分析，学过编译原理的应该会知道这一步是相当重要的，Oracle主要是分析SQL语句格式正确与否，各个对象是否存在，以及它是否拥有足够的权限执行。

SQL>SELECT DEPT_NO，DEPT_NAME，DEPT_LOC FROM DEPT；

提示：ERROR at line 1：

ORA-00942： table or view does not exist

由于查询没有可供访问的DEPT（部门表）对象，因此该SQL语句无法通过语义检测，不能查询结果。

（3）表达式转换，将复杂的SQL表达式转换成为相对应的对于基表的SQL查询语句，写出SQL的标准。

A.不要在WHERE中进行表达式计算，例如SELECT * FROM EMP WHERE DEPT_NO>=10*10 AND DEPT_NO >（10+1）*10是不被允许的查询语句。

B.数据类型的匹配

SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100 没有SELECT EMP_SALARY FROM EMP WHERE EMP_SALARY>100.00好了。

（4）选择优化器。Oracle的优化器共有3种：

1）RULE （基于规则 2）COST （基于成本 3）CHOOSE （选择性）

在默认的情况下，Oracle数据库一般选用的是选择性优化器， 目的就是规避不需要的全表扫描，但是实际应用中还是尽量不使用选择性优化器，而采用的是基于规则或者基于成本的优化器。当然，最好是能根据批量的SQL自己编制一个特定的优化器来帮助自己优化SQL查询。

（5）选择连接方式。优化器使用了六种不同的连接方式（不做详解）：

1 嵌套循环连接（NESTED LOOP JOIN）

2 群集连接 （CLUSTER JOIN）

3 排序连接（SORT MERGE JOIN）

4 笛卡尔连接（CARTESIAN JOIN）

5 哈希连接（HASH JOIN）

6 索引连接（INDEX JOIN）

这六种连接方式都存在各自独特的技术特性，在特定的条件下，各自高效的性能都能够得到充分的发挥。

（6）选择连接顺序。在Oracle SQL查询语句中最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集，这里所遵循的规则是将表结合起来，通常优先使用限制最严格的WHERE子句来连接表。

选择数据搜索路径。根据以上条件选择合适的数据搜索路径，能使SQL语句执行的更加有效率。

1.2 执行

主要在于使用更新和删除的SQL语句时，必须要锁定数据列，防止其他用户进行修改。Oracle会先在数据库缓冲中寻找是否存在所需要的数据块，如果存在这样的数据块，就可以直接进行读或者修改操作，否则数据会被从物理文件之中读取到数据库缓冲区中。

1.3 返回结果

对SELECT 语句需要返回结果的语句，看返回的结果集是否需要按照实际应用中排序，若需要，则排序后输出。

2 Oracle优化器工作原理

Oracle优化器分为三类：

1）RULE优化器：基于规则的优化器相对而言比较简单，工作原理概括为检查数据库当中的所有的可用路径并且将这些可用的路径与已经存在的路径表进行比较，来确定SQL语句具体的执行过程。RULE优化的过程中不需要任何表或索引的统计信息，而且也会忽略任何表或索引的统计信息 。

2）COST优化器：基于开销的优化器相对比较复杂，通过使用数据库的结构和数据等信息来选择最优的执行计划。优化的过程中需要相关表和索引的统计信息。

3）CHOOSE优化器：在存在相关统计信息的情况下采用基于开销的优化器，在不存在相关统计信息的情况下才用基于规则的优化器。这是8I中默认的优化器工作方式。

同样的查询语句可以有很多不同的执行计划， Cost优化器将自动计算不同执行计划所消耗的系统资源， 接着就会选出消耗最低的执行计划。一条查询的消耗大致被分为3个基本成分：I/O消耗、CPU消耗、Network消耗。

3 Oracle查询优化方法

3.1 共享SQL语句

Oracle在执行每条SQL语句的时候都会先对语句进行语法分析，而这个过程是比较消耗资源的，为了能够省略这个步骤来提高SQL查询语句的执行效率，Oracle采用的共享SQL语句的方式，就是把每条从用户发出的SQL存储到系统全局区（System Global Area）的共享池当中，这个共享池当中的所有SQL语句将会被数据库当中的所有有权限的用户共享使用；所以当你每天执行一条SQL语句时，Oracle会先去共享池当中查找是否存在这样一条SQL语句的执行记录，如果存在则会直接得到该SQL语句的执行计划和执行路径，这么就大大的节省了系统内存并且提高了SQL语句的执行效率。

如果某一查询语句需要匹配共享池之中的SQL， 都必须遵照以下三条规则。

（1）SQL语句之间字符之间必须满足相互匹配，另外字符的大小写也需要相互匹配。

（2）SQL语句中的对象必须完全一致，对于如下查询：

SELECT MAX（EMP_SALARY） FROM EMP LIMIT；

（3）SQL语句中使用命名的绑定变量必须相同。

3.2 创建和使用索引

在关系型数据库中，索引是数据库之中的一种结构。简而言之，索引的作用类似于于图书的目录，使用索引相当于使用这个目录快速找到所需文章的内容，它能够使作用于表的查询语句执行得更加有效率。对于一个数据库而言，索引是必须的，但是对于现在存在的各种大型数据库而言，索引强大的作用大大提升数据库的查询性能，使其变成了现在数据库不可或缺的重要部分。索引的创建需要去维护， 如果对表进行更新、插入、删去等操作的时候， 已经创建好的索引也需要去更新。因而在使用索引时需要遵循以下的原则：

（1）对于查询操作时较少需要的列或者列的重复值比较多，则不需要建立索引。

（2）对于按范围查询的列，最好建立索引。

（3）如果一个表中包含了主键和外键，则必须要建立索引。

（4）对于一些特殊的数据类型，不要建立索引。

（5）如果对数据库经常执行一些插入、删除等操作时， 不能建立太多索引。

（6）索引可以跟Where语句的集合融为一体。

当创建完索引后， 若SQL查询语句存在如下情况， 优化器生成的执行计划也会对创建好的所有进行使用， 因而尽量不要发生以下状况。

（1）如果对某个表中的两个列进行比较时，索引必不会一直被数据库使用；

如查询操作：SELECT EMP_NO，EMP_SALARY FROM EMP WHERE EMP_NO>MGR；

（2）数据库的表中存在NULL值。一般索引中并不会有NULL值。如果WHERE条件语句中出现is null或者is not null时， 索引就无效，不能被使用；

如下查询将将不会对comm列的索引进行访问：

SELECT EMP_NAME，EMP_NO，EMP_SALARY FROM EMP WHERE COMM IS NOT NULL；

（3）在WHERE条件语句中使用了不等于操作符号，如！=、<>；

SELECT EMP_NAME， EMP_SALARY，EMP_NO，DEPT_NO FROM EMP WHERE DEPT_NO<>20 GROUP BY EMP_NO；

可以改写为：

SELECT EMP_NAME， EMP_SALARY，EMP_NO，DEPT_NO FROM EMP WHERE DEPT_NO<20 OR DEPT_NO>20 GROUP BY EMP_NO；

（4）当对操作的列使用函数时；

SELECT EMP_NAME， EMP_NO， JOB，EMP_SALARY FROM EMP WHERE UPPER（DEPT_NO）=10 GROUP BY EMP_NO；

可以创建基于函数的索引来优化此查询。

Create index DEPT_NO_idx on emp（upper（DEPT_NO））；

（5）对相互不匹配的数据类型进行比较，DEPT_NO字段类型是VARCHAR（2）， 如查询：

SELECT EMP_NAME， EMP_NO， EMP_SALARY FROM EMP WHERE DEPT_NO=30；

Oracle数据库可以自动把where条件子句变成TO_NUMBER（DEPT_NO）=30， 这样就限制了DEPT_NO列上索引的使用。

3.3 重写SQL语句

3.3.1 使用WHERE代替HAVING

尽量不要使用HAVING条件子句， HAVING在检索出所有满足条件的结果集之后，再对这些结果集进行筛选。 整个的处理过程需要排序，总计等一系列操作。而WHERE条件子句限制了记录的数量，减少资源在这方面的消耗；

例如查询语句：

SELECT EMP_NAME，EMP_NO， AVG（EMP_SALARY） FROM EMP GROUP BY DEPT_NO HAVING EMP_NO<1000；

对表EMP中所有列项进行汇总之后， 再踢除EMP_NO小于1000的记录， 可以将上列查询语句改写为如下语句：

SELECT EMP_NAME，EMP_NO， AVG（EMP_SALARY） FROM EMP WHERE EMP_NO<1000 GROUP BY DEPT_NO ；

原因是在汇总前排除掉了EMP_NO<1000的记录，从而提升了查询语句的执行效率。

3.3.2 UNION ALL代替UNION

当我们需要UNION两个查询结果集合时，这两个结果集将会以UNION ALL的方式被Oracle数据库进行合并，然后进行排序后再输出结果。如果使用 UNION ALL替代UNION，就不需要这样的排序了，将会提升Oracle数据库的查询执行的效率。另外特别注意的是，UNION ALL会重复输出两个结果集中相同的记录。

3.3.3 使用DECODE函数

使用DECODE函数能够避免重复扫描表中相同的数据。

例如：

SELECT COUNT（EMP_NO），SUM（EMP_SALARY） FROM EMP WHERE DEPT_NO = 20 AND EMP_NAME LIKE‘JORDAN%；

SELECT COUNT（EMP_NO），SUM（EMP_SALARY） FROM EMP WHERE DEPT_NO = 30 AND EMP_NAME LIKE‘JORDAN%；

可以使用DECODE函数高效的得出查询结果：

SELECT COUNT（DECODE（DEPT_NO，20，X，NULL））

D20_COUNT，COUNT（DECODE（DEPT_NO，30，X，NULL））

D30_COUNT，SUM（DECODE（DEPT_NO，20，EMP_SALARY，NULL））

D20_EMP_SALARY，SUM（DECODE（DEPT_NO，30，EMP_SALARY，NULL））

D30_EMP_SALARY FROM EMP WHERE EMP_NAME LIKE‘JORDAN%；

3.3.4 用TRANCATE代替DELETE

当删除表中的记录时，在一般情形中，回滚段存放的是能够被恢复的信息。如果事务没有被提交，Oracle数据库之中的数据将会恢复到最初没有被删除的的状态，而使用TRUNCATE，被恢复的信息不再存放于回滚段之中。因而调用的资源将变得很少，SQL语句执行的效率也将变得越快。

3.3.5 物化视图查询重写

所谓物化视图查询重写就是，如果初始化参数query_rewrite_ enabled设置为TRUE，并且数据库运行在COST优化模式下，当对基表进行查询时，Oracle会自动判断是否能利用这个基表的所有包含ENABLE QUERY REWRITE关键字的物化视图，如果可以且根据统计信息判断通过查询物化视图代价更小，则Oracle数据库的查询语句将被重写，通过查询物化视图可以得出正确的查询结果。

3.3.6 创建位图索引

由于索引是位图，所以很多时候可以对这些索引中的位图进行位运算（and 和 or），在某些情况下这样的速度明显比b树快；由于位图索引可以存储null，所以可以直接通过位图索引计数。

SELECT EMP_NAME FROM EMP WHERE EMP_SALARY>10000 and JOB=SALES AND DEPT_NO=10；已用时间：00：00：01.13

在对EMP_SALARY（月薪）， JOB（职位）， DEPT_NO（部门编号）三列创建位图索引， 上面的SQL查询语句在执行时话费时间为：00：00：00.08， 对比两次执行所消耗的时间，SQL执行的效率的得到提升，若将这种方式使用到实际应用当中，数据库性能的提升等级不言而喻。

4 备注

本文所用的表为EMP（员工表），表中字段结构为EMP_NO（员工编号）、EMP_NAME（员工姓名）、DEPT_NO（所属部门编号）、EMP_SALARY（工资）、JOB（职位）等；DEPT（部门表），表中字段结构为DEPT_NAME（部门名称），DEPT_NO（部门编号），DEPT_LOC（部门所在城市）等。

【参考文献】

[1]张学义，王观玉，黄隽.基于Oralce数据库SQL查询优化研究[J].制造业自动化，2011（02）.

[2]刘星.Oracle数据库的性能优化与调整[J].科技资讯，2011（04）.

[3]张学琴. 基于Oracle数据库的SQL语句优化[J].电脑知识与技术，2010（01）.

[责任编辑：汤静]