今天在做程序的时候发现原先写的索引竟然莫名奇妙地失效了。去找了一下大概原因如下,做了分析心得写上,希望能帮助大家
首先了解失效索引的原因
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 =一个值),以上两种情况索引都会走。其他情况不会走。
解决思路
1,4,5,6,7,8不讲 应该都知道
第四个:表没分析
为什么要分析表?
对于使用CBO很有好处,可以使用更可靠的table信息,从而执行计划也可以更准确一些,在10g会自动analyze,之前的版本需要手动定期
生成统计信息,,选择合理的执行计划..
Oracle的online document这样描述analyze的作用:
Use the ANALYZE statement to collect non-optimizer statistics, for example, to:
1、Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
2、Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
3、Identify migrated and chained rows of a table or cluster.
对于收集统计信息这块,我一般使用dbms_stats这个package,收集统计信息的做用使CBO方式的优化选择执行计划更准确
统计信息最好定期收集,以业务的不同确定不同的收集周期
生成统计信息,,选择合理的执行计划..
Oracle的online document这样描述analyze的作用:
Use the ANALYZE statement to collect non-optimizer statistics, for example, to:
1、Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
2、Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).
3、Identify migrated and chained rows of a table or cluster.
对于收集统计信息这块,我一般使用dbms_stats这个package,收集统计信息的做用使CBO方式的优化选择执行计划更准确
统计信息最好定期收集,以业务的不同确定不同的收集周期
在新增的索引有可能没有收集 ,那么就会导致索引失效
解决方法:
分析方法有一下几种:
analyze table t1 compute statistics fortable;
analyze table t2 compute statistics for allcolumns;
analyze table t3 compute statistics for all indexescolumns;
analyze table t4 computestatistics;
analyze table t5 compute statistics for allindexes;
analyze table t6 compute statistics for table for all indexesfor allcolumns;
--收集表,索引,表字段信息
11.基于COST成本分析
在这种条件下 oracle会认为索引更占资源,就默认不走索引了。这种情况如果觉得索引快的 不妨试试强制索引
select /*+INDEX(t IDEX_HZYB_JSJL_BDJSRQ)*/
from hzyb_jsjl t
where BDJSRQ >TO_DATE('','');
其中T是 表名 IDEX_HZYB_JSJL_BDJSRQ 是表的索引