Oracle中不使用索引的几种情况分析

Oracle不使用b*tree索引的情况大致如下1：where条件中和null比较可能导致不使用索引2：count，sum，ave，max，min等聚集操作时可能导致不使用索引3：显示或者隐式的函数转换导致不使用索引4：在cbo模式下，统计信息过于陈旧导致不使用索引5：组合索引中没有使用前导列导致没有使用索引6：访问的数据量超过一定的比例导致不使用索引下面就其中的几点做一些说明一：Null可以使用索引吗一般情况下，where条件中和null比较将会导致fulltablescan，实际上，如果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 3isnert四条记录，但索引只保存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 2SQL 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行。CommitSQL 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 scanSQL 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 88888Execution 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 88888Execution 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的成本更低，此事走索引扫描反而会使总成本变大，因此，索引用来快速访问表中的少量记录，对于访问表中的大量记录是不适合用索引的。

站在用户的角度思考问题，与客户深入沟通，找到锡林浩特网站设计与锡林浩特网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站建设、做网站、企业官网、英文网站、手机端网站、网站推广、域名申请、雅安服务器托管、企业邮箱。业务覆盖锡林浩特地区。

oracle 强制走索引

若查询数据量过大，需要走索引提升查询速度，但查询不走索引，可通过强制走索引方式让查询走索引查询

用法：/*+index(t idx_name)*/

比如：select /*+index(t idx_name)*/t.a from t; t是表别名，idx_name是索引名。若要走多个索引可在后面添加比如：

/*+index(t idx_name1)(t idx_name2)*/ 不过自己尝试似乎没有走多个有待验证。

oracle不使用索引的原因有哪些

索引失效

1) 没有查询条件，或者查询条件没有建立索引

2) 在查询条件上没有使用引导列

3) 查询的数量是大表的大部分，应该是30％以上。

4) 索引本身失效

5) 查询条件使用函数在索引列上（见12）

6) 对小表查询

7) 提示不使用索引

8) 统计数据不真实

9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。

10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),

但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

错误的例子：select * from test where tu_mdn=13333333333;

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

11)对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

错误的例子：select * from test where id-1=9;

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

12)使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

错误的例子：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; 这时函数索引起作用了

1,

2,单独的,,(有时会用到，有时不会)

3,like "%_" 百分号在前.

4,表没分析.

5,单独引用复合索引里非第一位置的索引列.

6,字符型字段为数字时在where条件里不添加引号.

7,对索引列进行运算.需要建立函数索引.

8,not in ,not exist.

9,当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

10, 索引失效。

11,基于cost成本分析(oracle因为走全表成本会更小)：查询小表,或者返回值大概在10%以上

12,有时都考虑到了 但就是不走索引,drop了从建试试在

13,B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走

14,联合索引 is not null 只要在建立的索引列（不分先后）都会走,

in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,

其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,

或者=一个值；当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,

以上两种情况索引都会走。其他情况不会走。

文章名称：oracle怎么不走索引,oracle不使用索引
URL链接：http://hbruida.cn/article/hegcdp.html