索引使用误区

一、在oracle中，我们经常以为建立了索引，sql查询的时候就会如我们所希望的那样使用索引，事实上，oracle只会在一定条件下使用索引，这里我们总结数第一点：oracle会在条件中包含了前导列时使用索引，即查询条件中必须使用索引中的第一个列

二、使用了<> 和!=后，就不会使用索引

三、使用基于函数的索引（BFI, Based Function Index）：

从Oracle 8i开始，可以使用基于函数的索引来提高查询性能，

使用基于函数的索引，需要几个条件：

1，  用户需要有create index或者create any index权限

2，  用户需要有query rewrite或者global query rewirte权限

3，  设置系统参数 query_rewrite_enabled=TRUE 和 query_rewrite_integrity=enforced

4，  设置系统参数 ：COMPATIBLE=8.1.0.0.0 或者更高

5，创建了BFI后，需要对表进行分析

6，建立了索引后，要想在CBO下合理的使用索引，一定要定期的更新统计信息

四、空值对索引的影响

1、Oracle的索引不保存全部为空的行。

2、Oracle认为 NULL<>NULL ，进而 (NULL,NULL)<>(NULL,NULL)

换句话说，Oracle认为空值（NULL）不等于任何值，包括空值也不等于空值。

3、oracle在group by子句中认为完全为空的行是相同的行

换句话说，在group by子句中，oracle认为(NULL,NULL)=(NULL,NULL)

4、在使用IS NULL 和 IS NOT NULL条件的时候，Oracle不使用索引（因为Oracle的索引不存储空值，详细请参见前面的相关内容）

首先，尽量不在前导列上使用空值，请看下面的例子：

还有一个可以变通的方法，即我们在创建表的时候，为每个列都指定为非空约束（NOT NULL），并且在必要的列上使用default值

五、空值的妙用

并不是因为完全为空的条目不被记录到索引中，就坚决不能使用空值，相反，有时候合理使用oracle的空值会为我们的查询带来几倍甚至几十倍的效率提高。

举个例子，加入有一个表，里面有个字段是“处理时间”，如果没有处理的事务，该列就为空，并且在大部分情况下，处理的事务总是在总记录数的10％或者更少，而等待处理的记录（“处理时间”这列为空）总是绝大多数的记录，那么在“等待时间”这列上建立索引，索引中就总是会保存很少的记录，我们希望的访问方式是，当访问表中所有代处理的记录（即10％或者更多的记录数目）时，我们希望通过全表扫描的方式来检索；然而，当我们希望访问已经处理的事务（即5％或者更少的记录数目）时，我们希望通过索引来访问，因为索引中的记录数目很少

六、为所有列都建立索引

我们知道，建立索引是为了提高查询的效率，但是同时也应该注意到，索引增加了对DML操作（insert, update, delete）的代价，而且，一给中的索引如果太多，那么多数的索引是根本不会被使用到的，而另一方面我们维护这些不被使用的所以还要大幅度降低系统的性能。所以，索引不是越多越好，而是要恰到好处的使用。

比如说，有些列由于使用了函数，我们要使用已有的索引（如一些复合索引）是不可能的（详细请参见前面“函数索引”），那么就必须建立单独的函数索引，如果说这个函数索引很少会被应用（仅仅在几个特别的sql中会用到），我们就可以尝试改写查询，而不去建立和维护那个函数索引，例如：

1，trunc函数

2，to_char函数

3，to_date函数

4，substr函数

通常，为了均衡查询的效率和DML的效率，我们要仔细的分析应用，找出来出现频率相对较多、字段内容较少（比如varchar2(1000)就不适合建立索引，而varchar2(10)相对来说就适合建立索引）的列，合理的建立索引，比如有时候我们希望建立复合索引，有时候我们更希望建立单键索引。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/59630/viewspace-789193/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/59630/viewspace-789193/