索引失效的一些原因【整理】

1. Where子句中使用Oracle内部函数

在销售订单表中，有一个订单日期字段，其存储的数据为年月日。假设现在用户需要统计数据，需要统计2009年第一季度每个月的各个业务员的接单情况。由于在销售订单中没有存储年与月份的数据，而只有订单日期数据，那么就需要利用extract函数从订单日期字段中获取年份与月份字段，然后再查询各个业务员在2009年第一季度每个月的销售订单明细。下面的Select语句就是查询2009年1月份各个业务员的接单情况。

select 业务员，订单日期，销售订单号码，客户名称，订单金额 where extract(yyyy,订单日期)=2009 and Extract(mouth,订单日期)=1

原先在这个日期字段上建立了索引(不是函数索引)，那么此时会对数据库的查询产生什么影响呢？通常情况下，如果不使用基于函数的索引，那么当SQL语句在的Where子句中对存在索引的列使用函数时，这会让数据库的优化器忽略掉这些索引。也就是说，这种情况下即使只存在着少量的复合条件的信息，数据库仍然会对这张表进行全表扫描，以获取相关的数据。这主要是因为这些函数实际上已经改变了被索引列的值。一些常见的函数，如substr、extract、round等函数，都会改变索引列的值。此时数据库系统也就无法使用已被函数引用(此时列的值已经发生改变)的索引和列。也即是说，如果在Where子句的条件语句中，采用了函数的话，则即使列采用了索引(不是函数索引)，就会让设置在这个列上的索引失效，此时数据库就会对这个表进行全表扫描。

那么该如何避免这种情况呢？要在Where子句中要使用函数，此时就可以把这个列上的索引设置为函数索引。通常情况下，只要建立了函数索引，则即使在Where语句中采用了函数，这个列上的索引仍然有效。在查询中就可以避免全表扫描。因为函数索引实际上存储了预先计算过的值。也就是说，在索引表中，其实已经存储了年度与月份的值。而不是存储具体的订单日期。那么此时在查询时，数据库就会直接对应索引表中的年度与月份的值。为此索引就不会因为采用了函数而失效。

错误的例子：select * from test where round(id)=10; //此时id列上的索引已经失效

正确的例子：首先建立函数索引create index test_id_fbi_idx on test(round(id))，然后 select * from test where  round(id)=10; 这时函数索引起作用了

2.  隐式转换（不匹配的数据类型）

在数据库中，有些数据类型虽然不同，但是数据库会自动进行转换。如现在在一张用户信息表中，可能有公民的身份证号码字段，这个字段的类型为字符型。通常情况下，为这个字符类型的字段赋值时需要加入单引号。但是如果把一个纯数字的字符串赋值给一个字符型的字段时，可以不用加单引号。因为此时数据库系统会自动把这串数字转换为字符型数据。现在数据库在这表中已经给这个身份证号码字段设置了索引。如果现在用户在对这个表进行查询时，所采用的Where条件语句为 Where 身份证号码=123456789900。此时数据库会如何查询呢?

数据库会忽略掉设置在身份证号码字段上的索引，而采用全表扫描。Oracle数据库系统在数据类型字段上的兼容性，虽然提高了用户操作数据的便利性，但是毋庸置疑的也给用户留下不少的麻烦。就拿上面这个例子来说，数据库优化器会对以上这个条件语句进行一些转换，如可能会换成：to_number(身份证号码)= 123456789900，也就是说，会在身份证号码字段前面隐性的加入一个函数，把身份证号码转换为数字型。然后再与后面提供的身份证号码进行比对。此时就相当于对索引列采用了函数，跟上面提到的第一个错误类似。当Where条件语句中采用了函数，则即使这个列中设置了索引(不是函数索引)，则数据库优化器也会忽略掉这个索引。此时即使一个身份证号码在数据库中只有一条记录，数据库仍然需要进行全表扫描。

--避免使用字符型字段为数字时在where条件里不添加引号

错误的例子：select * from test where tu_mdn=13333333333;//tu_mdn定义为varchar2(20), 查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效。

正确的例子：select * from test where tu_mdn='13333333333';

3. 对索引列进行运算 (+，-，*，/，! 等)

错误的例子：select * from test where id-1=9; //类似隐式转换

正确的例子：select * from test where id=10;

4. Where子句中使用is null或is not null

在数据库设计的时候，允许某些字段为非空。而即使某个字段允许为非空，数据库仍然允许在这个字段上建立索引。但是使用索引就是一个很危险的事情。因为一不小心，就可能使得这个索引失效，在查询时需要用到全表扫描。如在以上这个表中，用户需要查询身份证号码为空的纪录，以方便用户补全身份证号码。此时用户就需要用到以下这个条件语句：where ‘身份证号码’is null，可以查询出所有身份证号码为空的纪录。但是，在Where子句中如果使用IS NULL或者IS NOT NULL等条件语句的话，会让在这个列上的索引失效。为此如果在几百万的信息中，如果只有两条记录没有身份证号码，则此事数据库仍然需要进行全表扫描，以查找相关的信息。这主要是因为普通情况下，如果一个字段为空，而且又在这个字段上设置了索引的话，则这个索引的值不会保存在索引表中。因为根本无法保存，因为空值(NULL)在数据库中是一个很特殊的值，不等于‘’，甚至不等于NULL。

为了避免这种情况笔者有几个建议。如允许身份证这个字段为NULL（1）在这个字段上建立位图索引，因为创建位图索引时，数据库系统会对整个表进行索引，并为索引列的每个取值建立一个位图，包括NULL字段。所以说位图索引通常对于NULL字段的搜索有独到之处。但是位图索引通常情况下是用在基数比较小的情况，即重复数值比较多时。而对于身份证号码的话，基本上都是唯一的，也就是说基数很大，此时并不适合采用位图索引。既然不能够采用位图索引，那么就（2）最好能够给这个字段设置默认值。如可以把这个字段默认设置为0。当没有输入身份证号而保存这个资料的时候，则数据库中以字符0表示。如此在以后想查询身份证号码为空的纪录时，只需要输入0，而不需要用IS NULL，这就可以避免全表扫描了。（3）也可对身份证字段能够实现非空限制最好。

5. 其他索引列使用注意事项：

a) 使用 <> 、not in 、not exist、!=

b) like '..%..' (不以 % 开头)，可以使用列上的索引

like '%...' 的 (不以 % 结尾)，可以利用reverse + function index 的形式，变化成 like '..%'

例如：select * from test_like where reverse(colName) like reverse('%AS');

尽量不要使用 like '%..%'，很难优化。

c) 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个 列被where子句引用时，优化器才会选择使用该索引。

d) 当变量采用的是timestamp变量，而表的字段（索引列）采用的是date变量时。或相反情况。

6. Oracle在进行一次查询时，一般对一个表只会使用一个索引.

有时候过多的索引可能导致Oracle使用错误的索引，降低查询效率。例如某表有索引1（Policyno） 和索引 2 （ classcode ），如果查询条件为  policyno =  ‘ xx ’  and classcode =  ‘ xx ’ ，则系统有可能会使用索引 2，相较于使用索引1，查询效率明显降低。

7. Oracle默认使用的基于代价的SQL优化器（CBO）非常依赖于统计信息，一旦统计信息不正常，会导致数据库查询时不使用索引或使用错误的索引。

一般来说，Oracle的自动任务里面会包含更新统计信息的语句，但如果表数据发生了比较大的变化（超过 20%）,可以考虑立即手动更新统计信息，例如：analyze table abc compute statistics，但注意，更新统计信息比较耗费系统资源，建议在系统空闲时执行。

8. 优先且尽可能使用分区索引。