rownum与Oracle分页SQL 以及提高分页查询效率

分页查询模板：

SELECT * FROM ( SELECT ROWNUM row_num, t2.* FROM    (...) t2 WHERE ROWNUM <= 30 ) t3 WHERE t3.row_num  > 20 

简单示例：

SELECT * FROM ( SELECT ROWNUM row_num, t2.* FROM    (
        SELECT USER_NAME,PASSWORD FROM T_USER T WHERE T.USER_NAME= 'ZHOU' AND T.PASSWORD = '123' ORDER BY T.ID) t2  
        WHERE ROWNUM <= 30
 ) t3 WHERE t3.row_num  > 20  

注意：将外层的查询条件推到内层查询中，可提高查询的执行效率。

分析：rownum是伪序列，是对结果集的编序排列，即先有符合条件的结果，再依次递增排序。rownum始终是从1开始，首先给第一个结果的序号为1，当第一个结果满足条件存在时，才会依次增加，若第一个结果不符合条件，会把下一个结果重新从1开始排序，如果你的条件是rownum>2，那么第一个结果rownum=1不满足条件，将会对下一个结果从1开始排序，因而所有结果的序列都将会是1，所以你的rownum>2永远为假，也就没记录返回

–添加说明–
–①之所以oracle需要三层嵌套来实现分页原因如下：
– 因为在查询的时候，order by 的执行是在 select 之后的，
– 所以如果在第一层查询中查出ROWNUM，再按某一字段排序后，
–会导致查出得到的ROWNUM值顺序混乱，没办法进行分页

–②如何提高效率(前提保证逻辑正确)：
– 尽可能把过滤条件放在最内层，内层过滤掉的东西越多，外层查询的数据越少，效率越高
– 必要的查询字段一一写在最内层，而不要用 t.*，这样可以极大减少外层的查询数据量
– 尽可能把计算放在外层，减少计算次数

示例代码：

SELECT row_num, "id", "name", "age",TO_CHAR("birthday",'dd-MM-yyyy HH24:mi:ss') FROM (
SELECT ROWNUM AS row_num, t2.* FROM 
(
SELECT "id", "name", "age",T."birthday" FROM  TEACHER t  ORDER BY t."id" DESC

) t2 WHERE ROWNUM<= 5
)t3 WHERE t3.row_num >1