优化使用ROWNUM进行分页查询的SQL

下面这个SQL是web页面查询分页显示的一个常见用法：

select *

  from (select row_.*,rownumpafa_rownum

          from (selecta.id_qt_wait_task_infotaskId,  a.department_codedepartmentCode, insuranceEndTime,......

                  fromqt_wait_task_info a

                 where 1 = 1

                   anda.task_state in ('B0','B1', 'B2', 'B3')

                   ANDa.plan_code = :1

                   ANDa.date_input <to_date(:2, 'yyyy-mm-dd') + 1

                   ANDa.date_input >=to_date(:3, 'yyyy-mm-dd')

                   ANDnvl(a.key_customer_id, ' ') !='SANY'

                   AND (upper(a.input_by_id) =upper(:4))) row_)

 where pafa_rownum<= :5 andpafa_rownum> :6;

这个SQL平均执行时间310毫秒，执行计划是：

现在我们将where后面红色的部分做一个改写：

select *

  from (select row_.*, rownumpafa_rownum

          from (select a.id_qt_wait_task_infotaskId,  a.department_codedepartmentCode, insuranceEndTime,......

                  from qt_wait_task_info a

                 where 1 = 1

                   and a.task_state in ('B0','B1', 'B2', 'B3')

                   AND a.plan_code = :1

                   AND a.date_input < to_date(:2, 'yyyy-mm-dd') + 1

                   AND a.date_input >= to_date(:3, 'yyyy-mm-dd')

                   AND nvl(a.key_customer_id, ' ') !='SANY'

                   AND (upper(a.input_by_id) =upper(:4))) row_  where rownum<=:5)

 where  pafa_rownum> :6;

再来看一下执行计划与consistent reads：

consistent reads只有25，执行时间0.165毫秒，性能提高1800倍(310/0.165=1800)。

各位研发的朋友，分页查询SQL不能再任性随意写了，性能差距太大了。

有人说你这个没有order by，为什么？ 那是因为加了order by后原来的SQL要跑180多秒，根本不可能满足业务需求。而加了order by 的改写SQL，执行时间只需要0.28秒。

rownum分页查询的sql还有一个性能方面需要注意的是到底使用order by字段的索引还是使用where子句后面谓词条件的索引，这个一般要靠优化器自己去计算，有时候优化器计算不太准确，可能需要加Hint来强制选择性能好的索引。