oracle分页查询及原理分析(总结)
oracle分页查询及原理分析(总结)
oracle分页查询是开发总为常用的语句之一,一般情况下公司框架会提供只需套用,对于增删改查而言,查是其中最为关键也是最为难的一块,其中就有使用率最高的分页查询。本人菜鸟,不足之处还望各位大神多多指导,谢谢各位看官!!!
假定我们有一个sys_user用户表,具体如下:
字段 | user_Id | login_name | password | org_id | create_time |
注释 | 用户主键 | 登陆名 | 密码 | 单位id | 创建时间 |
1 2 | --srcSql,最底层sql select * from SYS_USER t where t.org_id= '402881e54c40d74d014c40d8407a0016' order by t.create_time desc ; |
1、使用between ... and ... (不建议,数据量大情况下速度慢)
如果使用between ... and ... ,则对最底层进行再次封装成带行号的数据结果集,即下面sql语句,sqlA是最底层的sql。
1 2 | --sql1.1 select rownum num, tmp.* from (srcSql) tmp |
因此得到了带行号的一个结果集,对此结果集进行区间帅选。
1 | select * from (sql1.1) where num between 4 and 5 |
全部的sql展示为:
1 2 3 4 5 | select * from ( select rownum num, tmp.* from ( select * from SYS_USER t where t.org_id = '402881e54c40d74d014c40d8407a0016' order by t.create_time desc ) tmp) where num between 4 and 5 |
2、熟悉行号生成机制,多层嵌套分页查询
先放代码后说原因,不足之处还望多指导指导!!!
1 2 | --sql2.1 ,,,因行号rownum只能<=,<,!=,<>不能>=,>,=(=1是可以得),因此对所有的结果集先进行上限界定 select rownum num,tmp.* from (srcSql) tmp where rownum<=5 |
对sql2.1查询的结果集作为一张表再进行下限界定,这样就可以更快的逐步缩小范围,范围小了,数据量少了,查询当然会更快!!!
1 | select * from (sql2.1) where num>2; |
总共为三层,不知道你是否看明白???,全部的sql展示为:
1 2 3 4 5 | select * from ( select rownum num, tmp.* from ( select * from SYS_USER t where t.org_id = '402881e54c40d74d014c40d8407a0016' order by t.create_time desc ) tmp where rownum <= 5) where num > 2; |
对比两种方法
第一种方法是查出全局结果集,然后在全局结果集中在进行范围缩小,而第二种方法是一步一步的进行范围缩小,直到得到需求的结果集。。。不难看出第二种方法更为合适,在数据量大的情况下速度更为快一点。。。
原理分析
在两种方法中我们都用到了行号rownum,对于oracle的行号而言,这是一个动态的变量, 如果你去运行下面这个sql语句,你会发现无结果。为什么?
1 | select * from SYS_USER t where t.org_id= '402881e54c40d74d014c40d8407a0016' and rownum>= '3' and rownum<= '5' order by t.create_time desc ; |
rownum是oracle中的一个伪列(pseudo column),其目的就是给查询出来的结果集标注行号。可以实现查询前n行(top-n),中间几行(middle-n),最后几行(bottom-n)的功能。但是rownum又是一个很特殊的列,使用过程中,首先要弄清楚oracle rownum的原理,然后加以分析。
1.rownum不可以直接在前面加上表名或别名等。其他伪列如level,rowid等一样。
2.rownum和where在同一层查询中,where条件之后使用rownum比较,只能使用<=,<,!=,<>,不能使用>,>=(>=1,>=0,>0和不加效果一样),=(使用=,只能是where rownum=1才可以,rownum>1不可以)。否则不返回任何数据。如果使用!=或<>,那么只是返回前n-1行,其他按照rownum工作原理推算。
3.当rownum和order by在一个语句级别中(同一层)使用的时候.看这个查询的数据是否从索引中获取(或者根据索引先得到rowid然后定位行)的,并且获取的顺序和order by一致(注意索引可以自己desc获取的),如果不是,那么就是先查询出来,每行标上rownum,然后order by将结果重新排序,那么rownum的顺序是乱的,并且不是按照排序后获取TOP-N结果的,而是先查询的TOP-N,再排序
java封装sql分页查询
最底层sql,即本篇中的srcSql,先根据需求自己写出全部满足条件的结果集。
我是一个j搞java的,下面是我简单的封装,基本满足大多数分页查询需要。
1 2 3 4 5 6 7 8 9 10 | /** **srcSql 原sql **pageNo 页码 **pageSize 每页数量 **/ private String getPageQuerySql(String srcSql, int pageNo, int pageSize){ return "select * from (select tmp.*,rownum num from (" , srcSql, ") tmp where rownum <= " + pageNo*pageSize + ") where num >" +(page- 1 )*pageSize; } |
开心一刻
某大厦楼顶。
女:“我们分手吧。”
男:“为什么?我那么爱你?”
女:“我爸知道我们的事之后一直反对,你又不是不知道,现在都不让我回家了!”
男:“我相信我们的爱可以感动你爸的,你是知道的,我是真心爱你的!”
女:“我知道!但是我们真的不能在一起,我们的爱不会得到祝福的,就算我老爸同意,等我老公 回来,他一定会跟你拼了的。”
男:“我儿子什么脾气我会不知道?他没那个胆子!”
哎,这一家人!
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 单线程的Redis速度为什么快?
· SQL Server 2025 AI相关能力初探
· 展开说说关于C#中ORM框架的用法!
· AI编程工具终极对决:字节Trae VS Cursor,谁才是开发者新宠?