最高效的删除重复记录方法 ( 因为使用了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)
进行了显式或隐式的运算的字段不能进行索引