oracle数据库查询优化方法研究

Oracle数据库查询优化方法姓名：孙映川学号：1611314025摘要Oracle数据库是当前应用最广泛的大型数据库之一,其系统结构复杂,性能受多方面因素影响,其中查询操作是影响其性能的关键因素。为了提高Oracle数据库查询效率,通过分析Oracle处理查询语句的过程以及优化器的工作原理,结合实例,讨论了Oracle数据库查询优化的多种有效方法。Oracle查询语句的处理Oracle数据库应用系统通过SQL语言与数据库进行通信,数据库管理系统通过执行用户提交SQL语句完成查询过程。具体的查询操作包括解析、执行和提取数据三个阶段解析解析是检查SQL语句的语法和语义,最终生成SQL语句的执行计划,并将SQL语句和执行计划存放到共享池中。在解析期间,先搜索库高速缓存检查是否存在相同的SQL语句,若存在则直接执行该SQL语句。否则检查SQL语句的语法、语义以及用户访问对象的权限,最后交给查询优化器,由查询优化器确定SQL语句的最佳执行计划。执行

执行(Execute)是指服务器进程按照SQL语句执行计划执行SQL语句。执行计划是指执行这个查询所需要的一系列步骤,主要包括访问数据表的方式,表的连接次序,表的连接方式,以及各个步骤执行的先后顺序等。提取数据提取数据(fetch)是指将被选择行数据返回到客户端Oracle优化器工作原理相同的一条查询语句可以有许多不同的执行计划,CostBased优化器将计算各种执行计划的开销,然后选出最低成本的执行计划。一个查询的开销可以被分成3个基本组成分:I/O代价、CPU代价、Network代价。使用CBO优化器要定期对表和索引进行分析,为优化器收集必要的统计数据。查询优化方法

只有在共享池中不存在等价SQL语句的情况下,才对SQL语句作语法分析。在这种情况下,数据库内核重新为该语句分配新的共享SQL区,并对语句进行语法分析共享SQL语句

新的查询语句要与共享池中的语句相匹配,必须严格遵守下面三条规则(1)语句之间必须满足字符与字符相匹配(包括字符的大小写）(2)语句中的对象必须完全一致SELECTmax(sal)FROMsallimit其中sallimit对象的访问方式包括私有同义词(privatesynonym)、公有同义词(publicsynonym)、表的拥有者(tableowner),如果两个用户都通过公有同义词访问,而且都不是表的拥有者,则两个用户访问的是相同的对象,这条查询语句可以共享,否则属于不同的对象,不可以共享。(3)语句中必须使用相同命名的绑定变量下面的SQL语句前两个可以共享,后两个不同,即使在运行时赋予不同的绑定变量以相同的值。SELECTpin,posid,nameFROMpeopleWHEREpin=:blk1.pin;SELECTpin,posid,lnameFROMpeopleWHEREpin=:blk1.pin;SELECTposid,salcapFROMsallimitWHEREovertime=:blk1otind;SELECTposid,salcapFROMsallimitWHEREovertime=:blk1ovind;合理创建和使用索引

索引的创建需要维护,当对表中的数据进行insert/update/delete操作时,索引中的数据也会随之更新。因此,对于索引的使用,要注意以下原则:(1)该字段是否为关键词,表的主键和外键必须有索引;(2)该字段是否为连接操作字段,对经常与其他表进行连接的表的连接字段应该建立索引;(3)该字段出现在where子句的频率,经常出现在where子句中的字段应该建立索引;(4)选择性高的字段应该建立索引。索引的选择性是指索引列里不同值的数目与表中记录数的比;(5)对数据库的插入、删除操作频繁,不要建立过多索引;(6)对于在查询期间通常会被选择的列,而且重复值比较多,可以创建复合索引,这样查询所要求的所有数据可以通过索引访问提供,避免了索引扫描随后的表访问。创建索引后,如果SQL语句在下列情况下,优化器生成的执行计划也不会包含索引,应该避免下列情况的发生：当对一个表中的的两个列进行比较的情下,索引有时不会被使用例如查询:SELECT＊fromempWHEREempno<mg；NULL值一般情形下,索引中并不存在空值如果WHERE语句中出现了isnull或者isnotnull时,索引就不能被使用;如下查询将不会访问comm列上的索引:SELECT＊FROMempWHEREcommisnotnull(3)WHERE语句中使用不等于操作符(<>、!=);SELECTcustid,custnameFROMcustomersWHEREcustrating<>'aa';可以改写为:SELECTcustid,custnameFROMcustomersWHEREcustrating<'aa'orcustrating>′aa′;(4)当使用通配符%或者作为查询字符串的第一个字符时;(5)当对操作的列使用函数时;SELECTempno,ename,jobFROMempWHEREupper(job)=′SALESMAN′;可以创建基于函数的索引来优化此查询。重写SQL语句使用WHERE代替HAVING。HAVING从句是限制由GROUPBY从句返回的行,仅在汇总函数执行之后,WHERE从句在汇总之前就减少参加汇总的数据量,从而提高查询的速度。需要注意的是,注意HAVING从句应用了汇总函数,则不能用WHERE代替。

(2)使用UNIONALL代替UNION。UNION要求进行排序操作来消除两组行中所有重复的行,而UNIONALL会返回所有行(即使有些行是重复的)。如果重复行不重要,则使用UNIONAL可以避免潜在的消耗大量资源的排序、合并过滤操作。创建位图索引位图索引是一种索引结构,在数据库中存储压缩的用位图表示的索引,位图索引占用空间少,对空间的节约又可以转化为性能上的好处,表现为磁盘I/O的减少。位图索引对于WHERE子句的AND、OR、NOT运算非常快优化器提示随着时间的推移和数据量的增大,对于复杂的查询,优化器并不总是做出最佳的选择,我们除了注意定期对表进行分析外,还可以使用优化器提示来指定是否使用索引、表的连接方式以及连接次序等,使其以另外一种更佳的方式来执行查询。