oracle之语句调优
1.SQL语句大写
因为oracle总是先解析SQL语句,把小写的字母转换成大写的再执行。
2.表别名
多表join时,尽量使用表别名,同时把表别名前缀于每一列上,减少解析的时间及由列歧义引起的语法错误
3.from从右到左解析表名
from子句中写在最后的表(基础表也称为驱动表,driving table)将被最先处理,尽量记录条数最少的表作为基础表放最后,如维表
4.where自下向上解析
表之间的连接必须写在其他where条件之前, 那些可以过滤掉最大数量记录的条件必须写在where子句的末尾
5.select *
尽量避免使用*查询全部列,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
6.避免使用耗费资源的操作:
带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。DISTINCT需要一次排序操作,而其他的至少需要执行两次排序。
7.union all和union
UNION两个集合时,这两个结果集合会以UNION-ALL的方式被合并,然后在输出最终结果前进行排序。如果用UNION ALL替代UNION, 这样排序就不是必要了。效率就会因此得到提高。注意UNION ALL将重复输出两个结果集合中相同记录。
8.用EXISTS替代IN、用NOT EXISTS替代 NOT IN
在基于基础表的查询中经常需要对另一个表进行联接。在这种情况下, 使用EXISTS(或NOTEXISTS)通常将提高查询的效率。在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的(要对子查询中的表执行了一个全表遍历)。所以尽量将NOT IN改写成外连接(Outer Joins)或NOT EXISTS。
9.用Where子句替换HAVING子句
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序,总计等操作。最好能通过WHERE子句限制记录的数目。
10.用>=替代>
区别:前者DBMS将直接跳到第一个ID等于4的记录而后者将首先定位到ID=3的记录并且向前扫描到第一个ID大于3的记录。
11.建立索引
索引要点:
- 索引之后,按索引字段重复最少的来排序,会达到最优的效果。以我们的表来说,如果建立了No的聚集索引,把No放在where子句的第一位是最佳的,其次是Id,然后是MgrObjId,最后是时间,时间索引如果表是一个小时的,最好不要用
- where子句的顺序
- 决定了查询分析器是否使用索引来查询。比如建立了MgrObjId和Id的索引,那么
where MgrObjId='' and Id='' and Dtime=''
就会采用索引查找,而where Dtime='' and MgrObjId='' and Id=''
则不一定会采用索引查找。 - 把非索引列的结果列放在包含列中。因为我们条件是MgrObjId和Id以及Dtime,因此返回结果中只需包含Dtime和Value即可,因此把Dtime和Value放在包含列中,返回的索引结果就有这个值,不用再查物理表,可以达到最优的速度。