SQL优化，需要多一些严谨的态度

分享是一种美德，接受得了批评建议更是难得的美德。有很多大师写博客、写公众号、写书，都是一种分享,好事情.但有些人有这样一个毛病，不喜欢听别人的批评建议。公众号下面的留言是一种很好的技术交流途径，有些公众号只把吹捧的评论显示出来，批评建议留着自己"欣赏",这种行为我认为是非常可耻的,因为你宣传了错误的东西又没有更正,会误导公众。这些公众号屏蔽批评建议的做法不知是不是小编私下做主，建议请示下领导，做技术的容不下批评还做个屁啊。大师也有犯错的时候，承认错误不可耻。

在知识累积不够或是考虑不周的情况下，有时就会表现出严谨度不够、方法欠佳等问题,更有甚者还可能得出一些错误的结论。所以有时候知道的越多反而越不敢写。老虎刘非常欢迎大家公开讨论技术,而且保证全部批评建议公开.

下面就列举一些例子（有些例子是网友提供,表示感谢）：

case1（来自某用户组）：改写建议,严谨不足

原SQL: TO_CHAR(a.VERI_DATE,'yyyymmdd') = :B1

原改写建议：

a.VERI_DATE>truncate(sysdate-1) and a.VERI_DATE <=truncate(sysdate)

这个改写建议我相信很大程度上可能是笔误，因为原文把trunc还写成truncate了。其实等价改写应该是这样：

a.VERI_DATE>=to_date(:B1,'yyyymmdd') and a.VERI_DATE<to_date(:B1,'yyyymmdd')+1

原改写的问题关键不在于用:B1还是sysdate（应该B1取的就是前一天的日期），而是等于号在前还是在后。原改写没有把前一天的第一秒算进去，反倒是把第二天的第一秒算了进来。一秒钟可能发生很多事情.

case2（来自某用户组）：改写建议,严谨不足

原SQL:TO_CHAR(remote_date,'yyyymmdd') between '20171201' and '20171231'

原改写建议：

remote_date between to_date('20171201','yyyymmdd') and to_date('20171231','yyyymmdd')

这个改写把20171231这一天只取了00:00:00有一个时间点，剩下的23小时59分59秒都丢掉了,这个改写就太不严谨了。

正确的改写应该是：

remote_date >=to_date('20171201','yyyymmdd') and remote_date <to_date('20171231','yyyymmdd')+1

case3（来自某用户组）：误打误撞,问题解决,结论错误

这个案例虽然最后的优化效果堪称完美，但是对问题根源的分析却是错误的。原SQL：

Select xxx FROM cs_custinfo where vsrcrowid = :"SYS_B_0" and nvl(dr,:"SYS_B_1")=:"SYS_B_2";

vsrcrowid这个字段的选择性很好，而且存在索引，但是执行计划却使用了全表扫描。原作者把这个不使用索引的问题归结于普通索引+高聚簇因子(CLUSTERING_FACTOR)。最后建议客户将字段内容做了去重处理，改成唯一索引解决,而且由此得出的结论是唯一索引比范围索引的效率高很多。

如果这个字段不适合唯一索引怎么办呢？问题原因真的是上面分析的结果吗？

其实从原作者描述的症状来看，这个case的根本原因是因为直方图的局限性所致（我在以前的公众号文章有介绍）：11g的直方图最多识别字符串的前32位（12c识别到前64位），如果字符串的前32位都相同，那么直方图就会误认为字段只有一个唯一值，当然会走全表扫描；而字段上如果创建了唯一索引，那就不会创建直方图，就避免了这个问题。 这个case的常规解决方法是使用hint强制使用索引，或者删掉vsrcrowid字段上的直方图信息。而不是把索引改成唯一索引.

case4(来自某公众号)：画蛇添足

原SQL,效率差,消耗大量CPU资源:

原索引字段:(ONLINEDATE, ORGID,OPCODE, SSCODE, MOCODE);

原作者通过改写SQL,调整索引对SQL进行了优化:

第一次改写后SQL(sql开发规范基本原则,尽量不在字段上做操作):

同时修改索引字段为:(ORGID, OPCODE, SSCODE,ONLINEDATE, MOCODE);(这个组合索引是调整关键)

其实到这里已经是很完美的解决了问题(不包括最后使用result_cache),但是,接下来作者又做了进一步的改写,索引还是上面修改后的索引,SQL变简单了,同时还使用了一个index_desc的hint:

这里就有两个问题了:

1.在索引正常的时候,这个改写后的sql得到的结果确实可能和原SQL一致,为什么说可能? 因为onlinedate>=trunc(sysdate)条件没有体现出来,sql选出的最大onlinedate不一定满足>=trunc(sysdate)条件,此错误1,虽然发生概率较小,影响也不大,得到的结果可能也满足生产需求.

2.在索引不可用时(invisible或是被drop的情况下),sql的结果就完全不对了(会随机找一条满足条件的记录),没有索引就得到的错误结果,那完全是不可接受的事情.

二次改写后的sql比第一次改写没有任何的性能提升,这种做法就有点画蛇添足了.

类似的不严谨文章还有很多:改写不等价,得出错误结论,优化不到位等.SQL改写需谨慎,得出结论需严谨.