Oracle分页查询（效率分析与原理）

Oracle的分页查询不像MySQL这么友好，要搞清楚Oracle的分页我们需要了解Oracle特有字段rownum

rownum：是Oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且 rownum 不能以任何表的名称作为前缀。

rownum字段无法使用 >符号 ：如果想找到从第二行记录以后的记录，当使用 rownum > 2 是查不出记录的，原因是由于 rownum 是一个总是从1开始的伪列，Oracle 认为 rownum > n(n>1的自然数) 这种条件依旧不成立，所以查不到记录。

所以直接对单表 rownum 字段的使用 BETWEEN ... AND ... 也是不可取的

所以我们需要用到子查询的方式来实现分页查询：就是我们先把数据查出来，利用 rownum 得到的字段进行分页

第一种方法

无ORDER BY排序

写法1：虽然别的博主说这样写的效率快，因为

写法2 需要将内层的结果集全部排序，再从中取出需要的部分；而 写法1 只需要获取排序后<= #{pageNo} * #{pageSize}部分的结果就可以了。一般分页查询访问前面部分页面的几率较大，内层查询的结果集越大，性能差距越明显。如果是访问分页的最后部分的页面，基本上就没什么差别了。

但是在我测试了 3w 条数据量的情况下，区别不是很大，但大家尽量选 写法1 ，因为光看 SQL 语句看着也是 写法1 快一些

 select b.*,rn from (
        select a.*,rownum rn
        from TABLE1 a
        where 1=1 and rownum <= #{pageNo} * #{pageSize}
 ) b where b.rn >= (#{pageNo} - 1) * #{pageSize} + 1

写法2

 select b.*,rn from (
        select a.*,rownum rn
        from TABLE1 a
        where 1=1
 ) b where b.rn >= (#{pageNo} - 1) * #{pageSize} + 1 and b.rn  <= #{pageNo} * #{pageSize}

有ORDER BY排序

如果遇见复杂的查询，可以再套一层

        select * from (
            select C.*,rownum rn from (
                select A.*
                from TABLE1 A
                    inner join TABLE2 B
                        on A.id = B.id
                where 1=1 and A.id = #{id}
                order by A.id
            ) C where rownum <= #{pageNo} * #{pageSize}
        ) D where D.rn >= (#{pageNo} - 1) * #{pageSize} + 1

第二种方法

 select b.*,rn from (
        select a.*,rownum rn
        from TABLE1 a
        where 1=1 order by a.id
 ) b where rn BETWEEN 1 and 10  # 后面就是 11 到 20 、 21 到 30 以此类推