SQL调优

1. 查看执行计划

• EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称.你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行.在PL/SQL DEVELOPER中，选中SQL并按F5即可得到执行计划。

2. 使用索引

索引用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 通常, 在大型表中使用索引特别有效. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

• 使用第一个列： 如果索引建在多个列上，只有当它的第一列被where子句引用时，优化器才会选择使用该索引。
• 避免改变列的类型：如果sql语句的索引列被发生了 类型转换，则索引不会被用到。
• 避免使用计算：WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描，举例：
  SELECT * FROM DEPT WHERE SAL * 12 > 25000; （低效）
  SELECT * FROM DEPT WHERE SAL > 25000/12;（高效）
• 避免使用IS NULL, IS NOT NULL, != : 避免在索引中使用任何可以为空或者不存在的列，ORACLE将无法使用该索引
  SELECT * FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; （低效）
  SELECT * FROM DEPARTMENT WHERE DEPT_CODE >=0;（高效）
• 用>=替代>: 如果DEPTNO上有一个索引,则
  SELECT * FROM EMP WHERE DEPTNO >=4 （高效）
  SELECT * FROM EMP WHERE DEPTNO >3 （低效）
  两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录.
• 用union代替or: 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.
  SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”（高效）
  SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”（低效）

3. 减少数据库IO

• 避免耗费资源的操作：带有DISTINCT、UNION、MINUS、INTERSECT、ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.例如,一个UNION查询,其中每个查询都带有ORDER BY子句, ORDER BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序, 然后在执行UNION时, 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率.通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写.例如DISTINCT可以使用GROUP BY 代替
• 减少访问次数：当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
• 在含有子查询的SQL语句中,要特别注意减少对表的查询
• 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表
• 直接操作实际数据, 少用临时表

4. 其它建议

• 用EXISTS替代IN
• 用EXISTS替代DISTINCT
• NOT EXISTS替代NOT IN
• UNION-ALL 替代UNION