Oracle如何实现分页查询

Oracle采用ROWNUM实现分页

格式1

• sql脚本

  
  
      -- 格式1(推荐)
      SELECT * FROM   
      (  
          SELECT temp.*, ROWNUM rn   
          FROM (SELECT * FROM 表名 WHERE 条件 ORDER BY ROWID) temp  
          WHERE ROWNUM <= end (page*pagesize)  
      )  
      WHERE rn > start (page-1)*pagesize
  
  
  

• 案例

  
      -- 查询员工信息的6-10条数据 第二页数据
      select rownum,t.* from (select rownum r,e.* from emp e where rownum <=10) t where r>5;
  
      -- 分页查询员工信息按照工资排序
      select * from (select rownum r,t.* from (select * from emp  order by sal) t where rownum<=10 ) where r>5
  
  

• 分析

  从效率上看，上面的SQL语句在大多数情况拥有较高的效率，主要体现在WHERE ROWNUM <= end这句上，这样就控制了查询过程中的最大记录数，而在查询的最外层控制最小值。

  但最大值意味着如果查到了很大的范围（如百万级别的数据），查询就会从很大范围内往里减少，效率就会很低，因此，当面对大数据量时或者优化查询效率时，可以考虑使用ROWNUM结合ROWID，见末尾

格式2

• sql脚本

  
      -- 格式2
      SELECT *
  
      FROM (SELECT tt.*, ROWNUM AS rowno
      
              FROM (  SELECT *
      
                          FROM k_task t
      
                      WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')
      
                                              AND TO_DATE ('20060531', 'yyyymmdd')
      
                      ORDER BY fact_up_time, flight_no ) tt
          ) table_alias
      
      WHERE table_alias.rowno BETWEEN 10 AND 20;
  
  
  

• 分析

  第一个查询的效率比第二个高得多。

  这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。

  对于格式1，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

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

  上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效

Oracle采用ROWID实现分页

格式1

• sql脚本

  
  
      -- 格式1
      SELECT *
      FROM (SELECT RID
              FROM (SELECT R.RID, ROWNUM RN
                      FROM (SELECT ROWID RID
                              FROM TABLE1
                              WHERE TABLE1_ID = XX
                              ORDER BY order_date DESC) R
                      WHERE ROWNUM <= 20)
              WHERE RN >= 10) T1,
          TABLE1 T2
      WHERE T1.RID = T2.ROWID;
  
  
  
  

• 分析

  首先通过ROWNUM查询到分页之后的10条实际返回记录的ROWID，最后通过ROWID将最终返回字段值查询出来并返回

  以上共有4层Select嵌套查询，最内层为可替换的不分页原始SQL语句，但是他查询的字段只有ROWID，而没有任何待查询的实际表字段，具体查询实际字段值是在最外层实现的

参考链接

https://blog.csdn.net/use_admin/article/details/83622414
https://blog.csdn.net/death05/article/details/78744250