No SQL，No cost。SQL语句是造成数据库开销最大的部分。而不良SQL写法直接导致数据库系统性能下降的情形比比皆是。那么如何才能称得上高效的SQL语句呢？一是查询优化器为当前的SQL语句生成最佳的执行计划，保证数据读写使用最佳路径；二是设置合理的物理存储结构，如表的类型，字段的顺序，字段的数据类型等。本文主要描述如何编写高效的SQL语句并给出示例。下面的描述主要分为三个部分，一是编写高效SQL语句，二是使用索引提高查询性能的部分，三是总结部分。
一、编写高效SQL语句
1）选择最有效的表名顺序（仅适用于RBO模式）
ORACLE的解析器总是按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中最后的一个表将作为驱动表被优先处理。当FROM子句存在多个表的时候，应当考虑将表上记录最少的那个表置于FROM的最右端作为基表。Oracle会首先扫描基表（FROM子句中最后的那个表）并对记录进行排序，然后扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。
下面的例子使用最常见的scott或hr模式下的表进行演示
表 EMP 有14条记录
表 DEPT 有4条记录
SELECT /*+ rule */ COUNT( * ) FROM emp, dept; --高效的写法 scott@CNMMBO> set autotrace traceonly stat; Elapsed: 00:00:00.14 Statistics SELECT /*+ rule */ COUNT( * ) FROM dept, emp; --低效的写法 Elapsed: 00:00:00.02 Statistics |
2）select 查询中避免使用'*'
当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 '*' 是一个方便的方法.不幸的是，这是一个非常低效的方法.实际上，ORACLE在解析的过程中， 会将 '*' 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。
注：本文中的例子出于简化演示而使用了select * ，生产环境应避免使用。
3）减少访问数据库的次数
每当执行一条SQL语句，Oracle 需要完成大量的内部操作，象解析SQL语句，估算索引的利用率，绑定变量， 读数据块等等.由此可见，减少访问数据库的次数，实际上是降低了数据库系统开销
-->下面通过3种方式来获得雇员编号为7788与7902的相关信息
-->方式 1 （最低效）：
| select ename,job,sal from emp where empno=7788; select ename,job,sal from emp where empno=7902; |
-->方式 2 （次低效）：
-->下面使用了参数游标来完成，每传递一次参数则需要对表emp访问一次，增加了I/O
| DECLARE CURSOR C1(E_NO NUMBER) IS SELECT ename, job, sal FROM emp WHERE empno = E_NO; BEGIN OPEN C1 (7788); FETCH C1 INTO …, …, …; .. OPEN C1 (7902); FETCH C1 INTO …, …, …; CLOSE C1; END; |
-->方式 3 （最高效）
| SELECT a.ename , a.job , a.sal , b.ename , b.job , b.sal FROM emp a, emp b WHERE a.empno = 7788 OR b.empno = 7902; |
注意：在SQL*Plus，SQL*Forms和Pro*C中重新设置ARRAYSIZE参数，可以增加每次数据库访问的检索数据量，建议值为200.
4）使用DECODE函数来减少处理时间
-->使用decode函数可以避免重复扫描相同的行或重复连接相同的表
| select count(*),sum(sal) from emp where deptno=20 and ename like 'SMITH%'; select count(*),sum(sal) from emp where deptno=30 and ename like 'SMITH%'; |
-->通过使用decode函数一次扫描即可完成所有满足条件记录的处理
| SELECT COUNT( DECODE( deptno, 20, 'x', NULL ) ) d20_count , COUNT( DECODE( deptno, 30, 'x', NULL ) ) d30_count , SUM( DECODE( deptno, 20, sal, NULL ) ) d20_sal , SUM( DECODE( deptno, 30, sal, NULL ) ) d30_sal FROM emp WHERE ename LIKE 'SMITH%'; |
类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。
