如果说性能优化是数据库技术中的明珠，那么索引无疑是其中最耀眼的一颗，特别是OLTP业务数据库。掌握了索引技术，基本上性能就不会有太大的问题。
有时我们创建了字段上的索引，但是通过执行计划却发现索引并没有被使用，还是会使用全表扫描。随着表上数据量的增长，性能会越来越差。如果不能查明原因，就只能盲目的靠不断扩容硬件来缓解（不是解决）这类问题，投资巨大，收益甚微。有时实在没办法，只能通过定期清空表来解决。
对于大表来说，能否使用到索引，效率上可能会相差上万倍（表越大，性能差距就越大）。下面我们就列举一些有索引，但是没有被使用的10几种情况，并给出对应的解决方法：
索引状态为UNUSABLE
--普通索引
select * from dba_indexes where status='UNUSABLE';
（分区表global index比较容易因为删除分区导致unusable）
--分区索引
select * from dba_ind_partitions where status='UNUSABLE';
--子分区索引
select * from dba_ind_subpartitions where status='UNUSABLE';
应对方法：
处于unusable状态的索引，需要rebuild ，才可能被使用。
alter index idx_name rebuild;
alter index idx_name rebuild partition part_name;
alter index idx_name rebuild subpartition subpart_name;
索引状态为INVISIBLE
select table_owner,table_name,index_name,
visibility from dba_indexes
where visibility='INVISIBLE';
应对方法：
alter index idx_name visible;
如果把参数optimizer_use_invisible_indexes 改为true（默认是false），那么invisible状态的索引也可以正常使用，不建议。
优化器估算使用索引COST高：
3.1 字段的NDV（唯一值个数）小，数据平均分布，不使用索引是正确的选择：
比如在正常的gender（性别）字段上创建索引，where gender='M' ，即使gender字段上有索引，也不会使用，但是可以使用hint强制使用该索引，只是效率不如不使用索引。
如果还有除了M、F外的其他性别，占的比例又比较少，比如：where gender='X' ，这种情况优化器会自动使用gender字段上的索引（字段上的直方图信息是选择使用索引的依据）。
应对方法：
正确选择，不需要干预。如果修改了optimizer_index_caching或optimizer_index_cost_adj参数，让优化器误用低效索引，性能反而会下降。
3.2 字段的NDV（唯一值个数）小，分布不均，但是没有直方图信息：
上面例子，如果gender字段上没有直方图信息，即便我们知道where gender='X' 使用索引效率高，优化器也不会使用索引。因为在没有直方图信息的情况下，优化器会按照数据平均分布来计算COST，唯一值越小，COST越高。
应对方法：
可以使用hint强制使用索引。
直方图信息默认会自动收集，不建议主动关闭直方图信息收集。有了直方图信息，索引会自动使用。
3.3 字段数据分布不均，有直方图信息。但是SQL使用了绑定变量，而且隐含参数_optim_peek_user_binds（绑定变量窥视）被关闭（默认是开启，有少量生产数据库关闭了这个参数）：
还是上面例子，where gender=:b1 ，在b1赋值 'X' 时，我们希望SQL执行计划能使用索引，但是在绑定变量窥视关闭的情况下，优化器不会选择使用索引，理由还是cost高。
应对方法：
如果绑定变量b1经常使用的变量值是'X'，这种情况可以使用hint强制使用索引。
如果绑定变量值仍有一些使用'F'、'M'的查询，这种情况不建议使用hint强制使用一种执行计划。建议开启绑定变量窥视（生产系统为了避免全局影响，可以在sql级别通过hint开启绑定变量窥视/*+ OPT_PARAM('_optim_peek_user_binds' 'true') */），并且保持ACS（自适应游标）开启，必要时还需要配合 bind_aware的hint。
3.4 like '%abcde%'，
select ... from t1 where object_name like '%abcde%'这种常规写法，优化器计算使用索引COST高，不会使用。只能通过改写，再配合相关hint，才能起到优化效果：
错误应对方法：
下面常规写法SQL，object_name字段上有索引，使用hint也可以强制使用该索引，但是，效率比不使用索引还要差（index full scan）：
Select /*+ index(t1 idx_t1_object_name) */ object_id,object_type,object_name
from t1 where object_name like '%ABCDE%';
正确应对方法：
如果返回的行数比较少，可以做如下改写（配合hint）：
Select object_id ,object_type,object_name from t1
Where object_name in
(select *+ cardinality(t1 5) */object_name from t1 where object_name like '%ABCDE%');
索引不保存全是NULL的记录
4.1 select * from t1 where object_id is null；
这种SQL的谓词条件写法，object_id字段上的索引无法被使用，因为索引不保存全是null的条目。这种情况即使使用hint也无法使用索引。
应对方法：
如果要使用索引，需要与一个非空内容做组合索引：create index idx_t1_object_id on t1(object_id,0); 原object_id字段上的索引可以删除掉。
为了避免这种情况，一般索引字段在设计表时，都设计为not null，同时就会杜绝col_name is null的写法。
4.2 select * from (select * from t1 order by object_id desc) where rownum<=10；
这种sql写法也与4.1一样，如果object_id 定义为not null，优化器会自动使用索引。如果定义为null，应对方法同4.1
字段上使用了函数或是做了运算
下面列举了一些sql写法，这样的写法，无法使用字段上的普通索引，需要创建对应的函数索引，才能使用索引：
5.1 to_char(cdate,'yyyymmdd')='20191027'
应对方法：
如果不能改sql，只能创建函数索引: create index idx_name on tname (to_char(cdate,'yyyymmdd'));
如果可以改sql，建议改成 cdate >=to_date('20191027') and cdate<to_date('20191027')+1
5.2 substr(name,1,10)=:b1
应对方法：
不能改sql：只能再创建函数索引；
可改写sql：name like :b1||'%' and length(:b1)=10;
5.3 cdate+1/24>sysdate
应对方法：
不能改sql：只能再创建cdate+1/24函数索引（如果还有cdate+1/48>sysdate，还需要再创建一个cdate+1/48的函数索引）；
可改写sql：需要改成 cdate>sysdate-1/24 （ cdate >sysdate - 1/48）
几种隐式类型转换
6.1 varchar2类型的字段，谓词条件变量类型是number类型：
phone_no=87654321 ; 这种情况，oracle会自动转换成to_number(phone_no)=87654321; 导致无法使用phone_no字段上的正常索引。
应对方法：
能改代码：
更改变量类型定义，将number类型改成varchar2类型；
如果是关联字段类型不匹配（nested loop，驱动表关联字段类型是number类型，被驱动表关联字段类型是varchar2类型），则需要在nested loop驱动表的关联字段上使用to_char函数。
如: select ... from t1,t2 where t1.num_col=t2.varchar2_col and t1.object_name='xxx';
改成：select ... from t1,t2 where to_char(t1.num_col)=t2.varchar2_col and t1.object_name='xxx';
不能改代码：
创建to_number(phone_no)函数索引。
6.2 date类型的字段，谓词条件变量类型是timestamp类型：
cdate>=:b1 (b1 类型是timestamp)；这种情况，oracle需要将cdate通过内部函数(internal_function）转换成timestamp类型，导致无法使用cdate字段上的正常索引。
应对方法：
只能通过修改代码，将变量类型从timestamp改成date，或者将字段类型从date改成timestamp，不能通过创建to_timestamp函数索引解决。
6.3 varchar2类型的字段，谓词条件变量类型是nvarchar2类型，这种情况orace会自动使用SYS_OP_C2C函数，将varchar2转换成nvarchar2。执行计划中会出现SYS_OP_C2C(col_name）类似的信息（plsql developer的F5看不到这些信息）。
应对方法：
这种情况一般发生在关联字段上，可以在驱动表的关联字段上使用to_char；或者在被驱动表的关联字段上创建to_nchar或SYS_OP_C2C函数索引。
绑定变量窥视与自适应游标（ACS）
与3.3类似，在分布不均字段上使用绑定变量，sql硬解析时，如果窥视到的绑定变量适合全表扫描，那么接下来使用的绑定变量如果适合使用索引，就会有多种不同选择：
7.1 10g及以下版本，会一直使用全表扫描的执行计划，直到下次硬解析，再次窥视绑定变量，制定新的执行计划（可能仍是全表扫描，也可能走索引）
7.2 11g及以上版本，如果关闭了自适应游标ACS，与7.1是一样的结果。
7.3 11g及以上版本，如果没有主动关闭ACS，在绑定变量适合走索引的时候，第二次执行的时候，会变成使用索引的执行计划。
7.4 11g及以上版本，如果想让绑定变量在适合走索引的时候，第一次执行时执行计划就能及时作出调整，需要使用bind_aware的hint。
应对方法：
10g版本没办法；11g版本不建议关闭ACS，即可自动完成执行计划转变，必要时需要配合bind_aware hint。
直方图的局限性
11g及以下版本， 直方图只计算字符串的前32位，如果是带长路径的文件名、URL地址、设备号等字符串标识，前面32位都相同，即便后面的值各不相同，也会被认为只有一个唯一值，导致优化器不会使用字段上的索引。（如果前31位相同，那么直方图信息只能靠最后一位的唯一值个数来计算选择性）
应对方法（2选1）：
1、使用hint强制使用字段上的索引。
2、清除该字段上的直方图信息：
--清除当前直方图信息：
exec DBMS_STATS.DELETE_COLUMN_STATS('&owner','&tab_name','&col_name',col_stat_type=>'HISTOGRAM');
--避免下次收集统计信息又恢复：
exec dbms_stats.set_table_prefs( '&owner', '&tab_name', 'method_opt', 'for all columns size auto for columns size 1 &col_name');
12c及以上版本：
varchar2字符串，直方图信息扩展到可以识别字符串的前64位，如果字段的前面64位都是相同的，一般不会自动收集该字段直方图信息，不影响索引的正常选择使用。如果强制收集了直方图信息，也会导致索引无法被使用。应对方法与11版本相同。
（未完待续）
欢迎批评指正，在下篇，我们还会列举10种左右有索引没有被使用的情况。欢迎转发，转发就是对本人最大的支持。
欢迎加入技术讨论分享群：
QQ群：16778072 （群文件有学习材料和工具）
微信群：先加微信ora_service，再入群
