赞
踩
Oracle常用分页语句如下,但是这种分页写法,会随着查询的范围的扩大,而越来越慢。
有好的方法大家可以提出来交流一下。
1、带有排序的分页
- select * from (
- select row_.*, rownum rn from (
- select * from 表名 t where 1=1
- order by #{排序字段}
- ) row_ where rownum <= #{pageEnd}
- ) where rn>= #{pageStart}
2、不带排序的分页
- select * from (
- select t.*,rownum rn from 表名 t where 1=1 and rownum <=#{pageEnd}
- ) where rn >=#{pageStart}
ps:
pageStart、pageEnd为传入参数,mybaits中的xml文件不支持>、<,需要用 > 替换>、< 替换<
此时你是不是有疑问,这种写法不是更好吗?
但是pageStart为大于1的记录时,下面查询结果就为空,原理分析在下面,有兴趣可以继续往下看看
select a.*,rownum from 表名 t a where rownum >=#{pageStart} and rownum <=#{pageEnd};
实验数据
- create table student_sorce(
- id varchar2(32) primary key not null,
- name varchar2(32),
- score number,
- ranking varchar2(32)
- );
- insert all
- into student_sorce values ('1','刘一','90','10')
- into student_sorce values ('2','陈二','98','10')
- into student_sorce values ('3','张三','91','10')
- into student_sorce values ('4','李四','94','10')
- into student_sorce values ('5','王五','93','10')
- into student_sorce values ('6','赵六','92','10')
- into student_sorce values ('7','孙七','97','10')
- into student_sorce values ('8','周八','96','10')
- into student_sorce values ('9','吴九','95','10')
- into student_sorce values ('10','郑十','99','10')
- SELECT 1 FROM DUAL;
在解读下面前,首先思考一下下面几条SQL的结果,结果下面会有:
- select count(1) from student_sorce t where rownum =1;
-
- select count(1) from student_sorce t where rownum =2;
-
- select count(1) from student_sorce t where rownum=trunc(dbms_random.value(1,4));
-
- select count(1) from student_sorce t where rownum>1;
-
- select count(1) from student_sorce t where rownum<2;
(1)什么是rownum
既然说了是伪列,顾名思义是不是实际存在的列,不能通过(表名.rownum)方式去访问
直接访问即可,例如:
select rownum from student_sorce t;
以下是错误的写法:
select t.rownum from student_sorce t;
(2)rownum的产生
首先要知道rownum总是从1开始;
然后规则,按我的理解每当结果集产生一条符合的记录,rownum+1;
这里论证一下上述观点:
select count(1)from student_sorce t where rownum =1;
查询为rownum=1,结果为1
select count(1) from student_sorce t where rownum =2;
到时查询rownum=2时,结果为0
但是下面SQL,实际返回结果是0-n条不等,这又是为什么呢?多执行几次,你会发现基本每次执行结果都不一样
select count(1) from student_sorce t where rownum=trunc(dbms_random.value(1,4));
查看一下执行计划,发现rownum=trunc(dbms_random.value(1,4)),是过滤谓词
trunc(dbms_random.value(1,4))的随机取值结果是1、2、3
数据库逐行扫描student_sorce表,此时rownum=1
扫描到第一条记录时,把rownum=1加在后面,假如trunc(dbms_random.value(1,4)不等于1,舍弃
扫描到第二条记录时,把rownum=1加在后面,假如trunc(dbms_random.value(1,4)等于1,保留,rownum+1,此时rownum=2
扫描到第二条记录时,把rownum=2加在后面,假如trunc(dbms_random.value(1,4)不等于2,舍弃
扫描到第三题记录时,把rownum=2加在后面,假如trunc(dbms_random.value(1,4)等于2,保留,rownum+1,此时rownum=3
...
故查询出来的记录是0-n条不等。
select * from tablet where rownum=trunc(dbms_random.value(1,n));
实际上,查询出来的结果记录数与tablet的总记录数和n的大小有关
若表的总记录数远大于n,则查询结果条数基本就是n-1条;(自己动手验证)
若n远大于表的总记录数,则查询结果基本为空;(自己动手验证)
现在你已经应该知道下面SQL的执行结果了
select count(1) from student_sorce t where rownum>1;
查询结果为0;
select count(1) from student_sorce t where rownum<2;
查询结果为1;
回归正题
到了这里,相信你也已经理解下面的分页语句为什么不能这么写了
select * from 表名 t where 1=1 and rownum >= #{pageStart} and rownum <= #{pageEnd}
所以分页查询是 ,要先查询rownum <= #{pageEnd},再嵌套一层,再加查询 rownum >= #{pageStart}
但是如果第一种写法改成下面的不是更好吗?
- select * from (
- select t.*,rownum rn from 表名 t where 1=1 and rownum <=#{pageEnd}
- order by #{排序字段}
- ) where rn >=#{pageStart}
查询出,按成绩降序排序,前三条数据
我们先查询所有数据,手动排序一下,得到结果如下
然后用上面的SQL写的查询语句如下
- select * from (
- select t.*,rownum rn from student_sorce t where 1=1 and rownum <=3
- order by score
- ) where rn >=1
查询结果如下,明显不对
看下执行计划,执行计划不懂的去度娘/google学习一下
其中执行计划的执行顺序:根据Operation缩进来判断,缩进最多的最先执行(缩进相同时,最上面的最先执行),即最右最上先执行原则;
rownum <=3比排序先执行故结果不正确,所以有order by时,rownum <=3要嵌套一层后再加
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。