Best practice for pagination in Oracle?
If you're already using analytics (ROW_NUMBER() OVER ...
) then adding another analytic function on the same partitioning will add a negligible cost to the query.
On the other hand, there are many other ways to do pagination, one of them using rownum
:
SELECT * FROM (SELECT A.*, rownum rn FROM (SELECT * FROM your_table ORDER BY col) A WHERE rownum <= :Y) WHERE rn >= :X
This method will be superior if you have an appropriate index on the ordering column. In this case, it might be more efficient to use two queries (one for the total number of rows, one for the result).
Both methods are appropriate but in general if you want both the number of rows and a pagination set then using analytics is more efficient because you only query the rows once.
This may help:
SELECT * FROM ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp) WHERE Row_Num BETWEEN 5 and 10;
In Oracle 12C you can use limit LIMIT
and OFFSET
for the pagination.
Example - Suppose you have Table tab
from which data needs to be fetched on the basis of DATE
datatype column dt
in descending order using pagination.
page_size:=5select * from taborder by dt descOFFSET nvl(page_no-1,1)*page_size ROWS FETCH NEXT page_size ROWS ONLY;
Explanation:
page_no=1page_size=5
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY
- Fetch 1st 5 rows only
page_no=2page_size=5
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY
- Fetch next 5 rows
and so on.
Refrence Pages -
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging