有时我们创建了字段上的索引，但是通过执行计划却发现索引并没有被使用，还是会使用全表扫描。随着表上数据量的增长，性能会越来越差。如果不能查明原因，就只能盲目的靠不断扩容硬件来缓解（不是解决）这类问题，投资巨大，收益甚微。有时实在没办法，只能通过定期清空表来解决。
对于大表来说，能否使用到索引，效率上可能会相差上万倍（表越大，性能差距就越大）。下面我们就列举有索引，但是没有被使用的10几种情况，并给出对应的解决方法：
1. 索引状态为UNUSABLE
--普通索引，分区表global index比较容易因为删除分区导致unusable
select * from dba_indexes where status='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;
2. 索引状态为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状态的索引也可以正常使用，不建议。
3. 优化器估算使用索引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也可以强制使用该索引，但是，效率比不使用索引还要差
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%');
4. 索引不保存全是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的写法。
一个例子：
CDMBAS.BAS_PAPPVACTINFO表的PA_REQUNO ， PA_ORDER 两个字段上存在复合索引，但是下面SQL却使用了全表扫描，即使使用了index的hint也不行。想想可能是什么原因？（3300万记录的表，全表扫描执行一次19秒；问题解决后使用索引只需1.7毫秒）：
SELECT B2.* FROM
( SELECT B1.*, ROWNUM rnum
FROM ( SELECT
/*+ index(t1 IDX_BAS_PAPPVACTINFO_0001) */
T1.PA_CLINAME AS T1__PA_CLINAME ,
......
T1.PA_OPTIME AS T1__PA_OPTIME
FROM CDMBAS.BAS_PAPPVACTINFO T1
WHERE 1=1
ORDER BY PA_REQUNO DESC , PA_ORDER DESC -- 复合索引
) B1
WHERE ROWNUM <= 21
) B2
WHERE rnum > 0 ;有索引而不能用，抛开insivisible和unusable 等情况，基本上可以确定是因为复合索引的两个字段定义都是NULL，因为索引不保存全是NULL的条目，为了保证结果的正确性，优化器不选择使用索引是正确的。
如果两个字段确实是可以为NULL，为了优化这个SQL，我们可以这样创建索引，两种方法：
- 在联合索引的两个字段后，再增加一个定义为NOT NULL的字段，形成3个字段的联合索引
- 直接增加一个常量作为字段，形成3个字段的联合索引
create index idx_name on CDMBAS.BAS_PAPPVACTINFO(PA_REQUNO,PA_ORDER,0);这个案例是一个真实的生产系统，实际的业务数据中，这两个字段都是NOT NULL的，这种情况我们还有两种做法来进行优化：
- 改写SQL，增加一个PA_REQUNO is not null的谓词条件（用另一个字段也可），即where 1=1 and PA_REQUNO is not null order by .....
- alter table CDMBAS.BAS_PAPPVACTINFO modify (PA_REQUNO not null); 或改另一字段为not null，或者两个都改写not 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
5. 字段上使用了函数或是做了运算
下面列举了一些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
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. 几种隐式类型转换
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
改成：select ... from t1,t2 where to_char(t1.num_col)=t2.varchar2_col
不能改代码：
创建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函数索引。
7. 绑定变量窥视与自适应游标（ACS）
与3.3类似，在分布不均字段上使用绑定变量，sql硬解析时，如果窥视到的绑定变量适合全表扫描，那么接下来使用的绑定变量如果适合使用索引，就会有多种不同选择：
- 10g及以下版本，会一直使用全表扫描的执行计划，直到下次硬解析，再次窥视绑定变量，制定新的执行计划（可能仍是全表扫描，也可能走索引）
- 11g及以上版本，如果关闭了自适应游标ACS，与7.1是一样的结果。
- 11g及以上版本，如果没有关闭ACS，在绑定变量适合走索引的时候，第二次执行的时候，会变成使用索引的执行计划。
- 11g及以上版本，如果想让绑定变量在适合走索引的时候，第一次执行时执行计划就能及时作出调整，需要使用bind_aware的hint。
应对方法：
10g版本没办法；11g版本不建议关闭ACS，即可自动完成执行计划转变，必要时需要配合bind_aware hint。
8. 直方图的局限性
11g及以下版本，
应对方法（2选1）
- 使用hint强制使用字段上的索引。
- 清除该字段上的直方图信息：
--清除当前直方图信息：
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版本相同。
9. SQL写法导致无法使用索引
下面两个sql，走object_id字段上的索引，效率都非常高：
select min(object_id) from t1;
select max(object_id) from t1;
但是，如果同时查询，只能是全表扫描（执行计划按规则办事，没办法）：
select min(object_id),max(object_id) from t1;
如果字段上所有值都是NULL，则min/max函数都返回null，否则忽略null，返回非空值。在object_id定义为null时，只能是全表扫描；如果object_id 字段定义为NOT NULL，上面同时查min、max的sql，也可以使用index fast full scan的执行计划。
应对方法：
改变sql写法，让写法适应规则。写法虽然繁琐了一点，却是会带来非常大的性能提升：
select (select min(object_id) from t1) as min_object_id，
select max(object_id) from t1) as max_object_id
from dual；
思考一下，下面SQL的优化，该如果创建索引，写法又该是怎样？已知owner字段选择性差。
select max(created),min(object_id) from t1 where owner='SYS'；
应对方法：
创建owner+created和owner+object_id两个索引，然后按上面方法改写（如果执行频率很低，大可不必）。
select (select min(object_id) from t1 where owner='SYS') as min_object_id，
select max(created) from t1 where owner='SYS') as max_created
from dual；
10. 谓词条件使用了or，只有部分谓词条件上有索引
select * from t1 where object_id=:b1 or object_name=:b2；
object_id和object_name两个字段选择性都不错，但是只有object_id字段上有索引，object_name字段上没有索引，这个时候是无法使用object_id字段上索引的。
应对方法：
再创建object_name字段上的索引。如果把or换成and，则两个字段任何一个字段上有索引，都会使用。
11. 函数索引中使用了常量，SQL中使用绑定变量
表上创建了如下类似函数索引：
create index idx1 on t1(substr(object_name,1,9));
create index idx2 on t1(substr(object_name,1,10));
SQL中对函数索引的常量也使用了绑定变量，这种写法是无法使用索引的
select * from t1 where substr(object_name,1,:b1)=:b2;
应对方法：
必须把函数索引中的b1绑定变量换成常量9或10。还有其他诸如 mod(xxid,10)的这种函数，如果创建了函数索引，也是不能使用绑定变量的，放在组合索引里面也只能起到过滤作用，起不到索引作用。
12. 复合索引，前导字段没有用到，且NDV（唯一值个数）较高
t1表存在两字段复合索引：object_id , object_type （object_id 唯一值个数多，选择性好）
select * from t1 where object_type='RULE';
复合索引前导字段object_id，在sql的谓词条件没有用到，这种情况不会使用索引，如果用hint强制使用索引，效率反而会更差。如果前导字段唯一值个数少，优化器会选择使用index skip scan的执行计划，效率也还可以接受，会比全表扫描好很多（mysql不支持index skip scan）。
应对方法：
创建object_type单字段索引。
说到这个情况，我再点评一个案例：
2015年oracle技术嘉年华，有个这样的案例分享（数据库是11204版本）：
SQL：select * from t1 where object_type=:x and object_id=:k;
t1表130万记录，object_type和object_id两个字段上都存在单字段索引，object_id唯一值个数69万；object_type唯一值个数18，分布不均，有直方图。某天突然出现严重的性能问题，分析发现是使用了object_type字段上的索引。
原文给出的建议：
保持ACS（自适应游标）关闭，删除object_type字段上的直方图或删除object_type字段上的索引。
我对原文给出的建议都是持否定态度的：
1、正是因为关闭了ACS，才导致执行计划不能在绑定变量发生变化时不能及时调整。如果开启ACS，就不会有类似性能问题的发生。虽然acs有部分bug，但是带来的好处远大于关闭导致的最大弊端。
2、删除直方图或删除索引更是不可取，原因如下：
对于单表查询，直方图信息是优化器判断能否使用索引的重要依据，如果有sql是select * from t1 where object_type=:x ，查询的是对应记录数少的绑定变量值，这种情况是需要使用索引的，没有索引或直方图，都只能走全表扫描。此类SQL在OLTP业务也是比较常见的。
对于多表关联，直方图信息是用来准确估算Cardinality的重要依据，会影响两表关联时驱动表的选择；如果两个表关联后还要再跟其他表做关联，Cardinality估值偏差会比较大，将会导致优化器选择的执行计划可能不是最优。
13. 谓词条件是 not in(1，2) 、<> 、!= 、not like等
上面几种情况都不能使用索引，索引只能用来做等值查询或范围查询，类似上述不等于的情况，无法使用索引。
应对方法
如果经过上述谓词条件可以过滤掉大部分数据，那么可以通过改写sql+创建函数索引的方式，把不等于的sql，改写成等于，从而实现优化的目的。 详见 必须通过改写SQL才能提升性能的一些情况 第7种情况
14. 使用了sql profile
sql profile是10g版本引入的固定sql执行计划的技术，已经被DBA广泛使用。但是这个工具的原理和使用技巧，很多人还是没有完全掌握。
在检查sql执行计划时，一般建议使用dbms_xplan.display* 方法，这种方法显示的信息最全，很多DBA和开发人员喜欢用plsql developer的F5查看执行计划，会错过很多重要的信息。dbms_xplan.display*显示的执行计划，如果在note部分显示"SQL profilexxxxxxxxxxxxxx used for this statement"，说明使用了sql profile。 如果sql profile中没有用索引，那么索引就不会被使用。
注： 生成sql profile一般有两种方法，一种是sql tuning advisor，这种方法生成的sql profile，还是可能随着统计信息的变化，执行计划会发生改变；另一种是使用coe_load_sql_profile.sql 脚本生成的sql profile，就不会随着统计信息的变化发生执行计划改变。
应对方法：
可以在sql增加一些注释，避开sql profile绑定的执行计划，再看看相关索引能否被使用。如果sql profile选择的执行计划不合适，可以将该sql profile 删除。
15. 使用了sql plan baseline
sql plan baseline是11g版本引入的，对于绑定变量敏感的SQL，可以设定执行计划基线，可以选择使用多个基线。用dbms_xplan.display* 显示执行计划时，如果在note部分显示"SQL Plan baseline xxxxxxxxxxxxxx used for this statement"，说明使用了sql plan base line。 这种情况下，如果执行计划没有使用索引，就可能是sql plan baseline在起作用。
应对方法：
可以在sql增加一些注释，避开sql plan baseline绑定的执行计划，再看看相关索引能否被使用。如果sql plan baseline选择的执行计划不合适，可以drop掉。
16. 使用了sql patch
sql patch 是10g版本就提供的一个内部方法，使用起来还不是太方便，好在coe专家们提供了一个脚本coe_gen_sql_patch.sql，使用起来也是比较容易的。到了12.2版本，这个方法变成了一个public api，直接使用也是非常简单。
sql patch有自己的独特用途，一般不用来做固定执行计划使用，但是也可以用来控制执行计划。用dbms_xplan.display* 显示执行计划时，如果note部分显示"SQL Patch xxxxxxxxxxxxxx used for this statement"，说明使用了sql patch。
应对方法
可以在sql增加一些注释，避开sql patch绑定的执行计划，再看看相关索引能否被使用。如果sql patch选择的执行计划不合适，可以drop掉。
以上只是列举了一部分索引（B-Tree索引）不能被使用的一些情况，应该还有一些不常见的情形，比如在字符串字段上创建了desc 降序索引，like 'xxxx%'这种sql就无法使用这个降序索引，加hint也不行；reverse key反向键索引在范围查询无法使用等，欢迎大家留言补充。同时也欢迎有识之士批评指正。
参考
https://mp.weixin.qq.com/s/9CT96VrPn1YWWT52nnkvbA
