We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results.
We implement paging by putting the original query inside a SELECT that fetches only certain rownums from given query, like this:
SELECT * FROM (
SELECT ROWNUM rnum, d.* FROM (
' ||
pSql ||
'
[[filter]]
[[sort]]
) d
WHERE rownum < [[end]])
WHERE rnum> [[start]]
The only way to implement page counting I could think of was to do a count of all records in pSql. This, however, turned out to be very resource heavy and would needed to be done quite often, as we ofer filtering of data, which in return creates a whole new query in pSql.
Is there a more efficient way of doing a record count?
Best Answer
If the typical use pattern is to page through all the data, then all the records will eventually need to be sent to the client. In this scenario re-querying the database for every page is in-efficient. You should consider running the query once and paging through a cache on the client side.
If on the other hand the typical use pattern is to page through a very small amount of the data, then this method may be appropriate. Nick Chammas' comment on using ROW_NUMBER() is fleshed out by Tom Kyte in a Oracle Magazine article.
I recommend you read the whole article. Note in particular the warning to sort by something unique so that the results are consistent between pages (given static data):