ORACLE-SQL优化

ORACLE 采用两种访问表中记录的方式:http://blog.csdn.net/zhangao0086/article/details/6250777

a.  全表扫描

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

b.  通过ROWID访问表

你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息.ORACLE采

用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,

因此那些基于索引列的查询就可以得到性能上的提高.

---

现在来说一些能够增加效率的方法,为了方便测试,我建立了两个测试表:

IDS_EMP:,EMPNO上有索引,表中有100W条记录

IDS_DEPT,DEPTNO上有索引,表中记录:

并且为了保证结果更加真实,在每次执行语句之前,都清空共享池:

alter system flush buffer_cache(注意:需要权限!)

1.选择最有效率的表名顺序(只在基于规则的优化器中有效)

首先先设置规则的优化器,使用语句:

alter system set optimizer_mode=RULE;

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(我们称它为驱动表或基础表,driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

选择记录多的DES_EMP作基础表进行表连接:

选择记录少的DES_DEPT作基础表进行表连接:

注:我是执行完之后,才把select语句选上,目的是方便大家看.

2.WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

高效:

低效:

3.SELECT子句中避免使用*

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*' 是一个方便的方法.不幸的是,这是一个非常低效的方法.   
实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

4.计算记录条数

和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)   
(在论坛中曾经对此有过相当热烈的讨论, 这个观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别).

设置基于规则的优化器:alter system set optimizer_mode=RULE

5.用WHERE子句替换HAVING子句

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

高效:

低效:

(HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

6.使用表的别名(Alias)

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.  
(Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

7.用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

设置基于规则的优化器:alter system set optimizer_mode=RULE

8.用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

9.用表连接替换EXISTS

通常来说 , 采用表连接的方式比EXISTS更有效率

10.用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用 DISTINCT. 一般可以考虑用EXIST替换

11.常量的计算是在语句被优化时一次性完成的,而不是在语句每次执行时

假设要检索月薪大于2000的表达式:

sal > 24000/12  
sal > 2000   
sal*12 > 24000

如果SQL语句包括第一种情况，优化器会简单地把它转变成第二种。  
优化器不会简化跨越比较符的表达式，例如第三条语句，鉴于此，应尽量写用常量跟字段比较检索的表达式，而不要将字段置于表达式当中。否则没有办法优化，比如如果sal上有索引，第一条和第二条就可以使用(执行索引)，第三条就难以使用,因为它会把(sal*12)当作一个字段,于是不识别索引

12.IN、OR子句常会使用工作表,使索引失效

如果不产生大量重复值，可以考虑把子句拆开。拆开的子句中应该包含索引。

13.消除对大型表行数据的顺序存取

在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询 10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。  
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的WHERE子句强迫优化器使用顺序存取。

使用设置基于基于成本的优化器来测试:alter system set optimizer_mode=ALL_ROWS scope=both

下面的查询将强迫对ide_emp表执行顺序操作：

注:UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

14.避免相关子查询

一个列的标签同时在主查询和WHERE子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。