写出高性能SQL语句方法（上）

1、整合简单，无关联的资料库访问：

WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全资料表扫描。例如：

（高效）SELECT * FROM DEPT D WHERE D.SAL > 300000 / 12;

（低效）SELECT * FROM DEPT D WHERE D.SAL * 12 > 300000;

1. 如果你有几个简单的资料库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系）2、删除重复记录：
2. 最高效的删除重复记录方法（因为使用了ROWID）例子：
3. DELETE FROM EMP E WHERE E.ROWID > ( SELECT MIN( X.ROWID ) FROM EMP X WHERE X.EMP_NO = E.EMP_NO );
4. 3、用TRUNCATE替代DELETE:
5. 当删除表中的记录时，在通常情况下，回流滚段（ rollback segments ）用来存放可以被恢复的资讯。如果你没有 COMMIT 事务， ORACLE 会将资料恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况）而当运用 TRUNCATE 时，回滚段不再存放任何可被恢复的资讯。当命令运行后，资料不能被恢复。因此很少的资源被调用，执行时间也会很短。（注：TRUNCATE 只在删除全表适用，TRUNCATE 是DDL 不是 DML）
6. 4、选择最有效率的表名顺序（只在基于规则的优化器中有效）：
7. ORACLE 的解析器按照从右到左的连续处理FROM子句中的表名，FROM子句中写在最后的表（基楚资料表 driving table）将被最先处理，在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基楚资料表。如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基楚资料表，交叉表是指那个被期货表所引用的表。
8. 5、WHERE子句中的连接顺序 . :
9. ORACLE 采用自下而上的顺序解析 WHERE 子句，根据这个原理，表之间的连接必须写在其它 WHERE 条件之前，那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾。
10. 6、SELECT 子句中避免使用‘ * ’：
11. ORACLE在的过程中，会将‘ * ’依次转换成所有的列名，这个工作是通过查询资料字典完成的，这意味着将耗费更多的时间。
12. 7、减少访问资料库的次数：
13. ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读资料块等。
14. 8、在SQL*Plus，SQL*Forms 和Pro*C中重新设置 ARRAYSIZE 参数，可以增加每次资料库访问的检索资料量，建议值为200.
15. 9、使用DECODE函数来减少处理时间：
16. 使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。
17. 10、用WHERE 子句替换HAVING子句：
18. 避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过虑。这个处理需要排序，统计等操作，如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。（非oracle中）on 、where 、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的资料，按理说应该速度是最快的；where也应该比having快点的，因为它过滤资料后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在意表查询统计的情况下，如果要过滤的条件没有涉及到要计算栏位，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢，如果要涉及到计算的栏位，就表示在没计算之前，这个栏位的值是不确定的，根据ORACLE的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在联接查询时，on比where更早起作用。系统首先根据第各个表之间的联结条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里。
19. 11、减少对表的查询：
20. 在含有子查询的SQL语句中，要特别注意减少对表的查询。例如：
21. SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = (SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 666 );
22. 12、通过内建函数提高SQL效率：
23. 复杂的SQL语句往往牺牲了执行效率。能够熟练掌握运用函数解决问题的方法在实际工作中是非常有意义的。
24. 13、使用表的别名（Alias）：
25. 当在SQL语句中连接多个表时，请使用表的别名并把别名首码于每个Column上，这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
26. 14、用EXISTS替代IN、用NOT EXISTS替代NOT IN：
27. 在许多基于基楚资料表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOT EXISTS）通常将提高查询的效率。在子查询中NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，我们可以把它改写成外连接（Outer Joins）或NOT EXISTS。
28. 例如：
29. （高效）SELECT * FROM EMP E WHERE E.EMPNO > 0 AND EXISTS( SELECT ‘X’ FROM DEPT D WHERE D.EDPTNO = E.DEPTNO AND D.LOC = ‘MELB’ );
30. （低效）SELECT * FROM EMP E WHERE E.EMPNO > 0 AND E.DEPTNO IN( SELECT D.DEPTNO FROM DEPT D WHERE LOC = ’MELB’ );
31. 15、识别‘低效’执行的SQL语句：
32. 虽然目前各种关于SQL优化的图形化工具层出不穷，但是写出自己的SQL工具来解决问题始终是一个最好的方法：
33. SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,
34. ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
35. ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
36. SQL_TEXT FROM V$SQLAREA
37. WHERE EXECUTIONS > 0
38. AND BUFFER_GETS > 0
39. AND (BUFFER_GETS-DISK_READS)/BUFFER_BETS < 0.8
40. ORDER BY 4 DESC;
41. 16、用索引提高效率：

42. 索引是表的一个概念部分，用来提高检索资料的效率，ORACLE使用了一个复杂的自平衡B-tree结构。通过索引查询资料比全资料表扫描要快。当ORACLE找出执行Select和Update语句的最佳路径时，ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键（primary key）的唯一性验证。那些LONG或LONG RAW资料类型，你可以索引几乎所有的列。通常，在大型表中使用索引特别有效。当然，你也会发现，在扫描小表时，使用索引同样能提高效率。跃然使用索引能等到查询效率的提高，但是我们也必须注意到它的代价。索引需要空间来存储，也需要定期维护，每当有记录在表中培养或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将主此多付出4至5次的磁片I/O。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的：

    ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

43. 17、用EXISTS 替换DISTINCT:

44. 当提交一个包含一对多表资讯（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换，EXISTS全查询更主迅速，因为RDBMS核心模组将在子查询的条件一旦满足后，立刻返回结果。例如：

    （高效）SELECT D.DEPT_NO,D.DEPT_NAME FROM DEPT D WHERE EXISTS( SELECT 1 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO );

    （低效）SELECT DISTINCT D.EDPT_NO,D.DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO=E.DEPT_NO;

45. 18、在java代码中尽量活用转接了“+”连接字串。

46. 19、避免在索引列上使用NOT，NOT会对索引列产生影响，当ORACLE”遇到”NOT，就会停止使用索引转而执行全资料表扫描。

47. 20、避免在索引列上使用计算：

48. WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全资料表扫描。例如：

    （高效）SELECT * FROM DEPT D WHERE D.SAL > 300000 / 12;

    （低效）SELECT * FROM DEPT D WHERE D.SAL * 12 > 300000;