浏览 14237 次
锁定老帖子 主题:高效率Oracle SQL语句
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (4)
|
|
---|---|
作者 | 正文 |
发表时间:2009-11-01
最后修改:2009-11-12
【注:以下说的(低效)与(高效)都是相当来说的。】 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效率如何。 上面的资料也是本人长期看资料积攒下来的,并且很多都已经在项目中,特别是大数据量时得到了体现。 声明:ITeye文章版权属于作者,受法律保护。没有作者书面许可不得转载。
推荐链接
|
|
返回顶楼 | |
发表时间:2009-11-02
楼主的12条是不是说反了?oracle性能调整和优化上专门说到用exists代替表连接。
|
|
返回顶楼 | |
发表时间:2009-11-02
sql 不是末端往前段读取吗 lz是不是也说反了啊
|
|
返回顶楼 | |
发表时间:2009-11-02
最后修改:2009-11-02
java苹果+番茄 写道 最近在JavaEye上发现好多同志对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'; 做过表分析了吗?是CBO还是RBO?? 2、Select子句中避免使用 “ * ”: 当你想在select子句中列出所有的column时,使用动态SQL列引用 ‘*' 是一个方便的方法。 不幸的是,这是一个非常低效的方法。 实际上,ORACLE在解析的过程中,会将 '*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。 你试过count(*),count(1),count(主键)的效率么?当然你说的也没错 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'; or 一下是否更好? 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%'); 这个视情况而定吧,你去试过2张超大表 用exists 就知道执行效率了 |
|
返回顶楼 | |
发表时间:2009-11-02
select name,age,gender,address from t_student where id = '0001';
select name,age,gender,address from t_student where id = '0002'; 我不懂oracle,不过好像access和MySQL都支持这种做法: select name,age,gender,address from t_student where id in ('0001','0002'); 这种应该效率更高吧 |
|
返回顶楼 | |
发表时间:2009-11-02
hehe 对你的第一条 sybase是从上到下 从左到右
|
|
返回顶楼 | |
发表时间:2009-11-02
针对以上兄弟提出的问题回答:
1楼同学:不是什么情况下都是的,一般情况的话,用表连接要高点,比如说简单的多表查询等 2楼同学:请您看清楚一下,我说的就是sql是从末尾开始执行的,所以把能过滤大数据量的放到最后,这样关联其他小数据量表的时候不是更快吗 4楼同学:你提了很多问题,下面回答你第一个, 1、试问现在企业应用有多少会使用RBO; 2、第二个,对于count(*),count(1),count(主键)的效率,这个曾经争论很大,一般来说它们是没什么差别的,不过如果是索引字段的话,可能要快点; 3、关于这个问题,我没试过不敢妄下论断 4、关于这个问题,我不知道你们那所谓的大数据表是多少数据量的,我现在给建行做,都是千万以上的,效率要高的很 5楼同学:相当不错我说了,低效与高效是相当来说的,不表示没有更高效的,只想说明要减少数据访问次数。 6楼同学:我针对的是oracle,毕竟这是主流数据库。 谢谢同学们的指点与评论,如果有冒犯之处,还请多包含,欢迎广大朋友多提意见,多批评。 |
|
返回顶楼 | |
发表时间:2009-12-21
最后修改:2009-12-21
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'; or 一下是否更好? 第三个疑问明显有问题,同样是查询出复合001和002的学生,你用or的查询结果比用and要多得多,其中存在不少重复数据,查询结果的数量明显影响查询速度这个常识大家都应该懂吧 |
|
返回顶楼 | |