【转】高效SQL

这是高效SQL的详细页面，介绍了和数据库,oracle 高效SQL有关的知识、技巧、经验，和一些数据库源码等。

最高效的删除重复记录方法 ( 因为使用了ROWID)例子：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);



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

举例：
(低效)
select ... from table1 t1 where t1.sal > 300 and t1.jobtype = '0001' and 20 < (select count(*) from table1 t2 where t2.pno = t1.tno;

(高效)
select ... from table1 t1 where 20 < (select count(*) from table1 t2 where t2.pno = t1.tno and t1.sal > 300 and t1.jobtype = '0001';

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


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

举例：
题目——我要查找编号为0001、0002学生的信息。
（低效）
select name,age,gender,address from t_student where id = '0001';
select name,age,gender,address from t_student where id = '0002';
（高效）
select a.name,a.age,a.gender,a.address,b.name,b.age,b.gender,b.address from t_student a,t_student b where a.id = '0001' and b.id = '0002';

4、使用Decode函数来减少处理时间：
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

举例：
（低效）
select count(*), sum(banace) from table1 where dept_id = '0001' and name like 'anger%';
select count(*), sum(banace) from table1 where dept_id = '0002' and name like 'anger%';
（高效）
select count(decode(dept_id,'0001','XYZ',null)) count_01,count(decode(dept_id,'0002','XYZ',null)) count_02,
sum(decode(dept_id,'0001',dept_id,null)) sum_01,sum(decode(dept_id,'0002',dept_id,null)) sum_02
from table1
where name like 'anger%';

5、整合简单,无关联的数据库访问：
如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系)

举例：
（低效）
select name from table1 where id = '0001';
select name from table2 where id = '0001';
select name from table3 where id = '0001';
（高效）
select t1.name, t2.name, t3.name
from table1 t1, table2 t2, table3 t3
where t1.id(+) = '0001' and t2.id(+) = '0001' and t3.id(+) = '0001'
【注：上面例子虽然高效，但是可读性差，需要量情而定啊！】

6、删除重复记录：
最高效的删除重复记录方法 ( 因为使用了ROWID)

举例：
delete from table1 t1
where t1.rowid > (select min(t2.rowid) from table1 t2 where t1.id = t2.id);

7、尽量不要使用having子句，可以考虑用where替换。
having只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序，总计等操作。
如果能通过where子句限制记录的数目，那就能减少这方面的开销。

8、尽量用表的别名：
当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。
这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

9、用exists替代in（发现好多程序员不知道这个怎么用）：
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。
在这种情况下，使用exists(或not exists)通常将提高查询的效率。

举例：
（低效）
select ... from table1 t1 where t1.id > 10 and pno in (select no from table2 where name like 'www%');
（高效）
select ... from table1 t1 where t1.id > 10 and exists (select 1 from table2 t2 where t1.pno = t2.no and name like 'www%');

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


11、用exists替换distinct：
当提交一个包含一对多表信息的查询时,避免在select子句中使用distinct. 一般可以考虑用exists替换

举例：
（低效）
select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;
（高效）
select d.dept_no, d.dept_name from t_dept d where exists (select 1 from t_emp where d.dept_no = e.dept_no);

exists使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

12、用表连接替换exists：
通常来说，采用表连接的方式比exists更有效率。

举例：
（低效）
select ename from emp e where exists (select 1 from dept where dept_no = e.dept_no and dept_cat = 'W');
SELECT ENAME
（高效）
select ename from dept d, emp e where e.dept_no = d.dept_no and dept_cat = 'W';

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

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

14、最好把复杂的sql，去看下它的执行计划，这样有利于你分析知道自己的sql效率如何。

尽量少用IN操作符，基本上所有的IN操作符都可以用EXISTS代替
用IN写出来的SQL的优点是比较容易写及清晰易懂，但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
ORACLE 试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用 IN 的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

Oracle在执行IN子查询时，首先执行子查询，将查询结果放入临时表再执行主查询。而EXIST则是首先检查主查询，然后运行子查询直到找到第一个匹配项。NOT EXISTS比 NOT IN效率稍高。但具体在选择IN或EXIST操作时，要根据主子表数据量大小来具体考虑。

不用“<>”或者“!=”操作符。对不等于操作符的处理会造成全表扫描，可以用“<” or “>”代替

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

推荐方案：用其它相同功能的操作运算代替，如：
1）a<>0 改为 a>0 or a<0
2）a<>’’ 改为 a>’’
Where子句中出现IS NULL或者IS NOT NULL时，Oracle会停止使用索引而执行全表扫描。可以考虑在设计表时，对索引列设置为NOT NULL。这样就可以用其他操作来取代判断 NULL的操作
IS NULL 或IS NOT NULL操作（判断字段是否为空）
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
对数据类型不同的列进行比较时，会使索引失效。

UNION操作符
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录， 最常见的是过程表与历史表UNION。如：

select * from gc_dfys
union
select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys
union all
select * from ls_jg_dfys

同一功能同一性能不同写法SQL的影响

如一个SQL在A程序员写的为
Select * from zl_yhjbqk

B程序员写的为
Select * from dlyx.zl_yhjbqk（带表所有者的前缀）

C程序员写的为
Select * from DLYX.ZLYHJBQK（大写表名）

D程序员写的为
Select * from DLYX.ZLYHJBQK（中间多了空格）

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。
推荐方案：不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'

以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。
查询表顺序的影响
Oracle从右到左处理From子句中的表名，所以在From子句中包含多个表的情况下，将记录最少的表放在最后。（只在采用RBO优化时有效)
Order By语句中的非索引列会降低性能，可以通过添加索引的方式处理。严格控制在Order By语句中使用表达式
当在Sql语句中连接多个表时，使用表的别名，并将之作为每列的前缀。这样可以减少解析时间
多利用内部函数提高Sql效率
SQL语句索引的利用
采用函数处理的字段不能利用索引
如：
substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate)， 优化处理：sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
进行了显式或隐式的运算的字段不能进行索引

出处：http://www.dnbcw.com/biancheng/sql/lsyl185677.html