oracle3种分页总结

• 1.通过rowid来分页

select e.*
  from emp e
 where rowid in
       (select rid
          from (select rownum rn, rid
                  from (select rowid rid, e.sal from emp e order by sal)
                 where rownum < 14)
         where rn > 4)
 order by sal;

• 2.按rownum来分

select * from (select t.*, rownum rn
                  from (select * from emp order by sal) t
                 where rownum < 14)
  where rn > 4;

• 3. 按分析函数来分

select *
   from (select e.*, row_number() over(order by sal) ro from emp e)
  where ro < 14
    and ro > 4;

一般如果存在模糊查询 ，建议放到第一层sql里面

select *
   from (select e.*, row_number() over(order by sal) ro 
               from emp e  where  e.xxx LIKE ‘%'  +  xxxxx + ‘%’)
  where ro < 14
    and ro > 4;

如果数据量足够大的话，通过测试很明显的可以比较出来使用rowid来分页效率要快，rownum次之，


计算分页的总数

select count(msisdn)
  from (select *
          from (select t.*, rownum rn
                  from (select msisdn from dm_iread.dm_user_pack_relation) t
                 where rownum <= 1)
         where rn > 0
        )