[Oracle]分页查询-Oracle

有限结果集

在Oracle 中， 使用好 ROWNUM 这个栏位， 将会非常有用。

一般来说可以用它来做两件事情：

1. 执行top-N的查询。

这和其他数据库的 LIMIT 语法的作用类似。

（查找前N笔数据）

2. 执行分页查询， 特别是在Web 这样的无状态的环境。

ROWNUM 是如何工作的

ROWNUM 是一个在查询中的虚列（不是实际的列）。

ROWNUM 的值是类似于： 1,,2,,3,4,...,N。

ROWNUM 的值不是永久的分配给某行。

所用不能使用 ROWNUM=5 这样的方式来找到某行。

还有一个重要的概念是ROWNUM什么时间被赋值。

ROWNUM 在某个Query语句通过查询谓词阶段之后但是在查询做任何排序和汇总之前。

同样，ROWNUM的值只有在分配之后才能增长。

像以下这样的语句就得不到数据。

select * 
  from t 
 where ROWNUM > 1;

原因是: ROWNUM > 1对于第一行来说是不对的，ROWNUM不能提前到2. 考虑使用以下方式：

select ..., ROWNUM
  from t
 where <where clause>
 group by <columns>
having <having clause>
 order by <columns>;

可以看成是以下顺序进行处理：

1. FROM/WHERE 子句先行

2. ROWNUM 是从FROM/WHERE子句中分配和递增给每一个输出行。

3. SELECT 被应用

4. GROUP BY 被应用

5. HAVING 被应用

6. ORDER BY 被应用

这就是为什么以下方式进行Query 来处理ROWNUM 是会有问题的了：

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

这个语句会随意的找五笔数据，而不是薪资最高的五笔数据。

所以， 正确的做法应该是：

先排序， 然后再使用 rownum. 可以这样 

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

查询前 n 条数据

对与一张数据量很大（百万级甚至更多） 且每个记录的内容都比较大的表来说， 获取前 10条或是100条数据的方式可以是：

1. Query 出所有数据， 取前 n 条 order by

2. 使用内部表的方式， 结合ROWNUM, 类似

SELECT * FROM ( your_query_here ) WHERE ROWNUM <= N.

除了从DB 中取的比较少的数据外，这两种方式在性能上差异很大， 差在什么地方呢？

先来看一下第一种方式的执行过程

1. 全表扫描

2. 对所有数据根据字段排序

3. 如果排序的内存不够的话，从硬盘扩展

4. 结合扩展的临时内存取出前 n 条数据

5. 释放临时内存。

而第二种方式的过程就要简单得多

1. 全表扫描

2. 排序区先取n 条数据， n 条之后的数据每笔和这n 条进行比对， 满足条件的话就替换。

也就是说， 排序区始终只有n 条数据。

也就不会从硬盘扩展。

笔者在实际开发的过程中， 在 2000万笔数据的状况下，

不使用ROWNUM 简直就无法使用了。

使用ROWNUM 的状况系统响应时间在 10s 内。

看例子

建立一个 EMPLOYEE 的表， 并插入一些数据 

CREATE TABLE EMPLOYEE(
  EMPID varchar2(10),
  EMPNAME varchar2(10),
  SALARY varchar2(60)
);

insert into EMPLOYEE values('001','zhao','7300');
insert into EMPLOYEE values('002','qian','7400');
insert into EMPLOYEE values('003','sun','7500');
insert into EMPLOYEE values('004','li','7200');
insert into EMPLOYEE values('005','zhou','7100');
insert into EMPLOYEE values('006','wu','7000');
insert into EMPLOYEE values('007','zheng','6500');
insert into EMPLOYEE values('008','wang','6000');
insert into EMPLOYEE values('009','feng','6100');
insert into EMPLOYEE values('010','chen','6200');
insert into EMPLOYEE values('011','zhu','6300');
insert into EMPLOYEE values('012','wei','6400');
insert into EMPLOYEE values('013','jiang','6700');
insert into EMPLOYEE values('014','shen','6600');
insert into EMPLOYEE values('015','han','6800');
insert into EMPLOYEE values('016','yang','6900');

1. 现在想查看薪资最高的五笔数据。

1）使用 ：

select * from EMPLOYEE where ROWNUM <= 5 order by SALARY desc;

得到 ：

2） select * from EMPLOYEE order by SALARY desc;

结果完全不相同。

验证了以上部分的描述。

正确的写法可以是：

select * from(
select * from EMPLOYEE  order by SALARY desc) where ROWNUM <= 5 ;