Oracle语句优化 (汇总)

     大部分写sql语句都是为了实现而实现，如果最后在投入使用的过程中没有很难接受的性能，基本不怎么去考虑其性能。最近刚好有点时间，对优化方面进修进修，以备不时之需。

• 选择最有效率的表名顺序

     Oracle的解析器按照从右到左的顺序处理from子句中的表名，写在最后的表将会最先处理，所以在多个表的情况下，需要选择数据条数最少的作为基础表写最后。

• where子句中的连接顺序

Oracle采用自下而上的顺序解析where子句，所以表之间的连接必须写在其它where条件之前，可以过滤掉最大数量记录的条件必须写在where子句的末尾。

• select语句避免使用*

Oracle解析过程中会将*依次转换成所有列名，这个工作是通过查询数据字典完成的，意味着将耗费更多时间。

• 用Truncate代替delete

当删除表中记录时，通常情况下，回滚段用来存放可以被恢复的信息，若未commit事务，Oracle会将数据恢复到删除之前的状态。而Truncate删除后数据将不能恢复，因此很少的资源被调用，执行时间也会很短。

• 尽量多使用commit

可以释放资源，如：回滚段上用于恢复数据的信息，被程序语句获的锁，redo log buffer中的空间，Oracle为管理上面资源中的内部话费。

• 用where替换having语句

having只会在检索出所有记录后才对结果进行过滤，这个处理需要排序，总计等操作。on、where、having都可以加条件的子句中，on是最先执行的，where次之，having最后。

• 通过内部函数提高效率

复杂的sql往往牺牲了执行效率，能够运用函数解决问题也是非常重要的。

• 使用表的别名

sql连接多个表时，使用表的别名并把别名前缀到每列上，这样一来可以减少解析的时间，也能减少由列名歧义引起的语法错误。

• 用exists替代in,用 not exists 替代not in

无论哪种情况下，not in都是最低效的，因为它是对子查询中的表执行了一个全表遍历，我们可以用外连接（outer joins）或not exists。

 

• 用exists替代distinct

•  sql语句用大写的

因为Oracle总是先解析Sql语句，把小写字母转换成大写字母再执行。

• 避免在索引列上使用Not

Not会产生在和在索引列上使用函数相同的影响，当Oracle遇到Not会停止使用索引转而执行全表扫描。

• 避免在索引列上使用计算

where语句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。

低效：select name from dept where sal*12>5000;

高效：select name from dept where sal>5000/12;

• 用>=替代>

低效：select name from dept where sal>=4;

高效：select name from dept where sal>3;

• 优化group by

可以将不需要的记录在group by之前过滤掉。

低效：select job,avg(sal) from emo group job having job='j' or job='q';

高效：select job,avg(sal) from emo where  job='j' or job='q'  group job;

• 用where替代order by

order by 中所有的列必须包含在相同的索引中，并保持在索引中的排列顺序；order by 中所有列必须定义为非空。如：表dept(code pk 不为空，id 不为空，name 可为空)

低效（索引不被使用）

select code from dept order by name;

高效(使用索引)

select code from dept where id>0;

• 避免改变索引的类型

为避免Oracle对sql进行隐式的类型转换，最好把类型转换用显式表现出来。当字符和数值比较时，Oracle会优先转换成数值类型然后到字符类型。

• 避免索引不起作用

在以下情况索引不起作用：

1. '!='将不使用索引。记住索引只能告诉你什么存在表中，而不能告诉你什么不存在表中。
2. '||'是字符连接函数。就像其它函数那样，停用了索引。
3. '+'是数学函数，就像其它函数那样，停用了索引。
4. 相同的索引不能相互比较，这将会启用全表扫描。