oracle toomanyrows_Oracle索引优化，真正做到高效查询

索引是表的一个概念部分,用来提高检索数据的效率. 实际上,ORACLE

使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和

Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证. 除了那些 LONG或

LONG RAW数据类型, 你可以索引几乎所有的列.

通常, 在大型表中使用索引特别有效.

当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改.

这意味着每条记录的 INSERT ,

DELETE , UPDATE将为此多付出

4 , 5 次的磁盘 I/O .

因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

注意:

定期的重构索引是有必要的.

ALTER INDEX

REBUILD

一. 索引的操作

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’;

索引范围查询(INDEX RANGE

SCAN)

适用于两种情况:

1. 基于一个范围的检索

2. 基于非唯一性索引的检索

例

1:

SELECT LODGING

FROM LODGING

WHERE LODGING LIKE

‘M%’;

WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询

LODGING_PK .

由于索引范围查询将返回一组值,

它的效率就要比索引唯一扫描低一些.

例 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将使用全表扫描.

二. 基础表的选择

基础表(Driving

Table)是指被最先访问的表(通常以全表扫描的方式被访问). 根据优化器的不同,

SQL语句中基础表的选择是不一样的. 如果你使用的是 CBO (COST BASED

OPTIMIZER),优化器会检查

SQL语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径. 如果你用 RBO (RULE BASED

OPTIMIZER) , 并且所有的连接条件都有索引对应,

在这种情况下,

基础表就是 FROM

子句中列在最后的那个表.

举例:

SELECT A.NAME , B.MANAGER

FROM WORKER A,

LODGING B

WHERE A.LODGING = B.LODING;

由于 LODGING

表的 LODING

列上有一个索引,

而且 WORKER

表中没有相比较的索引,

WORKER表将被作为查询中的基础表.

三. 多个平等的索引

当 SQL

语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE 会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录.

在 ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有当 WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的. 如果不同表中两个想同等级的索引将被引用,

FROM 子句中表的顺序将决定哪个会被率先使用.

FROM子句中最后的表的索引将有最高的优先级.

如果相同表中两个想同等级的索引将被引用,

WHERE 子句中最先被引用的索引将有最高的优先级.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME,

FROM EMP

WHERE DEPT_NO = 20

AND EMP_CAT = ‘A’;

这里,DEPTNO

索引将被最先检索,然后同

EMP_CAT 索引检索出的记录进行合并.

执行路径如下:

TABLE ACCESS BY ROWID ON EMP

AND-EQUAL

INDEX RANGE SCAN ON DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

四. 等式比较和范围比较

当 WHERE子句中有索引列, ORACLE不能合并它们,ORACLE将用范围比较.

举例:

DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引.

SELECT ENAME

FROM EMP

WHERE DEPTNO > 20

AND EMP_CAT =

‘A’;

这里只有 EMP_CAT

索引被用到,然后所有的记录将逐条与DEPTNO 条件进行比较.

执行路径如下:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON CAT_IDX

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

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

举例:

低效：

SELECT …

FROM DEPT

WHERE SAL * 12 >

25000;

高效:

SELECT …

FROM DEPT

WHERE SAL > 25000/12;

六.避免在索引列上使用

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

=

NOT

<= to >

七. 用>=替代>

如果 DEPTNO上有一个索引,

高效:

SELECT *

FROM EMP

WHERE DEPTNO

>=4

低效:

SELECT *

FROM EMP

WHERE DEPTNO

>3

两者的区别在于,

前者 DBMS

将直接跳到第一个 DEPT

等于 4 的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个

DEPT大于

3的记录.

八. 用 UNION替换

OR (适用于索引列)

通常情况下,

用 UNION替换

WHERE 子句中的 OR将会起到较好的效果. 对索引列使用 OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效.

如果有 column没有被索引, 查询效率可能会因为你没有选择 OR而降低. 在下面的例子中, LOC_ID

和 REGION上都建有索引.

高效:

SELECT LOC_ID ,

LOC_DESC , REGION

FROM

LOCATION

WHERE LOC_ID =

10

UNION

SELECT LOC_ID ,

LOC_DESC , REGION

FROM

LOCATION

WHERE REGION =

“MELBOURNE”

低效:

SELECT LOC_ID ,

LOC_DESC , REGION

FROM

LOCATION

WHERE LOC_ID = 10 OR

REGION = “MELBOURNE”

如果你坚持要用 OR,

那就需要返回记录最少的索引列写在最前面.

注意:

WHERE KEY1 =

10 (返回最少记录)

OR KEY2 =

20 (返回最多记录)

ORACLE 内部将以上转换为

WHERE KEY1 = 10

AND

((NOT KEY1 = 10) AND KEY2 =

20)

十. 用 IN来替换

OR

下面的查询可以被更有效率的语句替换:

低效:

SELECT….

FROM LOCATION

WHERE LOC_ID = 10

OR LOC_ID = 20

OR LOC_ID = 30

高效:

SELECT…

FROM LOCATION

WHERE LOC_IN IN

(10,20,30);

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

3.39 总是使用索引的第一个列

如果索引是建立在多个列上,

只有在它的第一个列(leading

column)被

where 子句引用时,优化器才会选择使用该索引.

十二. 用 UNION-ALL

替换 UNION (

如果有可能的话)

当 SQL

语句需要 UNION

两个查询结果集合时,这两个结果集合会以UNION-ALL 的方式被合并,

然后在输出最终结果前进行排序.

如果用 UNION

ALL替代

UNION, 这样排序就不是必要了.

效率就会因此得到提高.

举例:

低效：

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

UNION

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

高效:

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

UNION ALL

SELECT ACCT_NUM, BALANCE_AMT

FROM DEBIT_TRANSACTIONS

WHERE TRAN_DATE = ’31-DEC-95’

需要注意的是，UNION ALL

将重复输出两个结果集合中相同记录.

因此各位还是

要从业务需求分析使用 UNION

ALL的可行性. UNION 将对结果集合排序,这个操作会使用到

SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的.

十三. 用 WHERE替代

ORDER BY

ORDER BY 子句只在两种严格的条件下使用索引.

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

ORDER BY中所有的列必须定义为非空.

WHERE子句使用的索引和

ORDER BY子句中所使用的索引不能并列.

例如:

表 DEPT包含以下列:

DEPT_CODE PK NOT

NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

非唯一性的索引(DEPT_TYPE)

低效: (索引不被使用)

SELECT DEPT_CODE

FROM DEPT

ORDER BY DEPT_TYPE

EXPLAIN PLAN:

SORT ORDER BY

TABLE ACCESS FULL

高效: (使用索引)

SELECT DEPT_CODE

FROM DEPT

WHERE DEPT_TYPE >

0

EXPLAIN

PLAN:

TABLE ACCESS BY ROWID ON EMP

INDEX RANGE SCAN ON DEPT_IDX

十四. 避免改变索引列的类型.

当比较不同数据类型的数据时,

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

因为内部发生的类型转换,

这个索引将不会被用到!

注意:

为了避免 ORACLE对你的

SQL进行隐式的类型转换, 最好把类型转换用显式表现出来.

注意当字符和数值比较时,

ORACLE会优先转换数值类型到字符类型.

十五. CBO下使用更具选择性的索引

基于成本的优化器(CBO, Cost-Based

Optimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率. 如果索引有很高的选择性,

那就是说对于每个不重复的索引键值,只对应数量很少的记录. 比如, 表中共有 100条记录而其中有

80个不重复的索引键值. 这个索引的选择性就是 80/100 = 0.8

. 选择性越高,

通过索引键值检索出的记录就越少. 如果索引的选择性很低,

检索数据就需要大量的索引范围查询操作和 ROWID 访问表的 操作. 也许会比全表扫描的效率更低.

注意:

下列经验请参阅:

a.如果检索数据量超过

30%的表中记录数.使用索引将没有显著的效率提高.

b.在特定情况下, 使用索引也许会比全表扫描慢,

但这是同一个数量级上的区别.

而通常情况下,使用索引比全表扫描要块几倍乃至几千倍!

十六. 避免使用耗费资源的操作

带有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的

SQL语句会启动

SQL引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序.

例如,一个

UNION查询,其中每个查询都带有GROUP BY子句, GROUP BY会触发嵌入排序(NESTED SORT) ; 这样, 每个查询需要执行一次排序,

然后在执行 UNION

时, 又一个唯一排序(SORT

UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深度会大大影响查询的效率. 通常, 带有 UNION, MINUS ,

INTERSECT的

SQL语句都可以用其他方式重写.

注意:

如果你的数据库的SORT_AREA_SIZE调配得好, 使用 UNION , MINUS,

INTERSECT也是可以考虑的, 毕竟它们的可读性很强

十七. 使用显式的游标(CURSORs)

使用隐式的游标,将会执行两次操作. 第一次检索记录,

第二次检查 TOO MANY

ROWS 这个

exception . 而显式游标不执行第二次操作.

十八. 优化 EXPORT和

IMPORT

使用较大的 BUFFER(比如

10MB ,

10,240,000)可以提高

EXPORT和

IMPORT的速度. ORACLE 将尽可能地获取你所指定的内存大小,即使在内存不满足,也不会报错.这个值至少要和表中最大的列相当,否则列值会被截断.

可以肯定的是,

增加 BUFFER

会大大提高 EXPORT ,

IMPORT 的效率.

(曾经碰到过一个CASE, 增加 BUFFER后,IMPORT/EXPORT快了

10倍!)

作者可能犯了一个错误:

“这个值至少要和表中最大的列相当,否则列值会被截断. “ 其中最大的列也许是指最大的记录大小.

关于EXPORT/IMPORT

的优化,CSDN

论坛中有一些总结性的贴子,比如关于

BUFFER 参数,

COMMIT参数等等, 详情请查.

3.53 分离表和索引

总是将你的表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于 ORACLE

内

部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上.

注意:

“同时,确保数据表空间和索引表空间置与不同的硬盘上.”可能改为如下更为准确

“同时,确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上.”