发表时间：2012-12-10
最后修改：2012-12-10
充分利用索引
索引的限制
1. 索引对不等号和NOT的限制
如果WHERE条件中出现!=或者<>，即使该列建立了索引，则该索引也不会被使用；如果不恰当的使用了NOT，则索引也不会被使用。
Oracle 10g起，在基于CBO的优化器模式下Oralce会进行自动优化，但在基于RBO（基于规则）的优化器模式下，依然保持此规则。
1. 索引对不等号和NOT的限制
RBO模式下，执行计划如下：
1. 索引对不等号和NOT的限制
RBO模式下，执行情况如下：
此时使用变通写法的耗费为：0.407/2.187=1.60%!
1. 索引对不等号和NOT的限制
CBO模式下，执行情况如下：
此时使用变通写法的耗费节约不到0.03秒，但依然更优，故此推荐此种变通写法，再看此时使用NOT：
1. 索引对不等号和NOT的限制
CBO模式下，在JYJE列的索引上使用NOT：
为使用<>的：0.156/0.329=47.42%!为变通写法的使用0.156/0.297=52.53%！ 因 此这种写法最优！
1. 索引对不等号和NOT的限制
一般，WHERE条件中，如果索引列是字符列，使用NOT往往也不会使用索引：
结论：如果索引列是数字，则对于不等号的处理可以变更为NOT的方式或者(大于 OR 小于)的方式① ；对于确实无法不使用不等号的方式，可以使用默认值② ；如果可以建立位图索引则使用位图索引③ ；否则可以考虑使用分区等方法进行优化④ ，具体是情况而定。
2. 索引对IS NULL的限制
一般来说，如果WHERE子句基于的列是可空的列，且其建立了索引，如果使用了IS NULL，由于NULL的列本身不包含在索引中，因此无法利用索引。
所以一般对要建立索引的列不要设置为可空，如果确实含有空值，建议使用默认值代替空值，具体参见前面章节“SQL优化技巧”部分的“使用默认值”。
3. 索引对函数的限制
基于索引IDX_BIGTAB_OBJECTNAME，执行情况如下：
这是因为该索引是常规b-tree索引，对该列在WHERE子句中使用了函数，则不能使用索引。因此，对在WHERE子句中经常要使用函数时，应该建立基于函数的索引，且 只有当查询语句包含该函数或者表达式时，基于函数的索引才会被调 用。详情请参见索引部分的理论讲解！
创建并使用函数索引：
此时使用基于函数的索引效率是原来的2.782/0.188＝14.78！呗！
4. 索引对不匹配数据类型的限制
先看执行情况：
不匹配的类型执行的时间是匹配的类型的 2.187/0.266=8.2 倍！
原因分析：
因为ACCOUNT_TRADE表的字段YKKH是CHAR，因此在对其指定的值是数字时，Oracle虽然能隐式的执行数字和字符的转换，但不会调用其索引。而当对其指定是字符时，则不存在此问题，索引可以调用。
注意：因为数据类型的不匹配和Oracle对数据类型的隐式转换，此种类型的低效代码在任何项目中均可能因为大意而存在，因此建议开发人员和管理人员要定期抽查相应的代码，以杜绝此类低效代码！
索引类型总结
|
类型
|
，描述
|
|
b-tree索引
|
最常最多使用的索引，其树结构与二叉树比较类似，根据ROWID快速定位所访问的行
|
|
bitmap索引
|
使用位图来管理与数据行的对应关系，适用于基数比较少的列
|
|
降序索引
|
降序索引在叶子节点中的存储从左到右是按照从大到小排序的；一般是针对逆向排序较多的查询时才使用该类型索引
|
|
函数索引
|
针对要频繁对列使用函数的索引，只有当查询语句包含该函数或者表达式时，基于函数的索引才会被调用
|
|
反转索引
|
反转了b*tree索引码中的字节，使索引条目分配更均匀，多用于并行服务器环境下，用于减少索引的竞争
|
|
分区索引
|
分区表的索引，又包括本地分区索引(本地前缀分区索引和本地非前缀分区索引)和全局索引，一般建议使用本地分区索引，因其与基表具有良好的数据均衡性和可维护性
|
访问路径
1. 全表扫描
全表扫描（FULL TABLE SCANS）时所有行、所有数据块均会被读到，是 效率最 低的一种，一般会在表 缺少索引、 读取大量数据、 访问小表或 高并发时发生。
2. ROWID扫描
ROWID扫描（ROWID SCANS）是通过ROWID中数据文件和块位置访问数据行。一般作为访问索引后的第二步，如果访问的列全部包含在索引中，则不会执行ROWID扫描。
作为索引访问后的第二步：
访问的列全部在索引中不再执行ROWID扫描
3. 索引扫描
索
引扫描(INDEX SCANS)包含全索引扫描（full index scan、FIS）、快速全索引扫描(fast full index
scan、FFIS)、索引范围扫描(index range scan)、索引唯一扫描 (index unique scan)、索引跳跃式扫描
(index skip scan)、位图索引扫描(bitmap index scan)， 其中前5种在本系列课程的索引章 节部分已经讲解了其理论和示例。位图索引示例如下：
3. 索引扫描
|
类型
|
方式
|
发生条件
|
|
1.FULL INDEX SCANS
|
逐一读取索引中的所有块，由于索引中数据已按索引键排序，因此会忽略掉排序
|
1.ORDER BY中的列全部在该索引中时
2.ORDER BY中列的顺序满足索引中前导列的顺序时
3.使用GROUP BY且该子句中的列在索引中时
|
|
2.FAST FULL INDEX SCANS
|
只扫描索引中的数据，不会扫描表中的数据；由于索引中数据未按索引键排序，因此不能忽略掉排序
|
当同时满足下列条件是，Oracle用FFIS替代FIS:
1.查询的所有列均包含在索引中
2.索引中的列至少一个具有not null约束
|
|
3.INDEX RANGE SCANS
|
访问选择性数据最常用的扫描方式；按顺序的对某个索引进行扫描，返回数据是升序排列的，可以使用唯一索引和非唯一索引;如果对索引列使用ORDER BY/GROUP BY则可省略排序
|
1.在唯一索引上使用范围操作符(>、<、>=、<=、<>、BETWEEN)
2.在组合索引上使用部分列进行查询，导致查出多行
|
|
4.INDEX UNIQUE SCANS
|
扫描唯一索引或主键，要么返回一行数据要么返回0行数据
|
1.当使用唯一索引时
2.当使用主键时
|
|
5.INDEX SKIP SCANS
|
其实质是将索引分解成多个小的子索引来提高效率，系从9i开始引入
|
复合索引中前导列的取值是枚举的从而可以分拆为多个子索引,并且查询条件中不含前导列时
|
为了 加深巩固前面的知识，本处对前五种索引扫描复习总结如下：
(1).全索引扫描
逐一读取索引中的所有块，由于索引中数据已按索引键排序，因此会忽略掉排序，可能发生的情况如下：
A. ORDER BY中的列全部在某个索引中
全部在某个索引中：
(1).全索引扫描
B. ORDER BY中列的顺序满足索引中前导列的顺序时
下面分别是满足和不满足前导列顺序时：
C. 使用GROUP BY且该子句中的列在索引中时
(2).快速全索引扫描
只扫描索引中的数据，不会扫描表中的数据；由于索引中数据未按索引键排序，因此不能忽略掉排序。当同时满足下列条件时，Oracle用FFIS替代FIS或FTS:
1.查询的所有列均包含在索引中
2.索引中的列至少一个具有not null约束（10g开始的，原低版本的系统中为查询的列中不包含任何null值）
全部列均在索引中：
有列不在索引中：
删除该索引，创建新索引，两个列均为可空：
此时即使全部列在该索引中， 也不会发生FFIS
索引范围扫描是访问选择性数据最常用的扫描方式；按顺序的对某个索引进行扫描，返回数据是升序排列的，可以使用唯一索引和非唯一索引;如果对索引列使用ORDER BY/GROUP BY则可省略排序。
下列情形中会发生索引范围扫描：
A.在唯一索引上使用范围操作符(>、<、>=、<=、<>、BETWEEN)
B.在组合索引上使用部分列进行查询，导致查出多行
示例请参考本系列课程的索引章节部分
(4).索引唯一扫描
当使用主键或唯一索引时发生。 示例请参考本系列课程的索引章节部分。
(5).索引跳跃扫描
复合索引中前导列的取值是枚举的从而可以分拆为多个子索引,并且查询条件中不含前导列时。示例如下：
create table customers as select * from sh.customers;
CREATE INDEX customers_gender_email
ON customers (cust_gender, cust_email);
(5).索引跳跃扫描
没进行表分析前：
进行表分后：
analyze table customers compute statistics;
何时需要索引
一般地，对于从表的总行中的大部分查询只查询不到10%数据（有的称为2%-4%）的表，可以考虑创建索引。一般考虑的索引的原则包括：
l对于经常以查询关键字为基础的表，并且该表中的数据行是均匀分布的
l以查询关键字为基础，表中的数据行随机排序
l表中包含的列数相对比较少（仅仅是相对，需要根据实际情况确定)
l表中的大多数查询都包含相对简单的WHERE子句
l表的记录数比较少的，不建议使用索引，如数据不超过1万行的表不要建立索
为索引选择列和表达式
一般遵循的原则包括：
l经常在WHERE子句中使用的列
lSQL语句中经常用于表之间连接的列
l重复性少（可选择性高）的关键字，如主键
l不宜将经常UPDATE的列作为索引列
l不宜将经常在WHERE子句中使用，但与函数或操作符相结合的列作为索引列
l对于取值较少的列，应考虑建立位图索引，而不应该采用B树索引
l如果经常访问的列上要使用函数，应使用基于函数的索引
本处举例说明取值较少的列使用bitmap索引和b-tree的对比分析，B-tree时：
使用复合索引
多个列联合起来组成的索引称为复合索引、或联合索引或者组合索引，往往联合索引比单个索引具有更好的性能。创建联合索引一般遵循的原则包括：
l经常在WHERE子句中使用的列且这些列之间使用AND连接
l查询条件可能包括n个列的AND关系，而大多数情况下使用m个列是(n>m)，应该考虑复合索引，且n个列为前导列
l某几个列联合起来能够组成唯一索引，应坚决建立联合唯一索引
l复合索引中，建议至少一个不能为null，且如果可能尽量将只是存在null的列对其null值采用其它默认值代替
本处举例说明Where中包含AND时使用多个索引性能低于联合索引的示例，使用多个索引时：
本处举例说明Where中包含AND是使用多个索引性能低于联合索引的示例，使用复合索引时：
结论：
