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()等配合使用。