1． 基本的Sql编写注意事项尽量少用IN操作符，基本上所有的IN

操作符都可以用EXISTS代替。不用NOT IN操作符，可以用NOT EXISTS或者外连接+替代。Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS比NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。

2． 不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫

描，可以用“<” or “>”代替。 3． Where子句中出现IS NULL或者IS NOT NULL时，Oracle会

停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断NULL的操作。

4． 当通配符“%”或者“_”作为查询字符串的第一个字符时，索引不会

被使用。

5． 对于有连接的列“||”，最后一个连接列索引会无效。尽量避免连接，

可以分开连接或者使用不作用在列上的函数替代。

6． 如果索引不是基于函数的，那么当在Where子句中对索引列使

用函数时，索引不再起作用。Where子句中避免在索引列上使用计算，否则将导致索引失效而进行全表扫描。

7． UNION操作符会对结果进行筛选，消除重复，数据量大的情况

下可能会引起磁盘排序。如果不需要删除重复记录，应该使用UNION ALL。

8． Oracle从下到上处理Where子句中多个查询条件，所以表连接

语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。 Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO优化时有效，下文详述）。

9． Order By语句中的非索引列会降低性能，可以通过添加索引的

方式处理。严格控制在Order By语句中使用表达式。

10． 不同区域出现的相同的Sql语句，要保证查询字符完全相同，以

利用SGA共享池，防止相同的Sql语句被多次分析。

11． 多利用内部函数提高Sql效率。

12． 当在Sql语句中连接多个表时，使用表的别名，并将之作为每列

的前缀。这样可以减少解析时间。

需要注意的是，随着Oracle的升级，查询优化器会自动对Sql语句进行优化，某些限制可能在新版本的Oracle下不再是问题。尤其是采用CBO（Cost-Based Optimization，基于代价的优化方式）时。

我们可以总结一下可能引起全表扫描的操作：

在索引列上使用NOT或者“<>”;

对索引列使用函数或者计算；

NOT IN操作；

通配符位于查询字符串的第一个字符；

IS NULL或者IS NOT NULL；

多列索引，但它的第一个列并没有被Where子句引用；

Oracle优化器

Oracle优化器（Optimizer）是Oracle在执行SQL之前分析语句的工具。

Oracle的优化器有两种优化方式：基于规则的（RBO）和基于代价的（CBO）。

RBO： 优化器遵循Oracle内部预定的规则。

CBO： 依据语句执行的代价，主要指对CPU和内存的占用。优化器在判断是否使用CBO时，要参照表和索引的统计信息。统计信息要在对表做analyze后才会有。Oracle8及以后版本，推荐用CBO方式。

Oracle优化器的优化模式主要有四种：

Rule：基于规则；

Choose：默认模式。根据表或索引的统计信息，如果有统计信息，则使用CBO方式；如果没有统计信息，相应列有索引，则使用RBO方式。

First rows：与Choose类似。不同的是如果表有统计信息，它将以最快的方式返回查询的前几行，以获得最佳响应时间。

All rows：即完全基于Cost的模式。当一个表有统计信息时，以最快方式返回表所有行，以获得最大吞吐量。没有统计信息则使用RBO方式。

设定优化模式的方式

Instance级别：在init<SID>.ora文件中设定OPTIMIZER_MODE； Session级别：通过SQL> ALTER SESSION SET

OPTIMIZER_MODE=;来设定。

语句级别：通过SQL> SELECT /*+ALL+_ROWS*/ ……;来设定。可用的HINT包括/*+ALL_ROWS*/、/*+FIRST_ROWS*/、/*+CHOOSE*/、/*+RULE*/ 等。

要注意的是，如果表有统计信息，则可能造成语句不走索引的结果。可以用SQL>ANALYZE TABLE table_name DELETE STATISTICS; 删除索引。

对列和索引更新统计信息的SQL：

SQL> ANALYZE TABLE table_name COMPUTE STATISTICS; SQL> ANALYZE INDEX index_name ESTIMATE STATISTICS;

第二篇：Oracle数据库设计中SQL语句优化研究

Oracle数据库中SQL语句优化研究

中国电信股份有限公司石嘴山分公司 任凤

摘要： 简要介绍数据库中SQL 语句优化技术，并结合具体的事例，探讨了基于Oracle 数据库的SQL 语句优化。

关键词： Oracle 数据库；SQL 语句；优化

1、引言

Oracle 数据库是当前应用最广泛的大型数据库管理系统, 它在各个领域的使用不断增长, 其查询性能直接关系到系统的运行效率, 对其查询优化方法的研究更具有现实意义。数据库SQL 查询优化的一个基本原则就是: 通过尽可能少的磁盘I/ O访问获取所需要的数据。要评价查询优化性能, 需要在数据库优化前后比较其评价指标: 响应时间和吞吐量之间的权衡、数据库的命中率以及内存的使用效率, 并以此来衡量优化的效果和指导优化的方向。优质的查询语句可以使应用系统的性能得到大大提高。拙劣的应用SQL 语句、大小不合适的SGA 内存结构、效率差的SQL 语句、执行计划过度的文件I/O及访问数据库资源的紊乱等都会影响数据库系统的性能和执行效率。本文将从数据库设计的角度及SQL语句优化技术的两方面进行详细地阐述如何提高数据库的性能和执行效率。

一、 数据库设计的优化

数据库设计是应用系统设计的基础, 其性能直接影响应用系统的性能。数据库性能包括查询响应时间和吞吐量两个方面。数据库设计优化的主要目标就是减少磁盘I/ O、减少CPU 利用率和资源竞争, 降低查询响应时间或提高系统吞吐量。

1.1 磁盘I/ O 优化

磁盘I/ O是影响Oracle 数据库性能的瓶颈,而影响磁盘I/ O 性能的主要原因有磁盘竞争、I/O。资源竞争增强时,响应时间增长读取次数和数据块空间的分配管理不当等。Oracle进程经常需要访问数据文件和重做日志文件,当这两种类型的文件位于同一磁盘中,就会造成磁盘竞争。将表空间所属的数据文件存储在多个不同的物理磁盘上,使系统I/ O 趋于平衡;把数据、日志、索引放到不同的I/O设备上,可以减少磁盘竞争和I/ O读取次数。提高I/O设备的并发访问率,可以有效提高SQL 语句的执行效率。当竞争增强的时候, 系统响应时间将增长。

1.2内存优化调整

要充分发挥Oracle据库的优势,必须对数据库的各项初始化参数进行合理配置。从Oracle 数据库内存优化管理的角度出发,针对影响其性能的因素及其对应的参数,分别从数据缓冲区优化、共享池优化、重做日志缓冲区优化几个方面完成内存优化配置。

1.3优化全表扫描操作

一次I/O 能读取多块数据块(由db_block_multiblock_read_count 参数设定),这极大地减少I/O总次数,提高了系统的吞吐量, 所以利用多块读取的方法可以高效地实现全表扫描。

二、SQL 语句优化

SQL语句尤其是复杂SQL语句的性能优化对于数据库的性能是至关重要的。实际数据库使用过程中80% 的性能问题是由于用户使用了不恰当的SQL 查询语句造成的,因此优化SQL 语句可以提升整个系统的运行效率。在集中式数据库中, SQL 查询的执行总代价= I/ O 代价+ CPU 代价+ 内存代价。调整影响其执行效率的三大因素来减少系统总代价:一是减少查询所产生的I/ O 总次数; 二是减少CPU 的计算频度, 减少SQL 语句中需要计算的量和参数; 三是减少对系统内存的使用和占用时间。

2.1检查不合理的SQL 语句

要检测出不合理的SQL 语句, 首先要生成执行计划,最简单的办法有两种: 一是SQL> set autotrace on 自动记录执行计划; 二是explainplan for ‘SQL 语句’，然后通过select * from table(dbms_xplan.display ( ) ) 来查看执行计划。第一种方法查看执行时间较长的SQL 语句时, 需要等待该语句执行成功后才返回执行计划。如果只想得到执行计划可以采用explainplan 或者SQL> set autotrace traceonly, 它们不会真正的执行语句。通过执行计划来确定不合理的SQL 语句, 为SQL 优化做准备。

2.2共享SQL 语句

在第一次解析之后, Oracle 将SQL语句存放位在系统全局区域( SGA) 的共享池中,它可以为所有的数据库用户共享,大大地提高了SQL 的执行性能并节省了内存。当用户提交一个SQL 语句时,服务器进程在共享池中查找有无该条语句,如果有就跳过语法分析等过程,节省了SQL语句的分析和编译的开销只有在共享池中不存在等价SQL语句的情况下,才对该语句作语法分析,并为该语句分配新的共享SQL 区。对于编程者来说,要尽量提高SQL 语句的重用率,尽量使用Bind 变量,来减少语句的分析时间。但是Oracle 对语句的匹配是相当严格的,要达成共享, 它要遵循三条规则:

（1） SQL 语句必须完全相同( 包括空格、换行等)。下列SQL 语句都是不同的, SELECT * from EMP; Select * From Emp; SELECT * FROM EMP;

（2）语句中的对象必须完全一致。

（3）语句中的绑定变量必须使用相同的名字。例如: 下面的两个SQL 语句是相同的,select ename from emp w here empno=:emp.no 和

select enam from emp where empno=:emp. no。

三、SQL语句优化技术

3.1基于索引的优化

索引是影响SQL 语句性能的一个重要因素, 在表上建立合适的索引, 可以避免全表扫描并减少I/ O 开销, 提高数据查询速度。但是创建索引会增加系统时间和空间的开销, 创建索引时必须要与实现应用系统的查询需求密切结合, 才能达到优化查询的目的。索引使用的一些原则：

（1） 当选择数据少于全表的20%, 并且表的大小超过ORACLE的5个数据块时,使用索引才会有效,否则用于索引的I/ O 加上用于数据的I/ O 就会大于做一次全表扫描的I/O,反而会降低查询效率;

（2） 由于表和索引的数据块通常是被同时读取的, 所以应该尽量将表和其相关联的索引放置在不同的磁盘上, 减少I/ O 冲突;

（3）索引的创建也是需要代价的, 对于DML 操作, 每个索引都要进行相应的删除、更新、插入操作。从而导致DML 操作的效率变低。因此定期的重构索引是有必要的, alter index < index name> rebuild < tablespacename> ;

（4）避免隐式转换和不必要的类型转换。例如: to_char( ) , to_number( ) , to_date( ) 等会导致该字段的索引失效, 从而对该表进行全表扫描, 对一个百万数量级的表进行全表扫描的会对系统带来严重的性能隐患;

3.2、基于Oracle 数据库对表访问的优化

3.2.1 全表扫描

全表扫描就是对表中每条记录进行顺序的访问。Oracle 采用一次读入多个数据块(database block)的方式对全表扫描进行优化。

3.2.2 选择最有效率的表名顺序

Oracle 的解析器按照从右到左的顺序对FROM 子句中的表名进行处理，FROM 子句中写在最后的表将最先被处理，当FROM 子句中包含多个表时，必须选择记录条数最少的表作为基础

表。如果有3 个以上的表进行连接查询，那么，就需要选择交叉表作为基础表，交叉表就是被其他表所引用的那个表？

例如: student 表描述了location 表和category 表的交集?

SELECT * FROM location L, category C, student S WHERE S.sno BETWEEN 1000 AND 2000 AND S.catno=C.catno AND S.locn=L.locn

相对于下列SQL 语句更有效率:

SELECT * FROM student S, location L, category C WHERE S.catno=C.catno AND S.locn=L.locn AND S.sno BETWEEN 1000 AND 2000.

3.2.3 通过ROWID 访问表

为了提高访问表的效率，强烈采用基于ROWID 的访问方式情况。ROWID 包含了表中记录的物理位置信息。Oracle 数据和存放数据的物理位置之间的联系，采用索引进行实现。通常情况下，索引提供了快速访问ROWID 的方法，因此那些基于索引列的查询，能够得到性能上的提高。

3.3 SQL 语句编写需要满足的规则

根据上面两种Oracle 访问表的方式，进行SQL 语句的编写时，需要满足以下规则：

（1） 尽量使用索引。

（2） 选择联合查询的联合次序。在SQL 语句的编写中，应该注意首先需要选择要查询的主表，因为主表要扫描整个表数据，所以主表应该数据量最小。

（3） IN 或者NOT IN 语句在子查询中慎重使用，可以使用(NOT) EXISTS。

（4） 慎重使用视图的联合查询，尤其慎重使用比较复杂的视图之间的联合查询。一般来说，将对视图的查询分解为对数据表的直接查询能够取得更好的效果。

（5） 可以在参数文件中进行SHARED_POOL_RESERVED_SIZE 参数的设置，这个参数保留

了一个连续的内存空间在SGA 共享池中，对于存放大的SQL 程序包非常有帮助。

（6） 对于某些经常使用的存储过程，可以通过Oracle 公司提供的DBMS_SHARED_POOL 程序固定在SQL 区中而不被换出内存，对于提高最终用户的响应时间是非常有利的。

3.4 实例

（1） 通过使用索引进行SQL 语句优化例如，比较下面两条SQL 语句：

语句A： SELECT deptno, deptname FROM dept WHERE deptno NOT IN (SELECT deptno FROM student);

语句B: SELECT deptno, deptname FROM dept WHERE NOT EXISTS SELECT * FROM student WHERE dept.deptno=student.deptno);

通过执行发现,这两条查询语句实现的结果相同，但是在执行语句A 时，Oracle 会扫描整个student 表，而建立在student表上的deptno 索引没有使用到，当执行语句B 时，由于在子查询中使用了联合查询，Oracle 只扫描了student 表中的部分数据，并利用了deptno 列的索引，因此，语句B 的效率比语句A 的效率高。

四、 结束语

通过进行SQL 语句优化，提高了Oracle 数据库系统的性能,明显降低了其系统响应时间，提高了程序运行速度，使系统的顺利运行得到良好的保障。对于Oracle 数据库应用系统来说, SQL查询语句的性能优劣直接影响整个信息系统的效率, 效率高的查询语句和效率低的查询语句速度相差可以达到上百倍。只有认真分析Oracle 运行过程当中出现的各种性能问题, 才能保证Oracle 数据库高效可靠地运行。论文从影响SQ L 性能的最主要的几个方面入手, 分析了如何优化SQ L 查询的I/ O、内存参数的调整和SQL 语句的优化。数据库的性能调整是一个系统工程, 需要在大量的实践工作中不断地积累经验, 结合上述各种优化技术, 从而更好地进行数据库调优。

参考文献:

[ 1] 蒋年德, 李英. Oracle 环境下管理信息系统的优化设计 计算机应用研究, 2003,

[ 2] Donald K. Burleson. Oracle 高性能SQL 调整 刘砚, 黄春译. 北京: 机械工业出版社, 2002

[ 3] 赵慧勤, 李秀兰. Oracle 数据库应用系统的优化策略 . 计算机工程与应用,

[ 4] 刘博. Oracle 数据库性能优化与调整 大连: 大连理工大学, 2007

[ 5] 谷小秋, 李德昌. 索引调整优化Oracle9i 工作性能研究

[6] . 计算机工程与应用, 2005,

作者简介：任凤 ，女 ，移动通信工程师，出生日期：19xx年8月20日，工程硕士，目前主要从事CDMA网络资源管理工作，研究方向数据挖掘。现工作在中国电信股份有限公司石嘴山分公司，邮编：753000，联系电话：189xxxxxxxx