Oracle分页查询

Oracle分页查询

方法一

-- 40为pageCurrent * pageSize，30 应为为(pageCurrent - 1) * pageSize
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME
WHERE 1 = 1 -- 条件
ORDER BY CREATETIME DESC -- 排序
) A
WHERE ROWNUM <= 40
)
WHERE RN > 30

方法二

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE 30 < RN AND RN <= 40

方法一比方法二效率要高很多，查询效率提高主要体现在 WHERE ROWNUM <= 40 这个语句上。

这是由于CBO 优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。方法一中，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

方法二中，由于查询条件30 < RN <= 40 是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。在方法二中，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，效率要比方法一要低得多。

注意
由于oracle排序算法问题，如果排序遇到相同的条件，比如时间，会使分页后一页包含前一页的内容，所以这个时候要把方法改成下面这两种。

方法一，order by 加上id主键

SELECT * FROM
(SELECT tt.*,ROWNUM AS RN FROM
(SELECT t.* FROM ${tableName} t
where 1=1 -- 条件
ORDER BY t.createTime DESC,t.id -- 排序) tt
WHERE tt.ROWNUM &lt;= #{pageNum}*#{pageSize}
) rs
WHERE rs.RN > #{pageNum-1}*#{pageSize}

• 方法二

SELECT * FROM
(SELECT tt.*,ROWNUM AS RN FROM
(SELECT t.* FROM ${tableName} t
where 1=1 -- 条件
ORDER BY t.createTime DESC -- 排序) tt
) rs
WHERE #{pageNum-1}*#{pageSize} &gt; rs.RN &lt;= #{pageNum}*#{pageSize}