基于Oracle数据库查询优化策略的研究

黄建军　龚玮玮　肖英剑

摘要：随着信息的高速发展，我们进入了云计算，大数据的时代，Oracle数据库作为信息技术发展的重要产物，它是目前市场上占有率最高，使用范围最广泛的关系型数据库。由于数据库中的数据量日渐庞大，对数据的处理速度提出了更高的要求，如果对数据库的查询不进行优化，将会消耗大量的系统资源和影响数据的查询效率，所以开发人员应该重视数据库查询优化的重要性。本文将对Oracle数据库的查询优化策略进行探讨。

关键词：Oracle数据库;SQL优化;查询

中图分类号：TP311      文献标识码：A

文章编号：1009-3044（2019）13-0010-02

大数据的“大”，云计算的“云”都体现了当今世界的信息量之大、数据规模之大，在数据库的操作中，查询是最常用的操作，因此我们有必要对数据库的查询进行优化，提高系统的查询效率。影响数据库的查询效率有很多因素，最常见的是没有合理使用索引、查询语句没有优化和临时表的建立。下面我们就开始探讨Oracle数据库的查询优化策略。

1 Oracle的优化器

优化器是Oracle数据库中内置的一个核心系统，优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前情景下最高效的执行路径，简单地说，优化器的目的就是给SQL选择一种最优的执行计划。

Oracle在执行一个SQL之前，首先要分析一下语句的执行计划，然后再按执行计划去执行，分析语句的执行计划的工作就是由优化器来完成。不同的情况，一条SQL可能有多种执行计划，但是在某一个时间点，一定只有一种花费时间最少的计划。根据选择执行计划时所用的判断原则，Oracle的优化器有两种，即基于规则的优化器（RBO）和基于成本的优化器（CBO）。

1）基于规则的优化器

Oracle会在代码里事先给各种类型的执行路径定一个等级，一共有15个等级，从等级1到等级15。Oracle认为等级值低的执行路径的执行效率比等级值高的更高，等级1所对应的执行路径的执行效率最高，等级15所对应的执行路径的执行效率最低，Oracle根据目标对象在数据字典中缓存的顺序判断选择哪一种执行计划。RBO是一种适用于OLTP类型SQL语句的优化器，RBO对数据不“敏感”，就按照优先顺序规则进行执行计划的选择，因为RBO存在一些先天的缺陷，RBO在Oracle 10g中已经不被支持，但RBO的相关实现代码并没有从Oracle数据库的代码中移除。

2）基于成本的优化器

CBO选择执行计划时，以目标SQL成本为判断原则，CBO会选择一条执行成本最小的执行计划作为SQL的执行计划，各条执行路径的成本通过目标SQL语句所涉及的表、索引、列等的统计信息算出。这里的成本是oracle通过相关对象的统计信息计算出来的一个值，它实际上代表目标SQL对应执行步骤所消耗的IO、CPU、网络资源（针对dblink下的分布式数据库系统而言）的消耗量，oracle会把网络资源的消耗量计算在IO成本内，实际上你看到的成本为IO、CPU资源。

2 Oracle的执行计划

在Oracle数据库中，执行计划是SQL优化最为复杂也是最关键的部分，因为它实际上代表了目标SQL在Oracle数据库内部的具体执行步骤，只有知道并了解了这些执行步骤，我们才能知道优化器选择的执行计划是否为当前情形下最优的执行计划。下面将介绍几种Oracle里常见的执行计划。

2.1 表访问相关的执行计划

Oracle数据库里与表访问相关的方法有两种，即全盘扫描和ROWID扫描。在执行计划上，与全盘扫描对应的执行计划的关键字是“table access full”，与ROWID扫描对应的执行计划的关键字是“table access by user rowid”或者“table access by user index rowid”。

2.2 B樹索引相关的执行计划

Oracle数据库里与B树索引访问有关的方法有索引唯一扫描、索引范围扫描、索引全扫描、索引快速全扫描和索引跳跃式扫描。在执行计划上，与索引唯一扫描对应的关键词是“index unique scan”，与索引范围扫描对应的关键词是“index range scan”，与索引全扫描对应的关键词是“index full scan”，与索引快速全扫描对应的关键词是“index fast full scan”，与索引跳跃式扫描对应的关键词是“index skip scan”。

2.3 表连接相关的执行计划

Oracle数据库里与表连接相关的方法有排序合并连接、嵌套循环连接、哈希连接等，在执行计划上，与排序合并连接对应的关键词是“sort join”和“merge join”，与嵌套连接循环连接对应的关键词是“nested loops”，与哈希连接对应的关键词是“hash join”。

3 Oracle数据库的查询优化策略的实现

Oracle数据库的查询效率受很多因素的影响，以下就一些常用于提高查询效率的策略进行分析。

3.1 索引优化

索引是表的一个概念部分，用来提高检索数据的效率，减少I/O操作和消除磁盘排序，ORACLE使用了一个复杂的自平衡B-tree结构。通常，通过索引查询数据比全表扫描要快，当Oracle找出执行查询的最佳路径时， Oracle优化器将使用索引。通常在以下条件使用索引可以提高查询速度：表的主键和外键使用索引;对经常与其他表进行连接的表的连接字段使用索引;经常出现在Where子句中的字段使用索引;选择性高的字段建立索引。一般，在大型表中使用索引特别有效，提高查询速度特别明显。

3.2 SQL语句优化

对于Oracle数据库而言，一个SQL语句的好坏将会直接影響系统的性能，SQL语句的优化是查询优化的一个重要的方法，下面列出常见的SQL语句优化。

①exits和in的用法

exists对外表做循环，每次循环再对内表进行查询，而in是把外表和内表做hash连接，先查询内表，再对外表匹配。当用exists时对内表查询用了索引，而对外表查询是全部遍历，使用in时，对外表使用索引，而内表是有多少都全部遍历。综上所述，当两个表的大小相当时，in和exists的效率差不多;当子查询表更大的时候，使用exists效率更高;当子查询表更小的时候，使用in效率更高。

②Where和Having的用法

Where和Having都是给查询结果做限定条件，两者的不同之处在于Having常用于分组的查询，与grounp  by配合使用。Having和Where在数据量不大的情况下，效率差不多，但当数据量很大时，效果就很明显了，因为使用Having时，是先分组后条件判断，需要检索所有的记录再条件，判断而当我们使用Where时，我们可以先判断再分组，分组的效率就更快了，从而查询的效率也很明显地提高了。

③带通配符“%”的like语句

在我们平时实现系统的某些功能是，往往我们需要对一个表模糊查询，模糊查询通常会用到like和‘%，如果模糊查询的这一列有索引，这时我们就应该小心了，要合理的使用这种方法，如果通配符‘%在搜寻词首出现，那么索引就没有用了，这时就要全表搜索，如果在其他位置，就可以利用索引，查询的效率就会得到提升，例如：查询student表中name列中包含‘梦的学生学生，并且name列建立了pk_name的索引，第一种方法是select stuid from student where name like ‘%梦%;另一种方法是select stuid from student where name like ‘梦%，方法一不会使用索引pk_name，方法二会使用索引，可以提高查询的速率。

④用TRUNCATE替代DELETE

当删除表中的记录时，在通常情况下，回滚段（rollback segments ） 用来存放可以被恢复的信息。如果你没有COMMIT事务，ORACLE会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况） 而当运用TRUNCATE时， 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复，因此很少的资源被调用，执行时间也会很短。

3.3 共享SQL语句

为了不重复解析相同的SQL语句，在第一次解析之后， Oracle将SQL语句存放在内存中.这块位于系统全局区域SGA（system globalarea）的共享池（shared bufferpool）中的内存可以被所有的数据库用户共享.因此，当你执行一个SQL语句（有时被称为一个游标）时，如果它和之前的执行过的语句完全相同， Oracle就能很快获得已经被解析的语句以及最好的执行路径. Oracle的这个功能大大地提高了SQL的执行性能并节省了内存的使用.

4 结语

综上所述，开发人员应该充分地理解Oracle数据库的优化器的工作原理和执行计划，充分利用索引、合理使用SQL语句和临时表，结合不同的实例选择不同的优化方式，从而提高系统的查询效率。

参考文献：

[1] 韩峰.SQL优化最佳实践构建高效率Oracle数据库的方法与技巧[M].北京：机械工业出版社，2016.

[2] 崔华.基于Oracle的SQL优化[M]. 北京：电子工业出版社，2018.

[3] 盖国强，李轶楠 主编.oracle性能优化与诊断案例精选[M]. 北京：人民邮电出版社，2016.

[4] 师庆栋.Oracl查询优化改写技巧与案例2.0[M]. 北京：电子工业出版社，2018

[5] [美]Karen MortonKerry OsborneRobyn SandsRiyaj ShamsudeenJared Still.精通Oracle SQL [M]. 2版. 北京：人民邮电出版社，2014.

【通联编辑：光文玲】