ORACLE的其他建议

1、列比较时尽量保持类型一致

数值型列和文字列比较的时候，文字列会自动的转换成数值型，但是这样的处理会使基于索引的检索无效，从而降低性能。 例：

对：~ WHERE emp_no = 123 (emp_no建有索引的数值列) 可能不对：~ WHERE emp_no = ‘123’ (emp_no建有索引的数值列) 注）ORACLE在实际执行中会做~ WHERE emp_no = TO_NUMBER(‘123’)的转换而可能导致索引不被使用 对：~ WHERE emp_type = ‘123’ (emp_tepe建有索引的文字列)

错：~ WHERE emp_type = ‘123’ (emp_tepe建有索引的文字列) 注）ORACLE在实际执行中会做~ WHERE TO_NUMBER(emp_no) = ‘123’的转换而导致索引使用无效

2、尽量避免使用子查询

不使用子查询的SQL访问路径上自由度增大，处理性能上也提升不少，所以原则上不使用子查询而通过考虑是否能用通常的结合处理来代替。

错：SELECT e.ename FROM emp e, (SELECT d1.deptno FROM dept d1 WHERE d1.deptno = ‘A’) d WHERE e.deptno = d.deptno;

对：SELECT e.ename FROM emp e, dept d WHERE e.deptno = d.deptno AND d.deptno = ‘A’;

3、对子查询层次限定

在子查询中再嵌套子查询会使ORACLE的实行计划变得更加复杂，随着子查询的重度越高执行不确切的实行计划的可能性越高。因此，尽量使避免使用嵌套子查询。

4、尽量用NOT EXISTS代替NOT IN + 子查询

在子查询中使用了NOT IN演算后，会发生内部排序、合并处理，为了提高性能，可以用NOT EXISTS来代替NOT IN + 子查询。

错：SELECT e.ename FROM emp e WHERE e.deptno NOT IN(SELECT d.deptno FROM dept d WHERE d.deptno = e.deptno AND d.dept_cat = ’A’) ;

对：SELECT e.ename FROM emp e WHERE NOT EXISTS(SELECT ‘X’ FROM dept d WHERE d.deptno = e.deptno AND d.dept_cat = ’A’);

5、用EXISTS替换DISTINCT

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

低效：SELECT DISTINCT dept_no,dept_name FROM dept d,emp e  WHERE d.dept_no = e.dept_no;

高效：SELECT dept_no,dept_name FROM dept d  WHERE EXISTS ( SELECT ‘X’ FROM emp e WHERE e.dept_no = d.dept_no);

6、用表连接替换EXISTS

通常来说 ， 采用表连接的方式比EXISTS更有效率 注）在RBO的情况下，前者的执行路径包括FILTER，后者使用NESTED LOOP

低效：SELECT ename  FROM emp e  WHERE EXISTS (SELECT ‘X’  FROM dept  WHERE dept_no = e.dept_no AND DEPT_CAT = ‘A’);

高效：SELECT ename FROM dept d, emp e  WHERE e.dept_no = d.dept_no AND dept_cat = ‘A’ ;

7、尽量不使用HAVING子句

HAVING子句是把全部的纪录选中之后，对选择的纪录再按条件进行过滤，相比之下用WHERE子句来对选择的纪录进行过滤性能更胜一筹，所以在用HAVING子句的时候考虑一下是不是能够移到WHERE子句中实现。

错： SELECT e.deptno AVG(e.sal) FROM emp e GROUP BY e.deptno HAVING e.deptno > 10;

对： SELECT e.deptno AVG(e.sal) FROM emp e WHERE e.deptno > 10 GROUP BY e.deptno;

8、基础表的选择

基础表（Driving Table，也被叫做驱动表）是指被最先访问的表（通常以全表扫描的方式被访问）。根据优化器的不同，SQL语句中基础表的选择是不一样的。

CBO模式下，优化器会检查SQL语句中的每个表的物理大小，索引的状态，然后选用花费最低的执行路径。

RBO模式下，表结合的条件全部相同的情况下，FROM子句中从右到左结合，所FROM子句最右边的表为基表。

因为基表纪录数越少性能越好，所以FROM子句中按纪录数从大到小顺序指定结合表。 注）但是条件指定后件数变化的情况下，按照变化后的件数从大到小来指定。

CBO例：

SELECT a.name，b.manager FROM worker a，lodging b  WHERE a.loding = b.loding;

由于lodging表的loding列上有一个索引， 而且worker表中没有相比较的索引，worker表将被作为查询中的基础表。

RBO例：

错：~ FROM a,b,c; (纪录数 c < b < a)

对：~ FROM c,b,a; (纪录数 c < b < a)

9、WHERE子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

错：SELECT … FROM emp e WHERE sal > 50000 AND  job = ‘MANAGER’ AND  25 < (SELECT COUNT(*) FROM emp WHERE mgr=e.empno);

对：SELECT … FROM emp e WHERE 25 < (SELECT COUNT(*) FROM emp WHERE mgr=e.empno) AND  sal > 50000 AND  job = ‘MANAGER’;

10、ROWID的使用

在通过SELECT结果作为条件来DELTE或者UPDATE的场合，使用ROWID作为条件能减轻负荷。 注）ROWID是数据库中实际的ORACLE数据块对应的物理纪录番号，通过ROWID检索是最快的检索方式。 错：SELECT e.ename INTO:emp_ename FROM emp e WHERE e.emp_no = 123 FOR UPDATE OF e.ename; UPDATE emp e SET e.ename = ‘XXX’WHERE e.emp_no = 123;

对：SELECT e.ename e.rowid INTO:emp_ename,:emp_rowid FROM emp e WHERE e.emp_no = 123 FOR UPDATE OF e.ename; UPDATE emp e SET e.ename = ‘XXX’WHERE ROWID = e.emp_rowid;

11、用ROWNUM判断纪录是否存在

记录存在与否用ROWNUM<=1来判断效率更高，因为当指定ROWNUM<=1的时候，只要一条记录被找到SQL的执行就结束了，所以能恒号的提升执行的速度。

错：SELECT ‘X’FROM dual WHERE EXISTS(SELECT ‘X’FROM emp WHERE sal > 100);

对：SELECT ‘X’FROM dual WHERE sal > 100 AND ROWNUM<=1;

12、翻页SQL(下N件记录取得)的写法

在取下N件纪录的时候，在FORM子句中用ROWNUM限定结果集，然后再在WHERE句中的指定所取件数，这样会大大减少response的性能。

错：SELECT row_num,empno,name FROM (SELECT ROWNUM row_num, empno,name WHERE emp) WHERE row_num >= 6 AND row_num <= 10;

对：SELECT row_num,empno,name FROM (SELECT ROWNUM row_num, empno,name WHERE emp ROWNUM <= 10) WHERE row_num >= 6;

13、MERGE的使用

MERGE是ORACLE 9i之后才用的新机能，对应条件的纪录存在的时候执行UPDATE,不存在的时候执行INSERT处理。使用MERGE可以简化编程工作量及复杂度。

错：DECLARE  CURSOR dept_cur IS SELECT * FROM dept FOP UPDATE;

 wk dept_cur%ROWTYPE;  

BEGIN  FOR wk IN dept_cur LOOP  UPDATE dept2 SET dname = wk.dname WHERE deptno = wk.deptno;  

IF SQL%NOTFOUND THEN  INSERT INTO dept2 VALUES(wk.deptno,dname,wk.loc);  

END IF

 END LOOP;

 END;

对：MERGE INTO dept2 d2  USING dept d  ON (d2.deptno = d.deptno)

 WHERE MATCHED THEN  UPDATE SET d2.dname = d.dname  

WHEN NO MATCHED THEN  INSERT(d2.deptno,d2.dname,d2.loc) VALUES(d.deptno,d.name,d.loc);

14、多表插入的使用

同一数据源插入复数个表，这在ORACLE 9i之前需要执行复数个单独的INSERT ~ SELECT语句，但是在ORACLE 9i以后我们可以用多表插入功能，只要执行一次INSERT文就可以向复数的表中插入数据。

过去： INSERT INTO sales_month_a(month,sales) SELECT month,SUM(sales) FROM sales WHERE prod_id = ‘PROD_A’ GROUP BY month;

INSERT INTO sales_month_b(month,sales) SELECT month,SUM(sales) FROM sales WHERE prod_id = ‘PROD_B’ GROUP BY month;

现在： INSERT INTO FIRST  WHEN prod_id = ‘PROD_A’INTO sales_month_a(month,sales) VALUES(month,slaes)  WHEN prod_id = ‘PROD_B’INTO sales_month_b(month,sales) VALUES(month,slaes) SELECT month,SUM(sales) FROM sales GROUP BY month;

15、DBlink使用的限制

分散DB环境中，通过DBlink使用不同DB中的表进行结合处理，会因为传送数据量的过多而造成性能严重低下。所以尽量用其他方法来替换不同DB中的表结合处理。

16、尽量用DECODE代替集合函数

为了减轻集合函数中的总数或合计的计算的负荷，尽量使用DECODE来减少处理时间。使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

错：SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=10;  SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=20;

对：SELECT COUNT(DECODE(deptno,10,’D10’)) “COUNT-10”,  COUNT(DECODE(deptno,20,’D20’)) “COUNT-20”, SUM(DECODE(deptno,10,sal,NULL) “SUM-10”, SUM(DECODE(deptno,20,sal,NULL) “SUM-20” FROM emp;

17、删除重复记录

最高效的删除重复记录方法 （ 因为使用了ROWID） DELETE FROM emp e WHERE E.ROWID > (SELECT MIN(x.ROWID)  FROM emp x  WHERE x.emp_no = e.emp_no);

18、减量减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询。 错：SELECT tab_name  FROM tables  WHERE tab_name = ( SELECT tab_name FROM tab_colums  WHERE version = 604)  AND db_ver= ( SELECT db_ver  FROM tab_colums  WHERE version = 604);  对：SELECT tab_name  FROM tables  WHERE (tab_name, db_ver) =  ( SELECT tab_name, db_ver)  FROM tab_colums  WHERE version = 604);

19、避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎，执行耗费资源的排序(SORT)功能。

DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。 例如一个UNION查询，其中每个查询都带有GROUP BY子句，GROUP BY会触发嵌入排序(NESTED SORT)；

这样，每个查询需要执行一次排序，然后在执行UNION时，又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行。

嵌入的排序的深度会大大影响查询的效率。通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以用其他方式重写。

案例一

 

使用提示(Hints)： 在对ORACLE优化器缺省的执行路径不满意时，需要手工修改执行路径，使用hint，这是一个很有技巧性的工作。建议只针对特定的，少数的SQL进行hint的优化。

案例二

使用提示(Hints)： 在对ORACLE优化器缺省的执行路径不满意时，需要手工修改执行路径，使用hint，这是一个很有技巧性的工作。建议只针对特定的，少数的SQL进行hint的优化。