Oracle中常用的查询优化方法.doc

Oracle中常用的查询优化方法 　　摘要：在数据库的使用过程中，查询是一个非常重要的部分，人们在使用SQL时往往太关注于所得到的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型或是复杂的数据库环境中表现得尤为明显。该文就常使用的查询优化方法进行阐述。 　　关键词：查询；优化Oracle 　　中图分类号：TP311文献标识码：A文章编号：1009-3044(2011)29-7095-03 　　SQL语句是对数据库(数据)进行操作的惟一途径；消耗了70%~90%的数据库资源；独立于程序设计逻辑。相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。而在SQL语句中耗费时间较多的就是查询语句，查询处理的代价通常取决于磁盘访问，磁盘访问的速度比内存访问慢很多。对于一个给定的查询，通常会有多种写法，选择一种优化的方法可大幅度提高查询的效率。下面就常用的查询优化方法进行阐述。 　　1 书写习惯 　　为了不重复解析相同的SQL语句，在第一次解析之后，Oracle将SQL语句存放在内存中。这块位于系统全局区域SGA(System Global Area)的共享池中的内存可以被所有的数据库用户共享。 因此，当你执行一个SQL语句时，如果它和之前的执行过的语句完全相同，Oracle就能很快获得已经被解析的语句以及最好的执行路径。大大地提高了SQL的执行性能并节省了内存的使用。但只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。 　　因此写SQL语句时，应保持固定的SQL书写习惯，相同的查询尽量保持相同的形式，包括字母的大小写、标点符号、换行的位置等。 　　使用动态SQL列引用*是一个方便的方法，但这是一个非常低效的方法。Oracle在解析的过程中, 会将*依次转换成所有的列名, 这个工作是通过Oracle多表查询数据字典完成的, 这将耗费更多的时间。 　　2 常量优化 　　常量的计算并不是每次执行之时，而是在语句被优化时一次性完成。下面是检索年薪大于36000的方法：1）sal 3000*122）sal 360003）sal/12 3000 　　如果是第一种情况，优化器会简单地把它转变成第二种。而第三条语句会对每一行进行计算。因此，尽量不要将字段置于表达式当中，否则没有办法优化。尤其当sal上有索引时，第三种方式将进行全表扫描。 　　3 操作符优化 　　3.1 IN 操作符优化 　　在值不多的情况下，尽量使用“=”和“OR”来替换IN。如，可把表达式ename IN (FORD,KING,JONES)替换为ename = FORD OR ename = KING OR ename = JONES。 　　对于连续的数值，能用 BETWEEN就不要用IN。如age IN（20,21,22,23,24）,可使用BETWEEN 20 AND 24来替代。 　　当子查询使用IN时，为了提高效率，可以使用EXISTS来替代，如图1（a）（b）所示。（a）所在子查询中进行了全表扫描，而（b）进行的是部分扫描。几乎所有的IN操作符子查询都可以改写为使用EXISTS的子查询。在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询，而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。也不建议使用NOT IN，因为其不能应用表索引，可以使用NOT EXISTS代替。 　　3.2 ANY和ALL 操作符优化 　　可将跟随值列表的ANY检索条件用等价的同等操作符和“OR”组成的表达式替换。如sal ANY (1000, 2000)可替换为sal 1000 OR sal 2000。 　　可将跟随子查询的ANY检索条件转换成由“EXISTS”和一个相应的子查询组成的检索表达式。如x ANY (SELECT sal FROM emp WHERE job = ANALYST)可替换为 EXISTS (SELECT sal FROM emp WHERE job = ANALYST AND x sal)。 　　可将跟随值列表的ALL操作符用等价的“=”和“AND”组成的表达式替换如sal ALL (l000, 2000)表达式会被替换为sal1000 AND sal2000。 　　对于跟随子查询的ALL表达式，可使用ANY和另外一个合适的比较符组成的表达式替 　　换。如 xALL (SELECT sal FROM emp WHERE deptno = 10)可替换为NOT (x = ANY (SELECT sal FROM emp