Use ROWNUM for Pagination!!!

Use of ROWNUM in the queries can be very useful to improve query performance for pagination.


select * from

( select rownum rnum, a.* from (your_query) a where rownum <= :M )

where rnum >= :N;

select * from (

select /*+ first_rows(25) */ your_columns, row_number() over (order by something unique) rn from your_tables )

where rn between :n and :m

order by rn;