Oracle rownum应用系列之分页查询
Tags： Oracle
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伪列的方法。
思考一下：
这样的分页查询语句，应该创建怎样的索引？
昨天的思考题，老虎刘的建议是：
创建col_1和col_2字段上的联合索引，避免排序，提高效率。联合索引的字段顺序不能错，order by的字段要放在联合索引的最后。如果是order by desc，正常情况优化器会自动使用index descending扫描方式，不需要建索引的时候加desc 。也有sql复杂的时候优化器没有使用index descending扫描方式，则可以用index_desc来纠正。
今天刚好遇到一个分页查询的问题SQL，简化如下：
SELECT *
FROM (
SELECT
