like等价改写substr优化案例一则

客户有一套5节点的RAC系统，配置比较高,类似exadata的配置，业务量非常大，CPU使用率经常在90%以上，其中一个CPU TOP 1 SQL,占了总CPU资源将近10%。原SQL稍复杂，有UNION ALL，还有row_number分析函数，这里做了简化，只抽取了其中核心部分.

SQL：

select * from

(SELECT item_ver

FROM items T

WHERE :B1 BETWEEN T.FIRST_SN AND T.LAST_SN

AND :B1 LIKE SUBSTR (T.FIRST_SN, 1, 10) || '%'

order by T.UPDATED_DATE

)

where rownum<=1;

已知条件：

items表有500万左右的记录，12.5万 blocks，当前使用的是FIRST_SN + LAST_SN 两字段联合索引。平均buffer gets 2800，执行时间40毫秒（开发人员认为40毫秒完全可以接受，但是由于执行非常频繁,系统资源接受不了）。

满足 :B1 BETWEEN T.FIRST_SN AND T.LAST_SN 谓词条件的记录数较多，满足 :B1 LIKE SUBSTR (T.FIRST_SN, 1, 10) || '%' 谓词条件的记录数也比较多，但是两个条件都满足的记录数相对较少。

分析：

这是一个区间检索的SQL，但不是严格的区间检索，因为有大量的重复区间，还要把满足条件的全部记录排序后再取日期最早的一条（如果是严格的区间检索，一个给定的B1值，最多只会对应一个区间，区间不重复,以前的公众号文章有介绍,优化后效果会非常好）。所以用严格区间检索的优化方法在这里就不行了，但是如果只是使用index_desc的hint让索引降序扫描，应该还是会有一些提升。

最终优化建议,改写+索引调整：

1、改写SQL，将:B1 LIKE SUBSTR (T.FIRST_SN, 1, 10) || '%' 等价改写为下面红色部分：

select * from

(SELECT item_ver

FROM items T

WHERE :B1 BETWEEN T.FIRST_SN AND T.LAST_SN

AND SUBSTR(:B1,1,10) = SUBSTR (T.FIRST_SN, 1, 10)

order by T.UPDATED_DATE

)

where rownum<=1;

2、为了让索引发挥最大作用，让last_sn字段也起到索引的作用 , 创建SUBSTR (FIRST_SN, 1, 10) + LAST_SN + FIRST_SN + UPDATED_DATE 4字段联合索引 .

随机选择一个绑定变量在测试环境测试，原来将近2000 buffer gets，优化后变成11个buffer。

一段时间后,客户发邮件说:"上次做的主动优化效果挺好的,系统运行稳定,顶住了业务高峰的浪涌,感谢你们的主动服务,......". (当然,除了top cpu1 sql以外,还有其他一些top sql也做了优化调整,整体优化后大概有20%左右的系统CPU使用率下降)

能得到客户这样的评价还是很受用的.