Oracle分页语句优化

有张千万级别的日志表，分页拉取数据批处理。

总结使用

select * from (
                select rownum rn,t1.* FROM  (
                        查询语句
                        )  t1 where rownum<=pageSize*pageNum
    ) t2 where t2.rn>(pageSize-1)*pageNum

• 下面分页语句查询耗时4.430s。

SELECT * from(
	select rownum rn,e.* from (select * from IMF_REQ_LOG) e
)
where rn>20000 and rn<=20020

• 优化后语句,查询耗时0.330s。

select * from (
                select rownum rn,t1.* FROM  (
                        select * from IMF_REQ_LOG
                        )  t1 where rownum<=20020
    ) t2 where t2.rn>20000