Oracle 分页查询的一次Bug

     之前有一篇博客讲述，oracle 11g的查询计划的Bug，

     https://www.cnblogs.com/LinXuDong/p/6077302.html

     由于没有走最优查询计划导致查询时间暴涨。昨天自己线上项目也出现了类似的问题，具体语句如下：

   select *
  from (SELECT t.*, ROWNUM RN
          from (select a.BANK_ACC,
                       a.BANK_CODE,
                       a.BANK_ACC_STATUS,
                       a.ACTIVE_DATE,
                       a.CANCEL_DATE,
                       b.CLIENT_NAME,
                       b.CLIENT_EN_NAME,
                       b.FUND_ACC,
                       b.CLIENT_ID,
                       b.BIND_DATE,
                       to_char(b.BIND_TIME, 'fm000000') as BIND_TIME,
                       b.CLOSE_DATE,
                       b.CUST_ID,
                       b.EXPIRE_DATE,
                       b.IS_EXPIRED,
                       b.IS_FOREVER,
                       b.CPY_BANK_ACC,
                       b.STATUS as BIND_STATUS
                  FROM HKVMR_USER.EMHK_VMR_BANKACC a
                 inner join HKVMR_USER.EMHK_VMR_BANKFUNDACC b
                    on (a.BANK_ACC = b.BANK_ACC AND a.BANK_CODE = b.BANK_CODE)
                 where (('2' = '2' and '2' = '2') or
                       (b.STATUS = '' and b.STATUS = ''))
                   AND a.BANK_CODE = '012'
                   AND (b.CLOSE_DATE >= TO_NUMBER('20180219', 99999999))
                   AND (TO_NUMBER('20180301', 99999999) >= b.CLOSE_DATE)
                   AND rownum > 0
           order by a.BANK_ACC) t
         where rownum <= 30)
 where RN >= 1

注意红色部分即为强制SQL执行最佳执行计划。

上面语句中如果去掉红色部分可以导致两个执行计划

最优计划如下：

非最优计划

以上问题为oracle 11g Bug导致。记住此次惨痛教训