Recently, i was tasked with implementing pagination for a data-grid in my application on the server-side. The pagination which i choose, is enabled at the database end by using SQL. After a bit of google reasearch, i stumbled upon the following query which seems to fit the bill.
select * from ( select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from ( your_query_goes_here, with order by ) a where ROWNUM <= :MAX_ROW_TO_FETCH ) where rnum >= :MIN_ROW_TO_FETCH;
FIRST_ROWS(N) tells the optimizer, “Hey, I’m interested in getting the first rows, and I’ll get N of them as fast as possible.”
:MAX_ROW_TO_FETCH is set to the last row of the result set to fetchif you wanted rows 50 to 60 of the result set, you would set this to 60.
:MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.
Courtesy : Ask Tom, Q & A