ORACLE数据库SQL优化

SQL优化
由于ORACLE体系结构的关系，不同SQL写法，对性能影响关系比较大，所以遵循规范的SQL写法是我们进行SQL性能调整的重要一环。

以下列出个人认为比较重要的规则，其他SQL写法规则，请参考其他文档。

1 对查询条件字段创建索引
这是最立竿见影的操作，也是sql优化的基础。

2 选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
例如:
     表 TAB1 16,384 条记录
     表 TAB2 1      条记录
      选择TAB2作为基础表 (最好的方法)
     select count(*) from tab1,tab2   执行时间0.96秒
     选择TAB2作为基础表 (不佳的方法)
     select count(*) from tab2,tab1   执行时间26.09秒
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.
例如:
    EMP表描述了LOCATION表和CATEGORY表的交集.
  SELECT * FROM LOCATION L ,
      CATEGORY C,
      EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000
AND E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN

将比下列SQL更有效率
SELECT * FROM EMP E ,
LOCATION L ,
      CATEGORY C
WHERE  E.CAT_NO = C.CAT_NO
AND E.LOCN = L.LOCN
AND E.EMP_NO BETWEEN 1000 AND 2000

3 WHERE子句中的连接顺序．
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
例如:
(低效,执行时间156.3秒)
SELECT …
FROM EMP E
WHERE  SAL > 50000
AND    JOB = ‘MANAGER’
AND    25 < (SELECT COUNT(*) FROM EMP
             WHERE MGR=E.EMPNO);
(高效,执行时间10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
             WHERE MGR=E.EMPNO)
AND    SAL > 50000
AND    JOB = ‘MANAGER’;
 
4 SELECT子句中避免使用 ‘ * ‘
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 ‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
   
5 计算记录条数
     和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)
(译者按: 在CSDN论坛中,曾经对此有过相当热烈的讨论, 作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)

6 用EXISTS替代IN（重点）
IN 操作符，当有多个条件值时，索引失效，性能不佳。

7 用NOT EXISTS替代NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).  为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
                         FROM DEPT
                         WHERE DEPT_CAT=’A’);
为了提高效率.改写为:
(方法一: 高效)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A’

(方法二: 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
                    FROM DEPT D
                    WHERE D.DEPT_NO = E.DEPT_NO
                    AND DEPT_CAT = ‘A’);

20.       用表连接替换EXISTS
      通常来说 , 采用表连接的方式比EXISTS更有效率
      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’ ;
(译者按: 在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)

8 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换
例如:
低效:
    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);
   EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

9 避免在索引列上使用计算（重点）
WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
   举例:
低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;

高效:
SELECT …
FROM DEPT
WHERE SAL  > 25000/12;
译者按:
这是一个非常实用的规则，请务必牢记

10 避免在索引列上使用NOT
通常，　我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的
影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
   举例:
    低效: (这里,不使用索引)
    SELECT …
   FROM DEPT
   WHERE DEPT_CODE NOT = 0;
  
   高效: (这里,使用了索引)
   SELECT …
   FROM DEPT
   WHERE DEPT_CODE > 0;
    需要注意的是,在某些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符.
   NOT >  to  <=
   NOT >=  to  <
   NOT <  to  >=
   NOT <=  to  >
译者按:
     在这个例子中,作者犯了一些错误. 例子中的低效率SQL是不能被执行的.
我做了一些测试:
SQL> select * from emp where NOT empno > 1;
no rows selected
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)    
SQL> select * from emp where empno <= 1;
no rows selected
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   2    1     INDEX (RANGE SCAN) OF 'EMPNO' (UNIQUE)
       两者的效率完全一样，也许这符合作者关于” 在某些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符” 的观点．
     
11 用>=替代> （重点）
如果DEPTNO上有一个索引,

高效:
  SELECT *
   FROM EMP
   WHERE DEPTNO >=4
  
   低效:
   SELECT *
   FROM EMP
   WHERE DEPTNO >3

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

12 避免在索引列上使用IS NULL和IS NOT NULL（重点）
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录.　如果至少有一个列不为空，则记录存在于索引中．
举例:
  如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入). 然而如果
所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空. 因此你可以插入1000
条具有相同键值的记录,当然它们都是空!
       因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.
举例:

低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;

高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;

注：这条规则告诉我们，一个字段的值，尽量不要设置为NULL，一般设置一个默认值，也不要让他为空。这条对大数据量的时候，效果非常明显。

13 避免对查询条件字段使用函数trim、to_date、to_char

低效率
SELECT O.*,g.all_group_name GROUPNAME ,
case when (select count(*) from pw_complete_odd where trim(o.code) = trim(item_yk_code) and isdeleted='N' ) > 0 then 1 else 0 end as hasBill
FROM PW_PM_YK_OTHER O,eqm_cd_ssdw g
WHERE O.GROUP_ID = G.GROUP_ID and O.ISDELETED = 'N' ORDER BY O.CREATE_TIME DESC

如果字段中确实有空格，需要去掉空格操作，那么尽量放到程序中处理，然后不用trim函数；
SELECT o.*,g.all_group_name groupname ,
case when (select count(*) from pw_complete_odd where o.code = item_yk_code and isdeleted='n' ) > 0 then 1 else 0 end as hasbill
FROM pw_pm_yk_other o,eqm_cd_ssdw g
WHERE o.group_id = g.group_id and o.isdeleted = 'n' order by o.create_time desc

14 使用UNION ALL 由于UNION
UNION ALL：不需要去掉重复记录
UNION：去掉重复记录
所以UNION ALL 性能要远优于UNION

15 避免改变索引列的类型.
当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换.
假设 EMPNO是一个数值类型的索引列.
SELECT …
FROM EMP
WHERE EMPNO = ‘123’

实际上,经过ORACLE类型转换, 语句转化为:
SELECT …
FROM EMP
WHERE EMPNO = TO_NUMBER(‘123’)

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变.
现在,假设EMP_TYPE是一个字符类型的索引列.
SELECT …
FROM EMP
WHERE EMP_TYPE = 123
这个语句被ORACLE转换为:
SELECT …
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换, 这个索引将不会被用到!
操作：
如果EMP_TYPE为字符型的，一定要这样写
SELECT …
FROM EMP
WHERE EMPNO = ‘123’   一定要带引号


译者按:为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE会优先转换数值类型到字符类型.

16 需要当心的WHERE子句
某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.
在下面的例子里, ‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.
不使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >0;

下面的例子中, ‘||’是字符连接函数. 就象其他函数那样, 停用了索引.
不使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
使用索引:
SELECT ACCOUNT_NAME,AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = ‘AMEX’
AND  ACCOUNT_TYPE=’ A’;

下面的例子中, ‘+’是数学函数. 就象其他数学函数那样, 停用了索引.
不使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000;
使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ;
下面的例子中,相同的索引列不能互相比较,这将会启用全表扫描.
不使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);
使用索引:
SELECT ACCOUNT_NAME, AMOUNT
FROM TRANSACTION
WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);
译者按:
如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好的方案.
CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函数的索引*/
SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/

17 分离表和索引
总是将你的表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上.
“同时,确保数据表空间和索引表空间置与不同的硬盘上.”可能改为如下更为准确 “同时,确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上.”