68-oracle数据库，有索引，但是没有被使用的N种情况，以及应对方法（下篇）

正文开始

在上一篇我们讲了8种索引没有被使用的情况，今天我们接着再讲8种其他场景：

1. 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的优化，该如果创建索引，写法又该是怎样？ select max(created),min(object_id) from t1 where owner='SYS'； 已知owner字段选择性差。
2. 谓词条件使用了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，则两个字段任何一个字段上有索引，都会使用。
3. 函数索引中使用了常量，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; 上面sql写法是无法使用索引的。 应对方法： 必须把函数索引中的b1绑定变量换成常量9或10。 还有其他诸如 mod(xxid,10)的这种函数，如果创建了函数索引，也是不能使用绑定变量的，放在组合索引里面也只能起到过滤作用，起不到索引作用。
4. 复合索引，前导字段没有用到，而且NDV（唯一值个数）较高 t1表存在两字段复合索引：object_id , object_type （object_id 唯一值个数多，选择性好） sql: 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估值偏差会比较大，将会导致优化器选择的执行计划可能不是最优。 经过上面的解释说明，你们还认为原文给出的建议合理吗？ （案例点评完）
5. 谓词条件是 not in(1，2) 、<> 、!= 、not like等 上面几种情况都不能使用索引，索引只能用来做等值查询或范围查询，类似上述不等于的情况，无法使用索引。 应对方法： 如果经过上述谓词条件可以过滤掉大部分数据，那么可以通过改写sql+创建函数索引的方式，把不等于的sql，改写成等于，从而实现优化的目的。 详见本公众号的《第61篇-必须通过改写SQL才能提升性能的一些情况》的第七种情况。
6. 使用了sql profile sql profile是10g版本引入的固定sql执行计划的技术，已经被DBA广泛使用。但是这个工具的原理和使用技巧，很多人还是没有完全掌握。 在检查sql执行计划时，一般建议使用dbms_xplan.display* 方法，这种方法显示的信息最全，很多DBA和开发人员喜欢用plsql developer的F5查看执行计划，会错过很多重要的信息。 dbms_xplan.display*显示的执行计划，如果在note部分显示"SQL profile xxxxxxxxxxxxxx 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 删除。
7. 使用了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掉。
8. 使用了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反向键索引在范围查询无法使用等，欢迎大家留言补充。同时也欢迎有识之士批评指正。

欢迎转发，转发就是对本人最大的支持。

（全文完）