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;