oracle入门之分页查询

oracle的分页查询共三种方法

1、根据ROWID来分页(速率一般)

SQL>select * from emp where rowid in (select rid from (select rownum rn,rid from (select rowid rid,cid from emp order by cid desc) where rownum<10000) where rn>9980) order by cid desc;

看得更清楚点

select * from emp where rowid in

(select rid from

(select rownum rn,rid from

(select rowid rid,cid from emp order by cid desc)

where rownum<10000)

where rn>9980)

order by cid desc;

 

2、按分析函数来分页(速率慢)

SQL>select * from (select t.*,row_number() over(order by cid desc) rk from emp t) where rk<10000 and rk>9980;

 

3、按rownum来分页(推荐使用速率稳定且速率快)

SQL>select t2.* from (select t1.*,rownum rn from (select * from emp) t1 where rn<=10000) t2 where rn>=9980;

看得更清楚一点

select t2.* from

(select t1.*,rownum rn from

(select * from emp)

t1 where rn<=10000)

t2 where rn>=9980;