Oracle如何写出高效的SQL

转载：http://www.blogjava.net/ashutc/archive/2009/07/19/277215.html

1、选择最有效率的表明顺序（只在基于规则的优化器中有效）

　　Oracle的解析器按照从右到左的顺序处理FROM 子句中的表名，FROM 子句中写在最后的表（基础表 driving table）将被最先处理，在FROM 子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。假如有3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表

2、WHERE 子句中的连接顺序

　　Oracle 采用自下而上的顺序解析 WHERE子句，根据这个原理，表之间的连接必须写在其他的 WHERE 条件之前，那些可以过滤掉最大数量记录的条件必须写在 WHERE子句的末尾（保证最先处理的条件能过滤掉最多的记录，使得之后的条件处理能够处理尽可能少的记录）

3、SELECT 子句中避免使用 "*"

　　Oracle 在解析的过程中，会将 * 一次转换成所有的列名，这个工作时通过查询数据字典完成的，这意味着将耗费更多的时间

4、减少访问数据库的次数

　　Oracle 在内部执行了许多工作：解析SQL 语句、估算索引的利用率、绑定变量、读数据块等

5、在 SQL*Plus、SQL*Forms 和 Proc*C中重新设置 ARRAYSIZE参数，可以增加每次数据库访问的索引数据量，建议值为200

6、使用 DECODE 函数来减少处理时间

　　使用DECODE 函数可以避免重复扫描相同记录或重复连接相同的表

7、整合简单、无关联的数据库访问

　　假如你有几个简单的数据库查询语句，你可以把它们整合到一个查询中（即使它们之间没有关系）

8、删除重复记录

　　最高效的删除重复记录的方法（因为使用了 ROWID）例子

DELETE FROM EMP E 
WHERE E.ROWID > (
    SELECT MIN(X.ROWID)
    FROM EMP X
    WHERE X.EMP_NO = E.EMP_NO)

9、用 TRUNCATE代替 DELETE

　　当删除表中的记录时，在通常情况下，回滚段（rollback segments）用来存放可以被恢复的信息，假如你没有 COMMINT 事务，Oracle会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状态），而当运用 TRUNCATE时，回滚段不再存放任何可被恢复的信息。当命令运行之后，数据不能被恢复，因此很少的资源被调用，执行时间也会很短

　　但是TRUNCATE 只在删除全表使用，TRUNCATE 是DDL 不是DML

10、尽可能多使用 COMMIT

　　只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提升，需求也会因为 COMMIT所释放的字段而减少

　　COMMIT所释放的资源：

a. 回滚段上用于恢复数据的信息

b. 被程序语句获得的锁

c. redo log buffer 中的空间

d. Oracle为治理上述3 中资源中的内部花费

11、用WHERE 子句替代 HAVING子句

　　避免使用 HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序、总计等操作。假如能通过 WHERE子句限制记录的数目，那就能减少这方面的开销。（非Oracle中）on、where、having这单个都可以加条件的子句中，on是最先执行，where次之，having最后。

　　因为on 是先把不符合条件的记录过滤之后才进行统计，它就可以减少中间运算要处理的数据，按理说应该是最快的。

　　where也应该比having 快，因为它过滤数据后才进行sum ，在两个表联接时才用 on的，所以在一个表的时候，就剩下where跟 having比较了。

　　在单表查询统计的情况下，假如要过滤的条件没有涉及到计算字段，那它们的结果是一样的，只是 where可以使用 rushmore 技术，而having 就不能，在速度上后者要慢。假如要涉及到计算的字段，就表示在没有计算之前，这个字段的值是不确定的，根据上面写的工作流程，where的作用时间是在计算之前就完成的，而having 就是在计算之后才起作用的，所以在这种情况下，两者的结果会不同。

　　在夺标联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表之后，再由where 进行过滤，然后再计算，计算完成之后再由 having 进行过滤。

　　由此可见，要想过滤条件起到正确的作用，首先要明报这个条件应该在什么时候起作用，然后在决定放在哪里

12、减少对表的查询

　　在含有子查询的SQL 语句中，要非常注重减少对表的查询，例子

SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER)=(
    SELECT TAB_NAME, DB_VER
    FROM TAB_COLUMNS
    WHERE VERSION=604
    )

13、通过内部函数提高SQL 效率

　　复杂的SQL 往往牺牲了执行效率。能够把握上面的运算函数解决问题的方法在实际工作中往往是非常有意义的

14、使用表的别名（Alias）

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

15、用 EXISTS 替代IN；用 NO EXISTS 替代NOT IN

　　在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用EXISTS（或者NOT EXISTS）通常将提高查询的效率，在子查询中，NOT IN子句将执行一个内部的排序和合并。

　　无论在哪种情况下，NOT IN都是低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOT IN，可以把它改成外连接（Outer Joins）或者 NOT EXISTS

　　高效的SQL实例

SELECT* FROM EMP(基础表)
WHERE EMPNO>0 AND EXISTS(
    SELECT ‘X‘
    FROM DEPT
    WHERE DEPT.DEPTNO=EMP.DEPTNO AND LOC=‘MELB‘
    )

　　低效的SQL实例

SELECT * FROM EMP(基础表)
WHERE EMPNO>0 AND DEPTNO IN(
    SELECT DEPTNO
    FROM DEPT
    WHERE LOC=‘MELB‘
    )

16、识别“低效执行”的SQL 语句

　　虽然目前各种关于SQL 优化的图形化工具层出不穷，但是写出自己的SQL 工具来解决问题始终是一个最好的方法

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC; 

17、用索引提高效率

　　索引是表的一个概念部分，用来提高检索数据的效率，Oracle 使用了一个复杂的自平衡 B-Tree 结构。通常，通过所以查询数据比全表扫描要快。

　　当Oracle 找出执行查询和 Update语句的最佳路径时，Oracle优化器将使用索引。

　　同样在联结多个表时使用索引也能提高效率。

　　另一个使用索引的好处是，它提供了主键（PRIMARY KEY）的唯一性验证。那些LONG 或 LONG RAW 数据类型，你可以索引几乎所有的列。

　　通常，在大型表中使用索引能得到查询效率的提高，但是我们也必须注重到它的代价。所以需要空间来储存，也需要定期维护，每当有记录在表中增减或索引列被修改时，缩印本身也被修改。这意味着没条记录的INSERT、DELETE、UPDATE将为此多付出4、5 次的磁盘I/O。

　　因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询时间变慢。定期的重构索引是很有必要的

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

18、用EXISTS 替代 DISTINCT

　　当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在SELECT 子句中使用DISTINCT。一般可以考虑使用 EXISTS替换，EXISTS是查询更为迅速，因为 RDBMS 核心模块将在子查询的条件一单满足后，马上返回结果

　　低效的SQL 实例

SELECT DISTINCT DEPT_NO, DEPT_NAME
FROM DEPT D, EMP E
WHERE D.DEPT_NO=E.DEPT_NO

　　高效的SQL 实例

SELECT DEPT_NO, DEPT_NAME
FROM DEPT D
WHERE EXISTS (
    SELECT ‘X‘ 
    FROM EMP E
    WHERE E.DEPT_NO=D.DEPT_NO
    )

19、SQL 语句用大写的

　　因为 Oracle总是先解析SQL语句，把小写的字母转换成大写的在执行

20、在Java 代码中尽量少使用连接符 “+” 连接字符串

21、避免在索引列上使用 NOT

　　通常，我们在索引列上使用 NOT，NOT会产生在和在索引列上使用函数相同的印影响。当Oracle“碰到” NOT，它就会停止使用索引转而窒息感全表扫描

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

　　WHERE 子句中，假如索引列是函数的一部分，优化器将不再使用索引而使用全表扫描

　　低效的SQL 实例

SELECT ...
FROM DEPT
WHERE SQL*12>25000

　　高效的SQL 实例

SELECT ...
FROM DEPT
WHERE SAL > 25000/12

23、用>= 替代 >

　　高效的SQL 实例

SELECT *
FROM EMP
WHERE DEPTNO>=4

　　低效的SQL 实例

SELECT * 
FROM EMP 
WHERE DEPTNO >3

　　两者的区别在于，前置DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录. 

　　同理，用 <= 替代 <

24、用 UNION 替代 OR（适用于索引列）

　　通常情况下，用UNION 替换 WHERE子句中的OR 将会起到很好的效果。对索引列使用 OR 将造成全表扫描。

　　注意，以上的规则只针对多个索引列有效。假如有 column没有被索引，查询效率可能因为你没有选择OR而降低。下面的例子中，LOC_ID和 REGION上都建立索引

　　高效的SQL 实例

SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID=10 
UNION
SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE REGION=‘MELBOURNE‘

　　低效的SQL 实例

SELECT LOC_ID, LOC_DESC, REGION
FROM LOCATION
WHERE LOC_ID=10 OR REGION=‘MELBOURNE‘

　　假如你坚持要用 OR，那就需要返回记录最少的索引列写在最前面

25、