oracle sql语句优化

本文参考自Oracle+SQL优化一书

1.在 where 子句中使用了 is null 或 is not null，那么 索引就会失效

2.带通配符的like语句会导致索引失效
select * from employee where last_name like ‘%cliton%’;

但是%不出现字符串的起始位置时索引依旧有效
select * from employee where last_name like ‘c%’;

3.任何在 Order by 语句的非索引项或者有计算表达式都将降低查询速度。

<>导致索引失效
select * from employee where salary<>3000;
select * from employee where salary<3000 or salary>3000;
虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许 Oracle 对 salary 列使用索引，而第一种查询则不能使用索引。

4.使用exists的比使用in的效率高很多
… where column in(select * from … where …);
… where exists (select ‘X’ from …where …);
NOT EXISTS 也要比 NOT IN 查询效率更高

5.假设
表 TAB1 有16,384 条记录
表 TAB2 有1 条记录
select count(*) from tab1,tab2 执行时间 0.96 秒
select count(*) from tab2,tab1 执行时间 26.09 秒
原理：oracle解析表的顺序是从右往左的，将小表放在右边可以提高效率

6.ORACLE 采用自下而上的顺序解析 WHERE 子句,根据这个原理,表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾.
(高效,执行时间 10.6 秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;

(低效,执行时间 156.3 秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);

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

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

9.利用ROWID高效删除表中重复数据
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);

10.只要有可能,在程序中尽量多使用 COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT 所释放的资源而减少:
COMMIT 所释放的资源:
a.回滚段上用于恢复数据的信息.
b.被程序语句获得的锁
c.redo log buffer 中的空间
d.ORACLE 为管理上述 3 种资源中的内部花费

11. 用 Where 子句替换 HAVING 子句
避免使用 HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处
理需要排序,总计等操作. 如果能通过 WHERE 子句限制记录的数目,那就能减少这方面的开销.
例如:
低效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
高效:
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY’
AND REGION != ‘PERTH’
GROUP BY REGION
(译者按: HAVING 中的条件一般用于对一些集合函数的比较,如 COUNT() 等等. 除此而外,一
般的条件应该写在 WHERE 子句中)

12.在含有子查询的 SQL 语句中,要特别注意减少对表的查询.

13.通常来说 , 采用表连接的方式比 EXISTS 更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT_NO = E.DEPT_NO
Oracle SQL 优化
POSS 软件平台（www.poss.cn） 22
AND DEPT_CAT = ‘A’);
(更高效)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO
AND DEPT_CAT = ‘A’ ;

14.避免在索引列上使用计算
WHERE 子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;

15.自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．
在这种情况下，ORACLE 将使用唯一性索引而完全忽略非唯一性索引．
举例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ;
这里，只有 EMPNO 上的索引是唯一性的，所以 EMPNO 索引将用来检索记录．
TABLE ACCESS BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX

16.避免在索引列上使用 IS NULL 和 IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE 将无法使用该索引 ．对于单列索引，如果
列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此
记录. 如果至少有一个列不为空，则记录存在于索引中．
举例:
如果唯一性索引建立在表的 A 列和 B 列上, 并且表中存在一条记录的 A,B 值为(123,null) ,
ORACLE 将不接受下一条具有相同 A,B 值（123,null）的记录(插入). 然而如果
所有的索引列都为空，ORACLE 将认为整个键值为空而空不等于空. 因此你可以插入 1000
条具有相同键值的记录,当然它们都是空!
因为空值不存在于索引列中,所以 WHERE 子句中对索引列进行空值比较将使 ORACLE
停用该索引.
举例:
低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;

17.避免改变索引列的类型.
当比较不同数据类型的数据时, ORACLE 自动对列进行简单的类型转换.
假设 EMPNO 是一个数值类型的索引列.
SELECT …
FROM EMP
WHERE EMPNO = ‘123’
实际上,经过 ORACLE 类型转换, 语句转化为:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER(‘123’)
幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设 EMP_TYPE 是一个字符类型的索引列.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
这个语句被 ORACLE 转换为:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到!
为了避免 ORACLE 对你的 SQL 进行隐式的类型转换, 最好把类型转换用显式表现出来.

18.注意当字符和数值比较时, ORACLE 会优先转换数值类型到字符类型.

19.对索引列使用了！= || +都会导致索引失效
不使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >0;

不使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
AND ACCOUNT_TYPE=’ A’;

不使用索引
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000;
使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ;

20.优化 GROUP BY
提高 GROUP BY 语句的效率, 可以通过将不需要的记录在 GROUP BY 之前过滤掉.下面两个
查询返回相同结果但第二个明显就快了许多.
低效:
SELECT JOB , AVG(SAL)
FROM EMP
GROUP JOB
HAVING JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
高效:
SELECT JOB , AVG(SAL)
FROM EMP
WHERE JOB = ‘PRESIDENT’
OR JOB = ‘MANAGER’
GROUP JOB