Oracle语句优化

数据库  /  管理员 发布于 5年前   319

1. 避免使用“*”替代所有列
    如果采用“*”，Oracle系统需要首先通过数据字典来将语句中的“*”转换成相应表中的所有列名，自然要比直接使用列名花费更多的时间。

2. 用TRUNCATE代替DELETE删除整表数据
    在使用DELETE时，Oracle会对这些行进行逐行地删除，并且使用回滚段来记录删除操作，使用TRUNCATE语句删除表中的所有数据时，Oracle不会在撤销表空间中记录删除操作，提高了语句的执行速度，而且这种删除是一次性的，也就是所有的数据行是在同一时间被删除。
语法：TRUNCATE TABLE 表名 [DROP | REUSE STORAGE];
说明：
DROP STORAGE：收回被删除的空间，默认选项。
REUSE STORAGE：保留被删除的空间供表的新数据使用。

3. 在确保完整性的情况下多用COMMIT语句
    使用COMMIT命令后，系统将释放回滚段上记录的DML操作信息、被程序语句获得的锁、redo log buffer中的空间以及Oracle系统管理所需要的其它花费。

4. 减少表的查询次数
    能使用一次查询获得的数据尽量不要去通过两次或多次的查询获得
例：
SELECT NAME FORM MEMBER WHERE ID=(SELECT ID FROM CUSTOMER WHERE CERT_NO='000'); // 两次查询
SELECT NAME FORM MEMBER M FULL JOIN CUSTOMER C ON M.ID=C.ID WHERE C.CERT_NO='000'; // 使用一次查询替换上面的查询

5. 用EXISTS替代IN
    IN用来判断一个值是否在某个列表中，使用IN时，子查询先产生结果集，然后主查询再去结果集中寻找符合要求的字段列表，符合要求的输出，反之则不输出。
    EXISTS则用来判断一个值是否存在，使用EXISTS时子查询不返回列表的值，只返回一个TRUE或FALSE。运行方式是先运行主查询一次，再去子查询中查询与其对应的结果。
    由于IN操作符需要进行确切地比较，而EXISTS只需要验证存不存在，所有使用IN将会比使用EXISTS花费更多的查询成本。NOT EXISTS也应该替换NOT IN。
    也可以使用exists来替换distinct去重。

6. 用WHERE替代HAVING
    如果不是对统计函数的结果进行过滤，应尽量在分组前对查询先用WHERE进行过滤。

7. 使用“<=”替代“>”
    如果使用“<60”，则Oracle则会首先定位到60，然后再去找比60小的数；而如果使用“<=59”则Oracle会直接定位到59，虽然这种优化差别不大，但是在查询较大时区别还是很大的。“>=”与“>”的情况也是如此。

8. 选择FROM表的顺序
    Oracle的解析器在处理FROM子句中的表时，是按照从右到左的顺序，也就是说FROM子句中最后指定的表将被Oracle首先处理，Oracle将它作为驱动表，并对该表的数据进行排序，之后再扫描倒数第二个表，最后将第二个检索出来的记录与第一个表中的合适记录进行合并。
    因此，建议在使用表的连接查询时，选择记录条数最少的表作为驱动表，也就是将它作为FROM子句中的最后一个表。但是如果是两个表的交叉表时（一个表存放另两个表的主键）应该选择交叉表作为驱动表。

9. WHERE子句的连接顺序
    当WHERE子句中所指定的条件不止一个时，应该将表之间的连接查询放在其它查询条件的前面，因为Oracle解析WHERE子句的顺序是从下至上，所以应该将那些可以过滤掉最大数量记录的条件放在WHERE子句的末尾。

10. 使用表的别名
    连接查询时，并不是所有的列名都需要显式地指明表名或者它的别名，只有当这个列名同时存在于多个表中时才需要。但是如果在SQL语句中不指明它所属的表，那么这部分工作将会由Oracle自己去未完成，这显然会增加Oracle的负担，所有能够使用表的别名时就尽量使用。

11. 用instr代替like
    遇到需要用LIKE过滤的sql语句，完全可以用instr代替，处理速度将显著提高。像其它地方也可以通过内部函数来提高sql效率。

12. 相同的查询应保持sql一致
    Oracle对每个sql都会对其进行一次分析，并且占用共享内存，如果将sql的字符串及格式写得完全相同则Oracle只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析sql的时间，而且可以减少共享内存重复的信息。

13. 高效的删除重复记录方法
语法：delete from emp e where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no);

14. count计算记录条数
    和一般的观点相反， count（*） 比count（1）稍快 ， 当然如果可以通过索引检索，对索引列的计数仍旧是最快的。