oracle Sql优化原则与方法

 

提高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');

改用以下:

 

SELECT . . .

FROM    emp E

WHERE NOT EXISTS ( SELECT X'

                       FROM   dept

                       WHERE dept_no = E.dept_no

                       AND    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%';