Oracle一个sql完成分页并返回count(*)作为总条数

1.背景

• 最近要分页来防止大对象产生，在oracle中分页，用的这个方式

SELECT * FROM (
 
        SELECT A.*, ROWNUM RN  FROM (
             
             SELECT c1,c2,count(*) over () total  -----依靠count(*) over ()查出 数量总和       
             FROM  TABLE_NAME
             
        ) A  WHERE ROWNUM <= 40
        
)WHERE RN >= 21

2.执行结果

原理分析

1.分页

• 这个sql一共分为三层，
• • 第一层直接查询出结果，
• • 第二层增加<=40的查询条件，
• • 第三层增加了>=21的查询条件。
• oracle优化会直接把第二层的 <= 40 条件推倒第一层，这样往外传的时候就只传40条，而不是所有数据。这样效率就高了；如果把 <= 40 放在第三层，oracle优化不能把 <= 40 的查询条件传递到最内层，往外传递的是所有数据，效率低。
• 为什么不把 <= 40放在最内层？
  放到最内层，那么 count(*) over()代表的总条数就显示40了，而我们分页时候是要获取总条数的。

2. count(*) over()函数介绍

• over()函数写法over(partition by expr2 order by expr3)，根据expr2对结果进行分区，在各分区内按照expr3进行排序；

• 分区partiton by 与 group by的区别
  group by会将结果集按照指定字段进行聚合，结果集会缩减（就是比select * 要显示的数据少了）
  partition by会对结果集按照指定字段分层排列，结果集不会缩减（跟select * 结果一样，count(*) over 结果作为一列显示）

• 不指定 expr2 默认结果集为一整个分区，就是查询的所有结果了

• over函数不能单独使用，需要与row_number()，rank()和dense_rank，lag()和lead(),sum(),count()等配合使用。

4.引用参考、致谢

链接:
ORACLE分页查询SQL语句(最有效的分页).
Oracle数据库over函数的使用.