1.SQL优化规则
1.1索引的使用原则
- 尽量避免对索引列进行计算
坏 WHERE sal*1.1>950
好 WHERE sal>950/1.1
坏 WHERE SUBSTR(name,1,7)=’CAPITAL’
好 WHERE name LIKE ‘CAPITAL%’
- 尽量注意比较值与索引列数据类型的一致性
emp_no： NUMBER型
好 WHERE emp_no=123
好 WHERE emp_no=’123’
emp_type：CHAR型
坏 WHERE emp_type=123 （此时，查询时，不利用索引列）
好 WHERE emp_type=’123’
- 尽量避免使用NULL
坏 WHERE comm IS NOT NULL
坏 WHERE comm IS NULL
好 WHERE comm>=0
- 尽量避免使用NOT=（!=）
坏 WHERE deptno!=0
好 WHERE deptno>0
- 对于复合索引，SQL语句必须使用主索引列
例：复合索引(deptno,job)
好 WHERE deptno=20 AND job=’MANAGER’
好 WHERE deptno=20
好 WHERE job=’MANAGER’ AND deptno=20
坏 WHERE job=’MANAGER’
- ORDER BY子句
好 子句中，列的顺序与索引列的顺序一致。 好 子句中，列应为非空列。
- 查询列与索引列次序（WHERE）的一致性
好 SELECT empno,job FROM emp WHERE empno<100 AND job=’MANAGER’;
1.2其他
- 语句书写要规范
尽量避免相同语句由于书写格式的不同，而导致多次语法分析。
-
尽量少用嵌套查询
-
使用表的别名
多表连接时，使用表的别名来引用列。
坏
SELECT abc002,abd003
FROM ab001 ,ab020
WHERE ab001.col2=ab020.col3
......
好
SELECT t1.abc002,t2.abd003
FROM ab001 t1,ab020 t2
WHERE t1.col2=t2.col3
......
- 用NOT EXISTS代替NOT IN
坏
SELECT ......
FROM emp
WHERE dept_no NOT IN ( SELECT dept_no
FROM dept
WHERE dept_cat=’A’);
好
SELECT ......
FROM emp e
WHERE NOT EXISTS ( SELECT ‘X’
FROM dept
WHERE dept_no=e.dept_no
AND dept_cat=’A’);
- 用多表连接代替EXISTS子句
坏
SELECT ......
FROM emp
WHERE EXISTS ( SELECT ‘X’
FROM dept
WHERE dept_no=e.dept_no
AND dept_cat=’A’);
好
SELECT ......
FROM emp e,dept d
WHERE e.dept_no=d.dept_no
AND dept_cat=’A’;
- 少用DISTINCT，用EXISTS代替
坏
SELECT DISTINCT d.dept_code,d.dept_name
FROM dept d ,emp e
WHERE e.dept_code=d.dept_code;
好
SELECT dept_code,dept_name
FROM dept d
WHERE EXISTS ( SELECT ‘X’
FROM emp e
WHERE e.dept_code=d.dept_code);
-
使用UNION ALL、MINUS、INTERSECT提高性能
-
使用ROWID提高检索速度
对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID将会使效率大大提高。
例：SELECT rowid
INTO v_rowid
FROM t1
WHERE con1
FOR UPDATE OF col2;
......
......
UPDATE t1
SET col2=......
WHERE rowid=v_rowid;
- 查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面
SELECT info
FROM taba a,tabb b,tabc c
WHERE a.acol between :alow and :ahigh
AND b.bcol between :blow and :bhigh
AND c.ccol between :clow and :chigh
AND a.key1 = b.key1
AND a.key2 = c.key2;
其中，A表的acol列可以最多减少查询的记录数目，其次为B表的bcol列，依次类推。
- 尽量使用共享的SQL语句
如经常使用select * from dept where deptno=值, 如果每一个‘值’都是常量，则每一次都会重新解释，不能共享内存中的SQL语句优化结果。应把‘值’设置为一个变量，所有的共同语句都可以优化一次，高度共享语句解释优化的结果。
select * from dept where deptno=:d; //java里面也需要这样使用，而能不用常量
- 显示光标优于隐式光标
坏
update target
set t_field = (select s_information
from source
where source.key = target.key)
where exists (select …
from source
where source.key = target.key)
好
DECLARE
cursor cur_source is
select * from source;
begin
for row in cur_source loop
update target
set t_field = row.s_information
where key = row.key;
end loop;
end;