SQL优化

Elinc

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

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

 例如:

表 TAB1 16,384 条记录

表 TAB2 1 条记录

选择TAB2作为基础表 (最好的方法)

select count(*) from tab1,tab2 执行时间0.96秒

选择TAB1作为基础表 (不佳的方法)

select count(*) from tab2,tab1 执行时间26.09秒

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

例如:

EMP表描述了LOCATION表和CATEGORY表的交集.

1. SELECT * FROM LOCATION L , CATEGORY C, EMP E
   
2. WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN
   
3. AND E.EMP_NO BETWEEN 1000 AND 2000

将比下列SQL更有效率

1. SELECT E.CAT_NO FROM EMP E, LOCATION L , CATEGORY C
   
2. WHERE E.EMP_NO BETWEEN 1000 AND 2000
   
3. AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN

二、WHERE子句中的连接顺序

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

例如:

(低效,执行时间156.3秒) 

1. SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER

(高效,执行时间10.6秒)

1. SELECT … FROM EMP E
   
2. WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
   
3. AND SAL > 50000
   
4. AND JOB = ‘MANAGER

 

三、SELECT子句中避免使用 ‘ * ‘

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

 

四、减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量。

例如,

以下有三种方法可以检索出RMA号等于05051860或05051861.

方法1 (最低效)

1. SELECT RMA_BATCH_SYS_ID,CUSTOMER_SYS_ID,DEPOT_SYS_ID
   
2. FROM RMA_MAIN WHERE RMA_BATCH_SYS_ID = ‘05051860’
   
3. SELECT RMA_BATCH_SYS_ID,CUSTOMER_SYS_ID,DEPOT_SYS_ID
   
4. FROM RMA_MAIN WHERE RMA_BATCH_SYS_ID = 05051861 

方法2 (次低效)

1. DECLARE
   
2. CURSOR C1 (RMA_NO NUMBER) IS
   
3. SELECT RMA_BATCH_SYS_ID,CUSTOMER_SYS_ID,DEPOT_SYS_ID
   
4. FROM RMA_MAIN WHERE RMA_BATCH_SYS_ID = RMA_NO;
   
5. BEGIN
   
6. OPEN C1(05051860);
   
7. FETCH C1 INTO …,..,.. ;
   
8. OPEN C1(05051861);
   
9. FETCH C1 INTO …,..,.. ;
   
10. CLOSE C1;
    
11. END;

 方法3 (高效) 

1. SELECT A.RMA_BATCH_SYS_ID, A.CUSTOMER_SYS_ID, A.DEPOT_SYS_ID,
   
2. B.RMA_BATCH_SYS_ID, B.CUSTOMER_SYS_ID, B.DEPOT_SYS_ID
   
3. FROM RMA_MAIN A,RMA_MAIN B
   
4. WHERE A.RMA_BATCH_SYS_ID = 05051860
   
5. AND B. RMA_BATCH_SYS_ID = 05051861;

1. SELECT RMA_BATCH_SYS_ID, CUSTOMER_SYS_ID, DEPOT_SYS_ID
   
2. FROM RMA_MAIN
   
3. WHERE RMA_BATCH_SYS_ID IN (‘05051860’,’ 05051861’);

注意:

在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200。

五、使用DECODE函数来减少处理时间 

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表. 

例如:

1. SELECT COUNT(*) FROM RMA_MAIN
   
2. WHERE CUSTOMER_TRACK_NUM = ‘DELL160304’
   
3. AND CUSTOMER_SYS_ID =‘com0000391’;

 

1. SELECT COUNT(*) FROM RMA_MAIN
   
2. WHERE CUSTOMER_TRACK_NUM = ‘DELL160726’
   
3. AND CUSTOMER_SYS_ID =‘com0000391’;

可以用DECODE函数高效地得到相同结果

1. SELECT COUNT(DECODE(CUSTOMER_TRACK_NUM,'DELL160304','X',NULL)) COUNT1, COUNT(DECODE(CUSTOMER_TRACK_NUM,'DELL160726','X',NULL)) COUNT2 FROM RMA_MAIN WHERE CUSTOMER_SYS_ID ='com0000391'

 

六、用EXISTS替代IN

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

低效: 

1. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
2. WHERE DEPOT_SYS_ID='com0000003' AND CUSTOMER_SYS_ID IN (SELECT ORG_ID FROM ORGANIZATION
   
3. WHERE ORG_ENG_NAME='NEC UK Project')

高效: 

1. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
2. WHERE DEPOT_SYS_ID='com0000003' AND EXISTS (SELECT 'X'
   
3. FROM ORGANIZATION
   
4. WHERE ORG_ID = CUSTOMER_SYS_ID
   
5. AND ORG_ENG_NAME='NEC UK Project')

相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率

 补充：

有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。

 

第一种格式是使用IN操作符：

 ... where column in(select * from ... where ...); 

 

第二种格式是使用EXIST操作符：

 ... where exists (select 'X' from ...where ...); 

 我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。

 

第二种格式中，子查询以‘select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作（这里假定在where语句中使用的列存在索引）。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。

 

通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。同时应尽可能使用NOT EXISTS来代替NOT IN，尽管二者都使用了NOT，NOT EXISTS要比NOT IN查询效率更高。

 七、避免在索引列上使用计算

 WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描。

低效： 

1. SELECT ... FROM DEPT WHERE SAL * 12 > 25000;

高效:

1. SELECT ... FROM DEPT WHERE SAL > 25000/12;

 这是一个非常实用的规则，请务必牢记

 

八、用>=替代>

如果DEPTNO上有一个索引,

高效:

1. SELECT DEPTNO FROM EMP WHERE DEPTNO >=4

低效:

1. SELECT DEPTNO FROM EMP WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.

 

九、用IN来替换OR

低效:

1. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
2. WHERE CUSTOMER_SYS_ID='com0000004'
   
3. OR CUSTOMER_SYS_ID='com0000391'
   
4. OR CUSTOMER_SYS_ID='com0000003'

 

高效 

1. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
2. WHERE CUSTOMER_SYS_ID IN ('com0000004','com0000391','com0000003')

 

十、用UNION来替换OR

 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低

 

低效:

1. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
2. WHERE DEPOT_SYS_ID='com0000003'
   
3. AND (CUSTOMER_SYS_ID='com0000004' OR CUSTOMER_SYS_ID='com0000391' OR CUSTOMER_SYS_ID='com0000003')

高效:

1. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
2. WHERE DEPOT_SYS_ID='com0000003'
   
3. AND CUSTOMER_SYS_ID='com0000004'
   
4. UNION
   
5. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
6. WHERE DEPOT_SYS_ID='com0000003'
   
7. AND CUSTOMER_SYS_ID='com0000391'
   
8. UNION
   
9. SELECT RMA_BATCH_SYS_ID FROM RMA_MAIN
   
10. WHERE DEPOT_SYS_ID='com0000003'
    
11. AND CUSTOMER_SYS_ID='com0000003'

 

十一、避免在索引列上使用IS NULL和IS NOT NULL

 避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．

举例:

如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000条具有相同键值的记录,当然它们都是空!因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.

 

低效: (索引失效)

1. SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效: (索引有效)

1. SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0

 

十二、避免改变索引列的类型.

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换. 
假设 EMPNO是一个数值类型的索引列. 

1. SELECT ... FROM EMP WHERE EMPNO = '123'

实际上,经过ORACLE类型转换, 语句转化为:

1. SELECT … FROM EMP WHERE EMPNO = TO_NUMBER('123')

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.

现在,假设EMP_TYPE是一个字符类型的索引列.

1. SELECT … FROM EMP WHERE EMP_TYPE = 123

这个语句被ORACLE转换为:

1. SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123

因为内部发生的类型转换, 这个索引将不会被用到!

为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型.

 

十三、需要当心的WHERE子句

某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.

在下面的例子里, ‘!=' 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.

不使用索引:

1. SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0;

使用索引:

1. SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;

下面的例子中, ‘||'是字符连接函数. 就象其他函数那样, 停用了索引.

不使用索引: 

1. SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION
   
2. WHERE ACCOUNT_NAME||ACCOUNT_TYPE='AMEXA';

使用索引:

1. SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION
   
2. WHERE ACCOUNT_NAME ='AMEX' AND ACCOUNT_TYPE='A'

 

十五、尽量避免使用ORDER BY

ORDER BY 子句只在两种严格的条件下使用索引.

ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

ORDER BY 中所有的列必须定义为非空.

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列.