举例: 题目——我要查找编号为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';
举例: (低效) 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%';
举例: (低效) 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);
举例: (低效) 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%');
举例: (低效) 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将无法使用该索引。 对于单列索引,如果列包含空值,索引中将不存在此记录; 对于复合索引,如果每个列都为空,索引中同样不存在此记录; 如果至少有一个列不为空,则记录存在于索引中。