基于ORACLE的查询优化

科技信息博士●专家论坛基于ORACLE的查询优化宁波大红鹰职业技术学院唐兴通危烽吉林大学软件学院危明摘要]Oracle数据库系统性能优化是一个复杂的系统工程。本文论述了通过Oracle数据库查询的工作机制,并分析了-些影响应用性能的可能因素,主要从SQL语句的方面优化进行阐述,给出了--些可行的调整数据库应用性能的策略与方法。[关键词]ORCLE数据库优化 索引RDBMSORACLE是一个优秀的大型关系型数据库,支持关系对象下来对输出的SQL语句进行过滤,选出具有不同执行计划的模型的分布式数据库产品。目前在银行.电信、证券市场的数据SQL语句(不同的执行计划意味着不同的执行效率),最后,对库方面占有绝对的优势。具权威部门的统计,在全世界的范围得到的SQL语句进行批量测试,找出性能最好的SQL语句内,ORACLE市场占有率为46. 1%是SQLSERVER的7倍,而ORACLE的优化器共有3种: a. RULE (基于规则) bIBM的DB2市场占有率也只有23.6%。一个选用ORACLE为COST (基于成本) c. CHOOSE (选择性).数据库的系统的规模一般比较大,用户-般比较多而且空间上基于开销在基于开销的优化策略中(CBO),对于给定查询比较分离,事务处理的效率对于这样的系统来说是至关重要的,生成多个执行计划,然后对每个计划估算开销查询优化程序选但是在实际应用中,随着系统数据库中数据的增加,访问量的加用估算开销最低的计划,(代价= I/O代价+ CPU代价+内大，数据库系统性能将会下降。这样,就需要我们数据库管理人存代价)。员对数据库进行性能调整,使数据库系统能够在合理、可靠、低另外基于规则的优化程序(RBO)不同于基于开销的优化程风险和高效率的状态下运行。序(CSO)。它按照-系列既成的规则来对SQL语句进行分析，数据库应用的性能调优是一个系统工程。在实践中必须分从而得出较好的执行策略。RBO在分析过程中不考虑开销因析影响性能的各方面因素.针对不同的具体情况选择合理的优素。实际应用中-般不考虑化措施,对于ORACLE来说性能的优化主要有:内存分配的优数据库的反应速度经常体现在优化器的优化算法上。此外，化,1/O的调整,SQL语句的优化与调整以及数据库各种动态参优化器的优化策略还与所查询表的内容和其他一些与服务器有数的调整。本文主要探讨有关SQL语句的优化与调整的内容。关的因素有关,如Cache大小.Cache策略.I/O大小等。通常来.数据库性能优化目标就是使数据库应用运行得更快。“更说硬盘访问是成本最高的操作。快”通常指的是更高的吞吐量或者说是更短的应用响应时间。数2.数据库性能优化目标就是使数据库应用运行得更快据库性能优化的基本原则是通过尽可能少的磁盘访问获得所需“更快”通常指的是更高的吞吐量或者说是更短的应用响应要的数据。时间。SQL语句优化的要点，就是尽量减少页面存取，或使用内实际数据库使用过程中大部分的性能问题是由于程序员或存的页面代替访问磁盘。在查询优化过程中要对语法树中的每用户使用了不恰当的查询语句造成的,甚至达到80%以上的问.一个操作,计算各种执行算法的执行代价，选择代价小的执行算题,因此SQL语句的质量对整个系统效率有相当影响。SQL语法。其中就要涉及到从关系代数方面的一些优化规律:句优化的要点，就是尽量减少页面存取,或使用内存的页面代替(1)选择操作是根据条件对关系做水平分割,即选取符合条访问磁盘。件的元组。应尽可能早地执行选择操作,以得到较小的中间关我们要想很好的处理好查询优化,可以借鉴一些目前商品系,这样可以减少运算量和读外存的次数化的优化器中,它们是如何进行处理的，这样我们在用户书写查(2)把笛卡尔积和选择操作合并成F联接运算,也就是做询时就有个好的思路和方法。笛卡尔积操作时立即检查联接后的元组是否满足选择条件,并.1. RDBMS优化器决定取舍,以减少系统开销。随着人工智能技术的发展和在数据库优化领域应用的深(3)选择和投影都是--元操作符,而关系的元组是独立的单入,在20世纪90年代末优化技术取得了突破性的进展,出现了位,可以对每个元组连续做-串操作,即同时计算-连串的选择人工智能自动SQL优化。人工智能自动SQL优化的本质就是和投影操作,以免多次扫描文件,从而节省时间。借助人工智能技术,自动对SQL语句进行重写,找到性能最好(4)如果在-个表达式中多次出现-个子表达式,应该将此的等效SQL语句。像LECCO SQL Expert就采用了这种人工子表达式预先计算出结果并保存起来,以免重复计算。智能技术,其SQL Expert支持Oracle .Sybase、MS SQL Server(5)根据具体情况对文件进行排序或建立索引文件,即对关和IBMDB2数据库平台。其突出特点是自动优化SQL语句。能.系文件进行预处理,可以提高关系联接的速度。通过比较源SQL和待选SQL的不同之处,为开发人员提供“边(6)当查询涉及多个关系时最好使用嵌套结构,因为嵌套查做边学式训练”,迅速提高开发人员的SQL编程技能等。询的执行效率比联接查询的笛卡尔积的效率高。“SQL 语法优化器”的工作原理大致如下:输入一条初始中国煤化工意的问题SQL语句，“人工智能反馈式搜索引擎"对输入的SQL语句结最有效的方法之一合检测到的数据库结构和索引进行重写,产生N条等效的SQLMYHCNMH G校,是优化的基础,建立索引语句输出,产生的N条等效SQL语句再送入“人工智能反馈式的根本目的是提高查询效率。不论怎样优化都会充分利用索引搜索引擎”进行重写,直至无法产生新的输出或搜索限额满,接进行记录定位,减少磁盘的读写次数,从而达到提高查询速度的基金项目:本文获得宁波大红鹰职业技术学院软件学院的青年基金的赞助。(06-2070821高维数据的聚类分析)-7-科技信息博士●专家论坛目的。-个建有合理索引的数据库应用系统可能比一个没有建数据,而不是让用户继续等待。立索引的数据库应用系统效率高几+倍,但并不是索引越多越3.6使用显式的游标(Cursors)好，在那些经常需要修改的数据列上建立索引,将导致索引B*使用隐式的游标将会执行两次操作:第1次检索记录;第2树的不断重组,造成系统性能的下降和存储空间的浪费。次检查TOOMANYROWS这个EXCEPTION。而显式游标不.(1)应对经常出现在检索条件中的数据域建立索引,如果执行第2次操作,速度提高。某些数据域经常-起出现在检索条件中,那么建立复合索引。3.7 LIKE关键字的处理(2)在频繁进行排序或分组(即进行group by或order by例如:学生表中要查询2005级学生的情况: SELECT:操作)的列上建立索引。应对经常用于表连接操作中的数据域FROM学生WHERE XH LIKE“05___” 即使在xh字段建立索引,如果是多域连接则建立复合索引。上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果(3)在条件表达式中经常用到的不同值较多的列上建立索把语句改为SELECT * FROM学生WHERE XH >“05000”引,在不同值少的列上不要建立索引。比如在雇员表的“性别”列在执行查询时就会利用索引来查询,显然会大大提高速度。上只有“男”与“女"两个不同值,因此就没有必要建立索引。如果3.8使用连接,避免对数据表的顺序存取建立索引不但不会提高查询效率,反而会严重降低更新速度。在嵌套查询中,对表的顺序存取可能会采用顺序存取来进(4)对于复合索引,索引中的数据域的顺序要和实际应用的行,我们可以使用并集来避免顺序存取。虽然在所有的检查列上检索和连接顺序保持一致。都有索引,下面的查询将对学生表执行顺序操作:SELECT*(5)不要在索引中包含经常修改、增加和删除的列。FROM学生WHERE (TSH = 00810 AND XH> 05000) OR(6)ORACLE在外键上并不建立索引,因此在利用外键连XH=04008虽然在TSH和XH上建有索引,但是这语句中仍接的表格中，外键上也应该建立索引。然使用顺序存取路径扫描整个表。如果改为:SELECT(7)很少在where子句中出现的列,不宜建立索引。FROM学生WHERE TSH = 00810 AND XH> 05000 UNION(8)不能用null作索引,任何包含null值的列都将不会被SELECT*FROM学生WHEREXH=04008,可以避免顺序包含在索引中。也就是说如果某列存在空值,即使对该列建索引存取,是高效的。也不会提高查询效率。3.9SELECT子句中避免使用.(9)对查询型的表,建立多个索引会大大提高查询速度,对如果我们要选取表中所有的列，常用的方法就是使用* ,其更新型的表,如果索引过多,会增大开销。实这样的操作是低效果的，日常使用过程中如果表中的列没有ORACLE采用两种访问表中记录的方式:1.全表扫描,全几个,最好把这几个字段直接写出来。表扫描就是顺序地访问表中每条记录。ORACLE采用一次读结束语入多个数据块(database block)的方式优化全表扫描。在海量数以上只是一些常用的优化ORACLE数据查询的方法,其据库中要避免。2.通过ROWID访问表,你可以采用基于他还有好多像对数据表的切割，使用存储过程,避免不兼容数据ROWID的访问方式情况.提高访问表的效率, ROWID包含了的使用等等。总体说来ORACLE数据库的优化是一个动态,系表中记录的物理位置信息。ORACLE采用索引(INDEX)实现统的过程。仅仅调整某一方面,不考虑整体情况很难收到预期的了数据和存放数据的物理位置(ROWID)之间的联系。通常索引效果。对于查询是这样，对于整个系统也是同样的道理。因此,对提供了快速访问ROWID的方法,因此那些基于索引列的查询ORACLE相关的查询调整和优化,需要我们不断总结经验还得就可以得到性能上的提高。结合特定的系统来进行操作。3.2在WHERE子句中使用子查询时尽量使用EXISTS而参考文献不是IN。[1]William G Page等著;王磊,蒋蕊,王众等译. Oracle 8/在使用EXIST中，DBMS系统会首先检查主查询,然后运8i开发使用手册[M].北京:机械工业出版社,2000.行子查询直到找到第-个匹配项,这就节省了时间。DBMS系统[2]Marlene Theriault, Rachel Carmichael. Oracle 数据库在执行IN子查询时,首先执行子查询,并将获得的结果列表存管理员基础教程[M].北京:机械土业出版社,2000.放在一个加了索引的临时表中。待子查询执行完毕,存放在临时[3]George KOCh、 Keviri Loney等著;梅刚等译. Oracle8表中以后再执行主查询。这也就是使用EXISTS比使用IN通常完全参考手册(四)[M].北京:机械工业出版社,1998..查询速度快的原因。[4]Singh, Leigh, Zafianet al等著;史森、夏丽丽等译. Or-3.3尽量多使用COMMITacle数据库开发指南[M].北京:清华大学出版社,1998.在自动事务提交模式下,每执行一个SQL语句就进行一次[5]翁正科,王新英. racle8.X For Windows NT实用教程事务提交,程序的开销很大。可以通过取消自动提交模式,将[M].北京:清华大学出版社,1998.SQL语句汇集成-组执行后集中提交,程序还可以通过显式地[6]Jonathan Gennick , Carol McCullough - Dieter, Gerrit .用COMMIT和ROLLBACK进行提交和回滚事务。只要有可一Jan Linker著、赵艳勤,刘冠英,秦玉杰等译. ORACLE 8i能,在程序中尽量多使用COMMIT,这样程序的性能得到提DBA宝典[M].北京:电子工业出版社,2001.高，需求也会因为COMMIT所释放的资源而减少.COMMIT[7]Colbum Rafe. SQL使用全书[M].北京:电子工业出版所释放的资源: a.回滚段上用于恢复数据的信息. b. 被程序社,200苄于Oracle数据库性能优化中国煤化工语句获得的锁c. redo log buffer中的空间d. ORACLE为管理的研究YH上述3种资源中的内部花费。c N M H G军优化设计案[J].计算机系3.4用TRUNCATE替代DELETE .统应用,2001.(5):16- 18.3.5检索大量数据时费时很长,设置行预取数则能改善系.[9][美JAbraham Silberschatz, Henry F. Korth, S. Sudar-统的工作表现,设置一个最大值,当SQL语句返回行超过该值，shan.数据库系统概念.杨冬青,唐世渭等译.北京:中国机械工数据库暂时停止执行,除非用户发出新的指令,开始组织并显示业出版社,2000.--8