1、按rowid分页
select * from area where rowid in(select rid from (select rownum rn,rid from(select rowid rid,id fromarea order by id desc ) where rownum<82000) where rn>81980) order by id desc;
2、按分析函数分页
select * from (select t.*,row_number() over(order by id desc) rk from area t) where rk<82000 and rk>81980;
3、按rownum分页
select * from(select t.*,rownum rn from(select * from area order by id desc) t where rownum<82000) where rn>81980;
实际测试,第一种分页在大数据量的时候效率明显高于后两者;
还有关于rownum 的注意事项:oracle的rownum用法注意:(以下用法是没有意义的) rownum永远是从1开始开始计算,不能从2开始
select * from area where rownum between 2 and 10;具体关于这个的介绍参考:http://www.cnblogs.com/zjrstar/archive/2006/08/31/491090.html