先发一个培训通知：

 成都站培训 如期举行，时间定在11.16~11.17 周末两天。2人及以上组团报名有优惠，可以加微信ora_service咨询报名。

正文开始

在上一篇我们讲了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反向键索引在范围查询无法使用等，欢迎大家留言补充。同时也欢迎有识之士批评指正。

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

（全文完）

欢迎加入技术讨论分享群：

QQ群：16778072 （群文件共享了一些学习材料和工具）

微信群：先加微信ora_service，再入群（只要对oracle性能优化感兴趣，都可以加入）