Oracle-SQL-优化：rownum应用系列之分页查询（一）

1 rownum 3种用法介绍

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

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

2）rownum<= :b1

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

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

2 rownum 分页方法

2.1 SQL案例1

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

低效的写法：

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;

2.2 SQL rownum 分页优化

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

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;

2.3 rownum 分页优化原理

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

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

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

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

2.4 思考一下SQL如何建索引

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

建议：

创建col_1和col_2字段上的联合索引，避免排序，提高效率。联合索引的字段顺序不能错，order by的字段要放在联合索引的最后。

如果是order by desc，正常情况优化器会自动使用index descending扫描方式，不需要建索引的时候加desc 。

也有sql复杂的时候优化器没有使用index descending扫描方式，则可以用index_desc来纠正。

2.1 SQL案例2

SELECT *

FROM (

SELECT

......

,ROWNUM RNSTART

FROM S_SRV_REQ SSR

WHERE SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60 AND

ROWNUM <= 10 ORDER BY SSR.CREATED DESC

) T

WHERE T.RNSTART >= 1;

这个SQL有两个问题：

1、分页查询的写法有问题，rownum 与 order by并列同时出现在同一个where后面，这种写法得到的结果可能是乱序（除非使用CREATED字段上索引的降序扫描，但是如果该索引不可用，得到的结果就不对了），需要按照上一篇文章的写法进行改写。

2、谓词条件SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60过滤性很好，而且存在ACT_OPEN_DT字段上的索引，但是因为字段上存在表达式 “+1 / 3“，导致索引不能使用，需要改写sql，将 SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60 改成SSR.ACT_OPEN_DT >= SYSDATE - 60 - 1 / 3。

注：

因为谓词条件不是等值条件（昨天的sql是col_1= :b1，今天是col_x >=），这个sql如果创建ACT_OPEN_DT和CREATED联合索引是没有意义的，这种情况还是只会使用索引的第一个字段，无法避免排序。