What is an Effective Way to Count the Number of Pages in Oracle?

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  [[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?

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.

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;

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):


You need to order by something unique for these pagination queries, so
that ROW_NUMBER is assigned deterministically to the rows each and
every time.

StackExchange Database Administrators Q#6058, answer score: 3