oracle的一些调优建议（3）

15. 用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成NOT EXISTS.
 例如:

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 D
                    WHERE D.DEPT_NO = E.DEPT_NO
                    AND DEPT_CAT = ‘A’); 

16.用表连接替换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’ ;

17.用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核心模块将在子查询的条件一旦满足后,立刻返回结果.


18.用索引提高效率

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.
除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率.
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来
存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.
注:
定期的重构索引是有必要的.
ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>


19. 索引的操作

ORACLE对索引有两种访问模式.
1. 索引唯一扫描 ( INDEX UNIQUE SCAN)
大多数情况下, 优化器通过WHERE子句访问INDEX.
例如:
表LODGING有两个索引 : 建立在LODGING列上的唯一性索引LODGING_PK和建立在MANAGER列上的非唯一性索引LODGING$MANAGER.

SELECT * 
FROM LODGING
WHERE LODGING = ‘ROSE HILL’;

在内部 , 上述SQL将被分成两步执行, 首先 , LODGING_PK 索引将通过索引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方式 执行下一步检索.
如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.
下面SQL只需要INDEX UNIQUE SCAN 操作.

SELECT LODGING
        FROM  LODGING
WHERE LODGING = ‘ROSE HILL’;

2. 索引范围查询(INDEX RANGE SCAN)
适用于两种情况:
       基于一个范围的检索
       基于非唯一性索引的检索
例1:

SELECT LODGING
      FROM  LODGING
WHERE LODGING LIKE ‘M%’;

WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询LODGING_PK . 索引范围查询将返回一组值, 它的效率要比索引唯一扫描低一些.


19. 索引的操作

例2:

SELECT LODGING
      FROM  LODGING
WHERE MANAGER = ‘BILL GATES’;

  这个SQL的执行分两步, LODGING$MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LODGING列的值. 由于LODGING$MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.
由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中, 所以在索引范围查询后会执行一个通过ROWID访问表的操作.
WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT LODGING
      FROM  LODGING
WHERE MANAGER LIKE ‘％HANMAN’;

在这种情况下，ORACLE将使用全表扫描.

21.避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．
举例:
低效：

SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;

高效:

SELECT …
FROM DEPT
WHERE SAL  > 25000/12;

译者按:
这是一个非常实用的规则，请务必牢记


22.自动选择索引


如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．
在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引．
举例:

SELECT ENAME
FROM EMP
WHERE EMPNO = 2326  
AND DEPTNO  = 20 ;

这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．

TABLE ACCESS BY ROWID ON EMP
       INDEX UNIQUE SCAN ON EMP_NO_IDX 

23.避免在索引列上使用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  >