rownum应用系列之分页查询

rownum这个伪列在SQL中用途广泛，归纳起来大致有3总用法：

1、分页查询 rownum<= :b1 and RN>:b2 (rownum不能>)

2、rownum<= :b1

3、rownum=1 或 rownum<=1 ，也是rownum<=:b1的一种特例

我们接下来分别会找相关的优化案例来进行分析。

今天先谈谈简单的分页查询写法，虽然是老生常谈，但是因为在很多客户的大型系统中都发现了低效写法，还是再重新加强一下，如果能做成开发规范最好：

低效的写法：

select column_lists from

(select rownum as rn,A.* from

(select column_lists from table_name where col_1=:b0 order by col_2) A

) where rn<=:b2 and rn>:b1;

高效的写法，注意红色部分内容的位置：

select column_lists from

(select rownum as rn,A.* from

(select column_lists from table_name where col_1=:b0 order by col_2) A

where rownum<=:b2

) where rn>:b1;

原理：

低效写法需要将内层的结果集全部排序，再从中取出需要的部分；而高效写法只需要获取排序后<=:b2部分的结果就可以了。

一般分页查询访问前面部分页面的几率较大，内层查询的结果集越大，性能差距越明显。

如果是访问分页的最后部分的页面，基本上就没什么差别了。

oracle 12c 中使用了简洁的offset 语法，其实是使用了分析函数row_number()在内部做了改写，效率也很高。当前的主流写法还是上面使用rownum伪列的方法。

思考一下：

这样的分页查询语句，应该创建怎样的索引？