分页实现方式

----------------分页实现方式---------------------
方法一：直接通过rownum分页：
select * from (
select a.*,rownum rn from
(select * from product a where company_id=? order by status) a
where rownum<=20)
where rn>10;
数据访问开销=索引IO+索引全部记录结果对应的表数据IO

方法二：
select * from
(select pfc.*,row_number() over(partition by ruleid order by posttime desc) cn from pfscr650535 pfc where postfloor=0) t
where t.cn<11
该语句优化如下：
select b.* from
(select ruleid,row_number() over(partition by ruleid order by posttime desc) cn from pfscr650535 pfc ) a,pfscr650535 b
where a.cn<11 and a.ruleid=b.ruleid and b.postfloor=0;

方法三：采用rowid分页语法
优化原理是通过纯索引找出分页记录的ROWID，再通过ROWID回表返回数据，要求内层查询和排序字段全在索引里。
create index myindex on product(company_id,status);

select b.* from (
select * from (
select a.*,rownum rn from
(select rowid rid,status from product a where company_id=? order by status) a
where rownum<=20)
where rn>10) a, product b
where a.rid=b.rowid;
数据访问开销=索引IO+索引分页结果对应的表数据IO