古诗大全

一、SQL无法走索引的情况及解决思路

因为数据库优化器不够智能，或者一些逻辑原因，导致SQL在比较适合走索引的情况下却无法正确利用索引。这时候，除了给数据库需要的统计信息之外，SQL语句本身还必须要给优化器足够多的额外有效信息，帮助优化器能够选择更好的执行计划。要让优化器正确选择需要的索引，要考虑两点：

说明：这里说的走不了索引，是指走不了正常的RANGE SCAN，非(FAST) FULL INDEX SCAN。

SQL无法走索引常见的有如下8种情况：

统计信息不准确索引列的值允许为NULL谓词使用了不等于（<>, !=）LIKE前通配或全通配的查询索引列使用了函数、数学运算、其它表达式等使用了隐式类型转换查询转换失败其它语句逻辑原因

第一、二种情况在现实中比较常见，解决办法也相对比较简单，下面就不再作详细展开了。

谓词使用了不等于(<>,!=)，走不了索引

解决方法：

如果不等条件之外的值不多，而且是确定的，可以改为等值或IN查询，比如status状态字段一般值类别很少；如果不等条件之外的值很多，可以改为“> OR <”的形式，当然第2种方法包含了方法1。

举个例子，先构建测试场景：

谓词使用<>，无法利用索引：

将<>改写为OR连接后，能够正确使用索引，走OR扩展：

如果业务允许，改写为下列语句也是走索引的，不再演示。

SELECT * FROM t WHERE t.NAME IN (‘ORADB1′,’ORADB2′,’ORADB3’);

LIKE前通配或全通配的查询，走不了索引

解决方法，有如下三种：

（1）根据业务需求，是否可以把前通配去掉

原来全通配，无法走索引：

把前通配去掉，改为后通配，可以正常使用索引：

（2）和此LIKE一样的前通配或全通配的SQL有很多，此谓词的LIKE变化不大？如果是，考虑建立函数索引，否则对于全通配问题最好办法就是全文索引。

创建instr函数索引：

（3）如果只是前通配，可以使用reverse函数索引（不是翻转键索引）

原始语句：

SELECT * FROM t WHERE t.NAME LIKE ‘%ORADB1’;

创建reverse函数索引，并改写语句，注意查找值要倒序：

注意：如果通配查询的是中文，要注意使用REVERSE翻转条件值，因为REVERSE内部会按字节翻转的，正确写法如：

SELECT * FROM t WHERE REVERSE(t.name) LIKE REVERSE(‘数据’)||’%’;

否则查询出来的数据不对，将可能影响到业务的正常运行。

索引列使用了函数、数学运算、其他表达式等sql创建索引语句，走不了索引

解决方法：去掉对索引列的相关运算，保持索引列纯净。

目前优化器对一些数学运算，还无法做很好的消除动作，所以对于索引列应该尽量保持纯净，否则可能无法用上正确的索引。

举例：

把语句的条件改写一下，将运算去掉：

以上例子只是简单的数学运算，可能的运算还有和其他列运算，比如where ID+ext_col…

记住一个原则：尽量保持索引列纯净。

使用了隐式类型转换，走不了索引

解决方法：必须避免隐式类型转换，全部要求显式类型转换（非索引列），且避免对索引列进行类型转换（有函数索引除外）。如果类型不一致，不管是否发生自动类型转换，谓词的右值应该显式转换为与索引列保持一致（对于非索引列的运算也应该如此）。

举例：

从以上两次查询对比来看，第一次查询发生了类型转换，可以通过执行计划中的谓词信息获知。通过分析发现，X因为是VARCHAR2，优先级比数值类型低，遇到数值类型，会TO_NUMBER隐式转换，所以索引失效。第二次查询，通过传入与索引列类型一致的字符串后，得以解决。

查询转换失败，走不了索引

查询转换是非常复杂的过程，ORACLE CBO的查询转换有好几十种，比如CVM ：complex view merging ，SU:subquery unnest, JPPD:JOIN PREDICATE PUSH DOWN等（在10053文件里都可以看到）。如果查询转换失败，那么必将影响后续优化器的一些操作，比如JPPD中JOIN谓词无法推入到视图中，那么很可能视图就无法走索引了。而且，查询转换有很多BUG，触发BUG需要找到原因，比如设置隐含参数、fix control等，或者改写SQL绕过BUG。如下例所示：

其中AB_XRTOFFREC_201703是UNION ALL查询组成的视图，这个查询在10.2.0.4上很正常，升级到11.2.0.4后执行计划显示不走索引，性能非常差。

在10g中的执行计划：

在11g中的错误执行计划：

通过收集统计信息都无效，将优化器降级到10.2.0.4即有效。很显然，这是引入了BUG或者新的限制。一旦遇到这种是BUG或限制导致的，可以通过10053跟踪文件或者SQLT来进行分析。对于这条语句无法走JPPD查询转换，在10053中就可以找到原因：

然后在MOS中查看得知是BUG:9380298，默认开关关闭。

ORACLE针对这样的查询，为了防止遇到笛卡尔积，默认把修复BUG的补丁关闭了。显然通过设置_fix_control参数打开9380298 fix即可。

语句逻辑问题，导致优化器选择不了索引

举一个典型的例子，先准备测试表，并在其上创建一个组合索引：

查询需求：查找创建时间是2013年的，并且最后ddl时间比创建时间大1天以上的对象。

这个索引是组合索引，上面的语句对前导列进行了运行，也不符合走index skip scan的条件sql创建索引语句，所以，走FULL TABLE SCAN。那么是否可以通过逻辑改写走索引呢，基于保持索引列纯净的原则，将create_date移到右边，语句如下：

改写后发现，还是没有走索引，因为Oracle认为前导列右边的created不固定，无法从指定索引处查找。通过分析得知，Oracle谓词传递有一定限制，create_date+1无法做谓词传递给last_ddl_time。再次改写：