成绩：
Oracle数据库系统应用
大作业
班级：
学号：
姓名：
指导教师：
日期：
软件1101 201109020102 仇文博 黄建才 2014.5.10
基于Oralce数据库的SQL查询的优化
摘要：现代社会科技的发展日新月异，各行各业都开始应用计算机管理软件提升工作效率。数据库的设计是各个管理软件的设计与实现中必不可少的，很多领域都有数据库信息存储与信息处理的技术应用。Oracle 数据库是当前比较流行的数据库，SQL 作为通用的、功能极强的通用数据库语言如果得到更好的优化，将使软件性能更加优化。本文主要从对于Oracle 数据库SQL 查询优化的重要性着手，从索引以及查询语句等优化方式进行分析，并且总结了一些Oracle 数据库SQL 查询优化技巧。
关键词：Oracle；SQL语句；查询；优化。
1 引言
数据库管理系统是介于软件系统用户与操作系统两者间的一层负责数据管理的软件，是软件的灵魂，任何软件的运行都缺少不了后台数据库的支持。Oracle 数据库是数据库系统的一种，应用较为广泛。SQL 语言具有建立数据库、查询、维护等功能，其中，尤以查询功能应用较为普遍。当软件数据库中的数据达到一定量时，对于这些成千上万条的数据信息查询，就必须要掌握SQL 语言的优化。 2 SQL语句优化
SQL 语句尤其是复杂SQL语句的性能优化对于数据库的性能是至关重要的。实际数据库使用过程中80 %的性能问题是由于用户使用了不恰当的SQL 查询语句造成的, 因此优化SQL 语句可以提升整个系统的运行效率。在集中式数据库中, SQL 查询的执行总代价=I/O 代价+PU 代价+内存代价。调整影响其执行效率的三大因素来减少系统总代价:一是减少查询所产生的I/O 总次数;二是减少CPU的计算频度, 减少SQ L 语句中需要计算的量和参数;三是减少对系统内存的使用和占用时间。
2.1 检查不合理的SQL语句
要检测出不合理的SQL 语句, 首先要生成执行计划, 最简单的办法有两种:一是SQL >set autotrace on 自动记录执行计划;二是explain planfor ‘SQL 语句\' , 然后通过select * from table(dbms xplan .display())来查看执行计划。第一种方法查看执行时间较长的SQL 语句时, 需要等待该语句执行成功后才返回执行计划。如果只想得到执行计划可以采用explain plan 或者SQL >setautotrace traceonly , 它们不会真正的执行语句。通过执行计划来确定不合理的SQL语句, 为SQL优化做准备。
2.2 共享SQL语句
在第一次解析之后,Oracle 将SQL语句存放位在系统全局区域(SGA)的共享池中, 它可以为所有的数据库用户共享, 大大地提高了SQL 的执行性能并节省
了内存。当用户提交一个SQL语句时, 服务器进程在共享池中查找有无该条语句,如果有就跳过语法分析等过程, 节省了SQL语句的分析和编译的开销。只有在共享池中不存在等价SQ L 语句的情况下, 才对该语句作语法分析, 并为该语句分配新的共享SQL 区。对于编程者来说,要尽量提高SQL语句的重用率, 尽量使用Bind 变量, 来减少语句的分析时间。但是Oracle 对语句的匹配是相当严格的, 要达成共享, 它要遵循三条规则:
1） SQL 语句必须完全相同(包括空格、换行等)。下列SQL 语句都是不同的, SELECT *from EMP ;Select * From Emp ;SELECT *FROM EMP ;
2)语句中的对象必须完全一致。
3)语句中的绑定变量必须使用相同的名字。
例如:下面的两个SQL 语句是相同的,select ename from emp where empno =emp .no 和select enam from emp where empno =emp .no 。
2.3 基于索引的优化
索引是影响SQL语句性能的一个重要因素, 在表上建立合适的索引, 可以避免全表扫描并减少I/O 开销, 提高数据查询速度。但是创建索引会增加系统时间和空间的开销, 创建索引时必须要与实现应用系统的查询需求密切结合, 才能达到优化查询的目的。
2.3.1 索引的可选择性
如果该列的“唯一键的数量/表中的行数”的比值越接近1 , 也就是索引的可选择性也越高,则该列就越适合创建索引。在可选择性高的列上进行查询返回的数据较少, 提高SQL 查询效率。
2.3.2 索引使用的一些原则
1)当选择数据少于全表的20 %, 并且表的大小超过ORAC LE 的5 个数据块时, 使用索引才会有效, 否则用于索引的I/O 加上用于数据的I/O 就会大于做一次全表扫描的I/O , 反而会降低查询效率;
2)由于表和索引的数据块通常是被同时读取的, 所以应该尽量将表和其相关联的索引放置在不同的磁盘上, 减少I/O 冲突;
3)索引的创建也是需要代价的, 对于DML 操作, 每个索引都要进行相应的删除、更新、插入操作。从而导致DML 操作的效率变低。因此定期的重构索引是有必要的, alter index rebuild ;
4)避免隐式转换和不必要的类型转换。例如:to char(), to number(), to date()等会导致该字段的索引失效, 从而对该表进行全表扫描, 对一个百万数量级的表进行全表扫描的会对系统带来严重的性能隐患;
5)避免在索引列上使用i s null 和is no t null ,且不要在索引的字段上面进行任何操作(包括函数操作)。例如select * from emp where comm isnull 会使索引失效;select * from emp where sal*12 >60000 改写成select * from emp where sal>60000/12 才能使用sal上的索引;
6)存取多列, 且每列都含有重复值可考虑建立组合索引;组合索引要尽量使
查询形成索引覆
盖, 其引导列一定是使用最频繁的列。如createindex index emp on emp(empno , ename),则indexemp 为组合索引, 其中empno 为引导列。当我们进行查询时可以使用“where empno =?” , 也可以使用“where empno = ? and ename = ?” , 这样的限制条件都会使用索引, 但是“where ename =?”查询就不会使用该索引。只有where 子句中包含引导列时, 查询才会使用组合索引。
2.4 别名的使用
别名是Oracle 数据库的应用技巧之一。但在实际应用中, 它常常被忽略, 当在SQ L 语句中连接多个表时, 使用表的别名并把别名前缀于每个Column上, 就可以减少解析的时间并减少那些由Column歧义引起的语法错误。在人事管理系统数据表teacher 表中, 表有37 列, 共有2500 条记录, 最长列名(如:eduw o rking time 表示教育工作时间)为14 , 其它列名多数大于4 个字母, 查询时用一个字母替代表名和列名, 最后查询人员信息时速度提高了1 .8s 。
3 优化技巧与实例
近年来的软件数据库系统应用中，有一些常用的技巧，总结如下:
1.减少访问数据库的次数就等于减少了oracle数据库的工作量。oracle 数据库执行SQL 语句语句命令时，需要作出解析语句、读数据块信息、估算索引使用效率等等相关的内部执行工作，是很需要时间的。所以，尽量减少对数据库的访问次数就能提高优化效率。
2.避免使用select * 。用动态SQL 列引用‘* ’是最方便的可以在SELECT 子句中列出所有的COLUMN 的方法，但是，这种方法确实效率最低的。因为oracle 数据库在解析的过程中，将会通过数据字典的查询将’* ’依次转换成所有的列名，这些步骤将会浪费很多宝贵的时间。
3.WHERE 子句中的连接顺序。根据oracle 数据库系统将会采用自下而上的顺序解析WHERE 子句的原理，可以过滤掉最大数量记录的条件要写在WHERE 子句的末尾，而各个数据表之间的连接必须写在其他WHERE 条件之前，可以使用TRUNCATE替代DELETE。可以采取回滚段存放删除表记录后想要恢复的信息。
4.适当的使用commit。程序的性能得到提高，需求也会因为COMMIT 所释放的资源而减少COMMIT所释放的资源: 回滚段上用于恢复数据的信息、被程序语句获得的锁、redo log buffer 中的空间、oracle为管理上述3 种资源中的内部花费在使用COMMIT时必须要注意到事务的完整性，现实中效率和事务完整性往往是鱼和熊掌不可得兼，另外过于频繁的使用commit 效率也会降低。
5.用NOT EXIST、表等替换NOT IN。NOT IN，NOT EXIST 的应用也是SQL 语句优化的关键。在子查询中用NOT IN 子句执行合并和排序时，NOT IN由于要对查询表中要经历一次全表遍历的查询，所以执行效率非常低。因此，我们可以把NOT IN 改成外连接( Outer Joins) 或NOT EXISTS。 这样就能够避免使用低效的NOT IN 了。还可以用表连接连替换EXIST 来提高效率，例如下面一段程序的SQL 查询语句就是这样做的:
SELECT ENAME
FROM EMP E
WHERE EXISTS ( SELECT‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’) ;
( 更高效)
SELECT ENAME
FROM DEPT D，EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = ‘A’;
4 结论
Oracle 数据库的性能优化相当重要, 但调整起来难度也较大, 需要综合运用上面介绍的方法, 并在数据库建立时, 根据应用需要合理设置内存初始化参数。对于Oracle 数据库应用系统来说, SQL查询语句的性能优劣直接影响整个信息系统的效率, 效率高的查询语句和效率低的查询语句速度相差可以达到上百倍。只有认真分析Oracle 运行过程当中出现的各种性能问题, 才能保证Oracle 数据库高效可靠地运行。
参考文献
[1] ( 澳) Guy Harrison 哈里森著，郑勇斌，胡怡文，童家旺译， oracle 性能优化求生指南［M］． 北京: 人民邮电出版社． 2012
[2] Donald K.Burleson.刘砚, 黄春, 译.Oracle高性能SQL调整[M].北京: 机械工业出版社, 2002.
[3]盖国强循序渐进oracle: 数据库管理、优化与备份恢复［M］． 北京: 人民邮电出版社． 2011.
