由索引未被使用，看SQL开发规范落地(2)

从以上两次查询对比来看,第一次查询发生了类型转换,可以通过执行计划中的谓词信息获知.通过分析发现,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的条件,所以,走FULL TABLE SCAN.那么是否可以通过逻辑改写走索引呢,基于保持索引列纯净的原则,将create_date移到右边,语句如下：

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

此时Oracle知道将谓词传递给last_ddl_time了,T.LAST_DDL_TIME>=TO_DATE(‘ 2013-01-01 00:00:00’,‘syyyy-mm-dd?hh24:mi:ss’).当然,也可以手动谓词传递,last_ddl_time肯定大于等于DATE’2013-1-2′：

还未完,我们继续往下看：

如果查询条件中无t.created>=DATE’2013-1-1’,即如下面语句：

–由此两数据比较可知,应该走索引更佳.因为没有其他过滤条件,可以考虑建立函数索引：

SQL> CREATE INDEX idx1_t_object ON t_objects(last_ddl_time-created);

–注意收集直方图,因为分布不均

SQL> exec dbms_stats.gather_table_stats(ownname => USER,tabname => ‘t_objects’,estimate_percent => 100,method_opt => ‘for all indexed columns’,cascade => TRUE);

当然,对于两个都是范围的查询,这里只能通过一个列来轮询索引,先做access,再做filter.

（编辑：ASP站长网）