Oracle 分页

1.根据rowid来分 
select * from emp
where rowid in 


(select rid from 
(select rownum rn, rid from
(select rowid rid, empno from emp order by empno desc)
where rownum<10)
where rn>4) 
order by empno desc;
执行时间0.03秒 




2.按分析函数来分 
select * from
(select t.*, row_number() over(order by empno desc) rk from emp t)
where rk<10 and rk>2
执行时间1.01秒 




3.按rownum来分 
 select * from
(select t.*,rownum rn from emp t order by empno) 
where rn<9 and  rn>2; 

执行时间0.1秒 

个人推荐使用第三种 也主要介绍一下第三种

下面最主要介绍第三种：按rownum来分 
1. rownum 分页 
SELECT * FROM emp; 
2. 显示rownum[oracle分配的] 
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e; 
rn相当于Oracle分配的行的ID号 
3.挑选出6—10条记录 
先查出1-10条记录 
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10; 
如果后面加上rownum>=6是不行的， 
4. 然后查出6-10条记录 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 
5. 几个查询变化 
a. 指定查询列，只需要修改最里层的子查询 
只查询雇员的编号和工资 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6; 
b. 排序查询，只需要修改最里层的子查询 
工资排序后查询6-10条数据 
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE 
rn >= 6;