ORACLESQL语句优化技术分析(转载)

ORACLE SQL语句优化技术分析

选择自 yzsind 的 Blog  ORACLE SQL语句优化技术分析

出处

SQL语句优化技术分析

操作符优化

IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂&＃xff0c;这比较适合现代软件开发的风格。

但是用IN的SQL性能总是比较低的&＃xff0c;从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别&＃xff1a;

    ORACLE试图将其转换成多个表的连接&＃xff0c;如果转换不成功则先执行IN里面的子查询&＃xff0c;再查询外层的表记录&＃xff0c;如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功&＃xff0c;但对于含有分组统计等方面的SQL就不能转换了。

    推荐方案&＃xff1a;在业务密集的SQL当中尽量不采用IN操作符。

NOT IN操作符

    此操作是强列推荐不使用的&＃xff0c;因为它不能应用表的索引。

    推荐方案&＃xff1a;用NOT EXISTS 或&＃xff08;外连接&＃43;判断为空&＃xff09;方案代替

<> 操作符&＃xff08;不等于&＃xff09;

    不等于操作符是永远不会用到索引的&＃xff0c;因此对它的处理只会产生全表扫描。

推荐方案&＃xff1a;用其它相同功能的操作运算代替&＃xff0c;如

    a<>0 改为 a>0 or a<0

    a<>’’ 改为 a>’’

IS NULL 或IS NOT NULL操作&＃xff08;判断字段是否为空&＃xff09;

    判断字段是否为空一般是不会应用索引的&＃xff0c;因为B树索引是不索引空值的。

    推荐方案&＃xff1a;

用其它相同功能的操作运算代替&＃xff0c;如

    a is not null 改为 a>0 或a>’’等。

    不允许字段为空&＃xff0c;而用一个缺省值代替空值&＃xff0c;如业扩申请中状态字段不允许为空&＃xff0c;缺省为申请。

    建立位图索引&＃xff08;有分区的表不能建&＃xff0c;位图索引比较难控制&＃xff0c;如字段值太多索引会使性能下降&＃xff0c;多人更新操作会增加数据块锁的现象&＃xff09;

> 及 <操作符&＃xff08;大于或小于操作符&＃xff09;

    大于或小于操作符一般情况下是不用调整的&＃xff0c;因为它有索引就会采用索引查找&＃xff0c;但有的情况下可以对它进行优化&＃xff0c;如一个表有100万记录&＃xff0c;一个数值型字段A&＃xff0c;30万记录的A&＃61;0&＃xff0c;30万记录的A&＃61;1&＃xff0c;39万记录的A&＃61;2&＃xff0c;1万记录的A&＃61;3。那么执行A>2与A>&＃61;3的效果就有很大的区别了&＃xff0c;因为A>2时ORACLE会先找出为2的记录索引再进行比较&＃xff0c;而A>&＃61;3时ORACLE则直接找到&＃61;3的记录索引。

LIKE操作符

LIKE操作符可以应用通配符查询&＃xff0c;里面的通配符组合可能达到几乎是任意的查询&＃xff0c;但是如果用得不好则会产生性能上的问题&＃xff0c;如LIKE ‘%5400%’ 这种查询不会引用索引&＃xff0c;而LIKE ‘X5400%’则会引用范围索引。一个实际例子&＃xff1a;用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描&＃xff0c;如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询&＃xff0c;性能肯定大大提高。

UNION操作符

UNION在进行表链接后会筛选掉重复的记录&＃xff0c;所以在表链接后会对所产生的结果集进行排序运算&＃xff0c;删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录&＃xff0c;最常见的是过程表与历史表UNION。如&＃xff1a;

select * from gc_dfys

union

select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果&＃xff0c;再用排序空间进行排序删除重复的记录&＃xff0c;最后返回结果集&＃xff0c;如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案&＃xff1a;采用UNION ALL操作符替代UNION&＃xff0c;因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys

SQL书写的影响

同一功能同一性能不同写法SQL的影响

如一个SQL在A程序员写的为

    Select * from zl_yhjbqk

B程序员写的为

    Select * from dlyx.zl_yhjbqk&＃xff08;带表所有者的前缀&＃xff09;

C程序员写的为

    Select * from DLYX.ZLYHJBQK&＃xff08;大写表名&＃xff09;

D程序员写的为

    Select *  from DLYX.ZLYHJBQK&＃xff08;中间多了空格&＃xff09;

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的&＃xff0c;但是从ORACLE共享内存SGA的原理&＃xff0c;可以得出ORACLE对每个SQL 都会对其进行一次分析&＃xff0c;并且占用共享内存&＃xff0c;如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次&＃xff0c;共享内存也只会留下一次的分析结果&＃xff0c;这不仅可以减少分析SQL的时间&＃xff0c;而且可以减少共享内存重复的信息&＃xff0c;ORACLE也可以准确统计SQL的执行频率。

WHERE后面的条件顺序影响

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响&＃xff0c;如

Select * from zl_yhjbqk where dy_dj &＃61; &＃39;1KV以下&＃39; and xh_bz&＃61;1

Select * from zl_yhjbqk where xh_bz&＃61;1  and dy_dj &＃61; &＃39;1KV以下&＃39;

以上两个SQL中dy_dj&＃xff08;电压等级&＃xff09;及xh_bz&＃xff08;销户标志&＃xff09;两个字段都没进行索引&＃xff0c;所以执行的时候都是全表扫描&＃xff0c;第一条SQL的dy_dj &＃61; &＃39;1KV以下&＃39;条件在记录集内比率为99%&＃xff0c;而xh_bz&＃61;1的比率只为0.5%&＃xff0c;在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较&＃xff0c;而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较&＃xff0c;以此可以得出第二条SQL的CPU占用率明显比第一条低。

查询表顺序的影响

在FROM后面的表中的列表顺序会对SQL执行性能影响&＃xff0c;在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接&＃xff0c;由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。&＃xff08;注&＃xff1a;如果对表进行了统计分析&＃xff0c;ORACLE会自动先进小表的链接&＃xff0c;再进行大表的链接&＃xff09;

SQL语句索引的利用

对操作符的优化&＃xff08;见上节&＃xff09;

对条件字段的一些优化

采用函数处理的字段不能利用索引&＃xff0c;如&＃xff1a;

substr(hbs_bh,1,4)&＃61;’5400’&＃xff0c;优化处理&＃xff1a;hbs_bh like ‘5400%’

trunc(sk_rq)&＃61;trunc(sysdate)&＃xff0c; 优化处理&＃xff1a;

sk_rq>&＃61;trunc(sysdate) and sk_rqsysdate&＃43;1)

进行了显式或隐式的运算的字段不能进行索引&＃xff0c;如&＃xff1a;

ss_df&＃43;20>50&＃xff0c;优化处理&＃xff1a;ss_df>30

‘X’||hbs_bh>’X5400021452’&＃xff0c;优化处理&＃xff1a;hbs_bh>’5400021542’

sk_rq&＃43;5&＃61;sysdate&＃xff0c;优化处理&＃xff1a;sk_rq&＃61;sysdate-5

hbs_bh&＃61;5401002554&＃xff0c;优化处理&＃xff1a;hbs_bh&＃61;’ 5401002554’&＃xff0c;注&＃xff1a;此条件对hbs_bh 进行隐式的to_number转换&＃xff0c;因为hbs_bh字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引&＃xff0c;如&＃xff1a;

ys_df>cx_df&＃xff0c;无法进行优化

qc_bh||kh_bh&＃61;’5400250000’&＃xff0c;优化处理&＃xff1a;qc_bh&＃61;’5400’and kh_bh&＃61;’250000’

应用ORACLE的HINT&＃xff08;提示&＃xff09;处理

提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示

目标方面的提示&＃xff1a;

COST&＃xff08;按成本优化&＃xff09;

RULE&＃xff08;按规则优化&＃xff09;

CHOOSE&＃xff08;缺省&＃xff09;&＃xff08;ORACLE自动选择成本或规则进行优化&＃xff09;

ALL_ROWS&＃xff08;所有的行尽快返回&＃xff09;

FIRST_ROWS&＃xff08;第一行数据尽快返回&＃xff09;

执行方法的提示&＃xff1a;

USE_NL&＃xff08;使用NESTED LOOPS方式联合&＃xff09;

USE_MERGE&＃xff08;使用MERGE JOIN方式联合&＃xff09;

USE_HASH&＃xff08;使用HASH JOIN方式联合&＃xff09;

索引提示&＃xff1a;

INDEX&＃xff08;TABLE INDEX&＃xff09;&＃xff08;使用提示的表索引进行查询&＃xff09;

其它高级提示&＃xff08;如并行处理等等&＃xff09;

ORACLE的提示功能是比较强的功能&＃xff0c;也是比较复杂的应用&＃xff0c;并且提示只是给ORACLE执行的一个建议&＃xff0c;有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用&＃xff0c;一般不建议开发人员应用ORACLE提示&＃xff0c;因为各个数据库及服务器性能情况不一样&＃xff0c;很可能一个地方性能提升了&＃xff0c;但另一个地方却下降了&＃xff0c;ORACLE在SQL执行分析方面已经比较成熟&＃xff0c;如果分析执行的路径不对首先应在数据库结构&＃xff08;主要是索引&＃xff09;、服务器当前性能&＃xff08;共享内存、磁盘文件碎片&＃xff09;、数据库对象&＃xff08;表、索引&＃xff09;统计信息是否正确这几方面分析。

本文是我初学ORACLE写的&＃xff0c;有些地方是根据其它参与资料得来&＃xff0c;未经过我全面测试&＃xff0c;可能有些地方分析不正确&＃xff0c; 请网友多多指正&＃xff01;&＃xff01;

作者Blog&＃xff1a;http://blog.csdn.net/yzsind/