4.减少对表的查询次数
select tname from teacher
where tid=(select tid from student where sname='张三');select tname from teacher t full join student s
on t.tid=s.tid
where s.name='张三'
5.用 exists 替代 in, not exists 替代 not inselect distinct tname from teacher t left join student s
on t.tid=s.tid
where t.tid in(select tid from student);select distinct tname from teacher t left join student s
on t.tid=s.tid
where exists (select tid from teacher where t.tid=s.tid);
6.用 where 代替 having
select sclass,count(*) from student
group by sclass
having sclass !='1班';select sclass,count(*) from student
where sclass !='1班'
group by sclass
7.使用“<=” 替代 “<”
select * from student where sscore <60;select * from student where sscore <=59;
9.where 子句解析放置的顺序
(子句解析从下至上,应该将那些可过滤掉最大数量记录的条件放在where子句的末尾 )
select * from student s
where s.sscore >60
and
'张三'!=(select t.tname from teacher t where t.tid = s.tid);select * from student s
'张三'!=(select t.tname from teacher t where t.tid = s.tid);
and
where s.sscore >60
10.多表查询使用别名操作
select s.sid,s.sname,t.tname from student s left join teacher t
on s.tid = t.tid
where s.sid =1;
11.避免对索引列使用not关键字(一般表的主键)
select * from student where not sid <=2;--oracle进行全表扫描
select * from student where sid >2;--oracle使用索引查询
在索引列上使用not 关键字,与在索引列上使用函数一样,都会导致oracle进行全表扫描。
12.避免对唯一索引列使用 is(not)null
当在where子句中使用is null 或者 is not null对唯一索引列进行空值比较时,oracle将会停止使用该列上的唯一索引,导致oracle进行全表扫描
13.复合索引主列查询条件应和索引保持一致
在多个列上创建的索引叫复合索引
create index name_score_index on student(sname,sscore);
使用复合索引时,where子句的顺序应同复合索引中索引列保持一致
select * from student where sname='张三'
and sscore =94;
14.监视索引是否被使用,清除无用索引
不必要的索引会对表的查询效率起负作用
监视:
alter index name_score_index monitoring usage;
使用状态查询:
select table_name,index_name,monitoring from v$object_usage;
删除:
drop index name_score_index;