oracle高性能分页,Oracle三种分页模式及效率对比

insert into student select * from student;

select ROWNUM,t.* from student t where rownum <5;

select count(*) from student;--786432条，

--查 78000～78030 速度由快到慢：1-2-3-4

--1.按rownum 来分

select * --0.094S

from (select rownum as rn , st.*

from (select * from student) st

where rownum <78030) t

where rn > 78000;

--2.按rownum 来分

select * --0.343S

from (select rownum as rn, st.* from (select * from student) st)

where rn between 78000 and 78030;

--3 按rowid来分

select * --0.359S

from student

where rowid in

(select rid

from (select rownum rn, rid

from (select rowid rid, classid from student order by classid)

where rownum < 78030)

where rn > 78000)

order by classid;

--4 按分析函数来分

select * -- 0.624S

from (select t.*, row_number() over(order by classid) rk from student t)

where rk < 78030

and rk > 78000;