oracle分页SQL（深入分析）

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文件不支持>、<，需要用 &gt; 替换>、&lt; 替换<

此时你是不是有疑问，这种写法不是更好吗？

但是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;

上述分页查询用到伪列rownum，所以首先要理解伪列rownum

（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要嵌套一层后再加