SQL优化

                                SQL优化

1、SQL语句尽量用大写

      因为oracle解析SQL语句时，总是先把小写的字母转换成大写的再执行。

 2、使用表的别名

     当在SQL语句中连接多个表时，尽量使用表的别名并把别名缀于每个列上，这样一来，就可以减少解析时间并减少由列歧义引起的语法错误。

3、选择最有效的表名顺序

     oracle的解析器按照从右到左的顺序处理from子句中的表，from子句中写在最后的表将最先处理，在from子句中包含多个表的情况下，必须选择记录数最少的表放在最后。

     如果有3个以上的表连接查询，那就需要选择交叉表作为基础表，交叉表是指被其他表引用的表。

    --No.1  tableA 100w条记录  tableB 1w条记录 执行速度 十秒级

   SELECT COUNT(*) FROM tableA,tableB;

 

   --No.2  执行速度百秒级甚至更高

   SELECT COUNT(*) FROM tableB,tableA;

  上面的结果肯定是No.2比No.1效率更高。

SELSET COUNT(1) FROM tableA a,tableB b ,tableC c WHERE a.id=b.id AND a.id=c.i；

上面的sql中tableA 就称为交叉表，根据oracle对From子句从右向左的扫描方式，应该把交叉表放在最末尾，然后才是最小表，所以上面的应该这样写

--tableA a 交叉表

--tabelB b 100w

--tableC c 1w

SELECT COUNT(1) FROM tableB b ,tableC c ,tableA a WHERE a.id=b.id AND a.id=c.id;

这种写法对大数据量会非常有用，大家谨记，也是很常用的。

4、where子句后面的条件过滤

      oracle对where子句后面的条件过滤是自下而上，从右到左扫描的，所以和from子句一样一样的，把过滤条件排个序，按过滤数据的大小，自然就是 可以过滤掉最大数据记录的条件必须写在where子句的末尾最下面，最右边，以此类推。

 

5、在select的时候少用*

        因为oracle的查询器会把*转换为表的全部列名，这个会浪费时间，所以在大表中少用。

6、使用rowid，rownum

       常用语分页，删除查询重复记录，非常实用。

--查找重复记录
SELECT* FROM  tableA  a WHERE
 a.ROWID> (
 SELECT MIN(ROWID) FROM tableB b WHERE 
 a.column=b.column
 ) 

--删除相同记录
DELETE FROM  tableA  a WHERE
 a.ROWID> (
 SELECT MIN(ROWID) FROM tableB b WHERE 
 a.column=b.column
 ) 

--分页 start=10 limit=10
--end 为 start + limit
SELECT * FROM 
(
  SELECT A.*,Rownum rn FROM 
    (SELECT * FROM tableA ORDER BY id) A
  WHERE rownum <= 20
) b WHERE rn> 10  ORDER BY id DESC

7、存储过程中多用commit（谨慎使用）

        这样程序的性能能得到提高，需求也会因为commit所释放的资源而减少。

8、不用in，not in，用exists,not exists来代替

9、用表连接替换exists

10、用where子句代替having子句

11、用exists替换distinct

12、通常避免在索引列上使用not

          我们要避免在索引列上使用not，not会产生 和在索引列上使用函数相同的影响，当oracle遇到not，他就会停止使用索引转而执行全表扫描。

13、避免在索引列上使用计算

14、用>=替代>

15、避免在索引列上使用is null 和is not null

16、总是使用索引的第一个列

           如果索引是建立在多个列上，只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。这也是一条街简单而重要的规则，当仅使用索引的第二个列时，优化器使用了全表扫描而忽略了索引。

17、用union-all替换union（如果有可能的话）

          当SQL语句需要union两个查询结果集合时，这两个结果集合会以union-all的方式被合并，然后再输入最终结果并排序。如果用union-all替代union，这样排序就不是必要的了。效率就会因此得到提高。不过需要注意的是，union-all将会重复输出两个结果集合中相同的记录，因此还是要从实际业务需求分析使用union-all的可行性。

18、避免改变索引列的类型