[大数据]Oracle分页查询

Oracle分页查询

方法一

-- 40为pageCurrent * pageSize，31 应为为(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 >= 31

方法二

SELECT * FROM  
(  
SELECT A.*, ROWNUM RN  
FROM (SELECT * FROM TABLE_NAME) A  
)  
WHERE RN BETWEEN 31 AND 40 

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

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

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

注意

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

SELECT * FROM
        (SELECT tt.*,ROWNUM AS RN FROM
        (SELECT t.* FROM ${tableName} t
         where t.sjcp_del_flag = 0
         ORDER BY t.SJCP_UPDATE_TIME DESC) tt
        ) table_alias
        WHERE table_alias.RN >= #{pageStartOracle}
        and table_alias.RN <= #{query.size}*#{query.current}