高效率的SQL语句

 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 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效率如何。