Oracle中不使用索引的几种情况分析 ---转
Oracle不使用b*tree索引的情况大致如下
1：where条件中和null比较可能导致不使用索引
2：count，sum，ave，max，min等聚集操作时可能导致不使用索引
3：显示或者隐式的函数转换导致不使用索引
4：在cbo模式下，统计信息过于陈旧导致不使用索引
5：组合索引中没有使用前导列导致没有使用索引
6：访问的数据量超过一定的比例导致不使用索引
下面就其中的几点做一些说明
一：Null可以使用索引吗
一般情况下，where条件中和null比较将会导致full table scan，实际上，如果table中索引建列的值都为null，那么该行在索引（此处指b*tree，位图索引和聚簇索引可以有空值）中就不会存在，因此oracle为了保证查询结构的准确性，就会用full table scan代替index scan，这样理解，不走索引也就在情理之中。
当然，如果某个索引列上有定义为not null，在这种情况下，不存在所有索引列都为空的情况，所以此种情况下，是可以走index scan的，因此，对于where条件中含有类似is null，=null的情况，是否走索引，还是要看索引建中是否有某个列定义为not null。
具体实验如下：
SQL> create table t(x char(3),y char(5));
SQL> insert into t(x,y) values ('001','xxxxx');
SQL> insert into t(x,y) values ('002',null);
SQL> insert into t(x,y) values (null,'yyyyy');
SQL> insert into t(x,y) values (null,null);
SQL> commit;
SQL> create unique index t_idx on t(x,y);
SQL> analyze table t compute statistics for table for all indexes;
SQL> select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t_idx');
BLEVEL LEAF_BLOCKS NUM_ROWS
---------- ----------- ----------
0 1 3
isnert四条记录，但索引只保存3条，最后一条没有保存在索引中
SQL> set autotrace traceonly explain;
SQL> select * from t where x is null;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=8)
1 0 TABLE ACCESS (FULL)OF 'T' (Cost=2 Card=1 Bytes=8)
SQL> create table t1(x char(3),y char(5) not null);
SQL> insert into t1(x,y) values ('001','xxxxx');
SQL> insert into t1(x,y) values (null,'xxxxx');
SQL> commit;
SQL> create unique index t1_idx on t1(x,y);
SQL> analyze table t1 compute statistics for table for all indexes;
SQL> select blevel,leaf_blocks,num_rows from user_indexes where index_name=upper('t1_idx');
BLEVEL LEAF_BLOCKS NUM_ROWS
---------- ----------- ----------
0 1 2
SQL> select * from t1 where x is null;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=11)
1 0 INDEX (RANGE SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=1 Byt
二：COUNT(*)等聚集函数可能导致不使用索引
在做count，sum，ave，max，min等聚集操作时，有的时候也会不用索引，因为如果优化器发现索引列没有任何一个列定义为not null，而且where条件中也没有索引键列，如x=x,在此情况下，索引扫描结果会不准确，此时oracle就会用全表full table scan。沿用上面的二个表来说明
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=4)
SQL> select sum(x) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=4 Bytes=20)
因为该表的索引列（x，y）没有定义为not null，所以都走了全表扫描，即使把x=null，y=null的行删除，同样还是走全表扫描。
SQL> delete t where x is null and y is null;
已删除1行。
Commit
SQL> analyze table t compute statistics for table for all indexes;
SQL> select count(*) from t;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T'(Cost=2 Card=3)
同样的sql语句，对于t1表，因为索引列y定义为not null，所以oracle会选择index scan
SQL> select count(*) from t1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=2)
SQL> select sum(x) from t1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=5)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'T1_IDX' (UNIQUE)(Cost=1 Card=2 By
tes=10)
三：隐式或者显示的函数转换降导致全表扫描
SQL> Select * from t1 where x=001;
X Y COMM
------ ---------- ----------------------------
001 xxxxx 88888
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=26)
1 0 TABLE ACCESS (FULL) OF 'T1'(Cost=2 Card=1 Bytes=26)
因为x为char类似，在这里oracle把x=001做了隐式转换to_number(x)=001,建在该字段的索引将不起作用，基于函数的索引（function based index）可以在此派上用场，相对于普通索引，fbi是把经过函数转换后的值存放到索引中
SQL> create index t1_fbi on t1(to_number(x));
SQL> analyze table t1 compute statistics for table for all indexes;
SQL> Select * from t1 where x=001;
X Y COMM
------ ---------- ----------------------------
001 xxxxx 88888
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes
=26)
2 1 INDEX (RANGE SCAN) OF 'T1_FBI'(NON-UNIQUE) (Cost=1 Card
=1)
四：统计信息不是最新的，导致无法使用
五：组合索引中没有用到前导列导致没有用索引,如组合索引（x，y）,where条件类似where y=….,此时不走索引（如果x的不同值很少，那么oracle9i以后就有可能走index skip scan，其原理类似于select * from t where y=…and x=(某个确定的值) union all select * from t where y=…and x=(某个确定的值)……..
六：访问的数据比例超过一定范围，优化器会认为full table scan的成本更低，此事走索引扫描反而会使总成本变大，因此，索引用来快速访问表中的少量记录，对于访问表中的大量记录是不适合用索引的。
