Oracle 中sql的优化原则(转载)

SELECT . . .
FROM   emp E
WHERE NOT EXISTS ( SELECT X'
                       FROM   dept
                       WHERE dept_no = E.dept_no
                       AND    dept_cat = 'A'   );

提高SQL语句执行效率的原则和方法

 

1.1.Where子句中尽量不要使用is null或is not null的语句，不会使用索引

1.2.where子句　尽量不要将通配符（%）放在搜寻词首出现，通配符（%）在搜寻词首出现不会使用索引

1.3.where子句尽量少使用not或是<>,应该成< or >来实行

1.4.用not exists代替not in

 

 In要增加一个内部排序过程

例如：

SELECT . . .
FROM   emp
WHERE dept_no NOT IN ( SELECT dept_no
                           FROM   dept
                           WHERE dept_cat = 'A');

改用以下:

 

 

1.5.用EXISTS代替DISTINCT

例如：查询所有员工所在的部门

 

SELECT DISTINCT dept_no, dept_name
FROM   dept D,
       emp E
WHERE D.dept_no = E.dept_no ;
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
     SORT (UNIQUE)
       NESTED LOOPS
         TABLE ACCESS (FULL) OF 'EMP'
         TABLE ACCESS (BY ROWID) OF 'DEPT'
           INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)
优化后
 
   SELECT dept_no, dept_name
   FROM   dept D
   WHERE EXISTS ( SELECT 'X'
                   FROM   emp E
                   WHERE E.dept_no = D.dept_no );
   Execution Plan
   ---------------------------------------------------
   SELECT STATEMENT   OPTIMIZER HINT: CHOOSE
     FILTER
       TABLE ACCESS (FULL) OF 'DEPT'
       TABLE ACCESS (BY ROWID) OF 'EMP'
           INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX'

 

1.6.用IN 或 UNION 代替OR

 前提条件是各条件必须有索引时

 

例如：

loc_id 和 region 都是有索引的　

原语句：
   SELECT . . .                     SELECT . . .
   FROM   location                  FROM   location
   WHERE loc_id = 10               WHERE loc_id = 10
   OR     region = 'MELBOURNE'      OR     loc_id = 20
                                    OR     loc_id = 30
优化后
 
   SELECT . . .                     SELECT . . .
   FROM   location                  FROM   location
   WHERE loc_id = 10               WHERE loc_in IN (10,20,30)
   UNION
   SELECT . . .
   FROM   location
   WHERE region = 'MELBOURNE'

 

1.7.使用OR时应该把结果集小的条件放在前面

例如：

 

WHERE key1 = 10         结果集最小的条件

OR    key2 = 20         结果集最大的条件

 

实际执行会被转换成下面形式，所以要把结果集小的条件放在where表达式前面

 

WHERE key1 = 10

AND ( key1 NOT = 10 AND key2 = 20 )

1.8.order by　子句中尽量不要使用非索引列

1.9.Where子句中不要使用列联接，如：name||''||last_name

1.10.    通过分析SQL语句执行计划来优化语句,可用PLSql来查看执行计划，看是否有使用索引，是否发生全表扫描等

1.11.    SQL语句中必须使用占位符

1.12.    使rowid来作为delete和update的条件 rowid直接指向数据文件块的位置速底最快

1.13.    UNION改用UNION ALL ，UNION要对合并的结果进行排序，UNION ALL不排序

1.14.    表连接和exists子查询：

用表连接：

　　两个关联表的各自where条件地的结果集大小差不多时用表连接

用exists子查询

两个关联表的各自where条件地的结果集一大一小时，用exist子查询

且如果条件是用and 关系时exist子查询要放在where条件的第一位,如果是or关系则exist子查询要放在where条件的最后一个。

例如：

SELECT . . .
FROM   emp E
WHERE EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE dept_no = E.dept_no
                       AND    dept_cat = 'A' )
AND    E.emp_type = `MANAGER'
SELECT . . .
FROM   emp E
WHERE E.emp_type = 'MANAGER'
OR     EXISTS     ( SELECT 'X'
                       FROM   dept
                       WHERE dept_no = E.dept_no
                       AND    dept_cat = 'A' )

 

1.15.    用DECODE 减少处理

The DECODE statement provides a way to avoid having to scan the same rows repetitively, or to join the same table repetitively. Consider the following example:

 

SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE dept_no = 0020
AND    emp_name LIKE 'SMITH%' ;
SELECT COUNT(*), SUM(salary)
FROM   emp
WHERE dept_no = 0030
AND    emp_name LIKE 'SMITH%' ;
You can achieve the same result much more efficiently with DECODE:
 
SELECT COUNT(DECODE(dept_no, 0020, 'X',    NULL)) D0020_kount,
       COUNT(DECODE(dept_no, 0030, 'X',   NULL)) D0030_kount,
       SUM (DECODE(dept_no, 0020, salary, NULL)) D0020_sal,
       SUM (DECODE(dept_no, 0030, salary, NULL)) D0030_sal
FROM   emp
WHERE emp_name LIKE 'SMITH%';