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 ROWNUM rnum, d.* FROM (
      ' ||
      pSql ||
      ) 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?

  • 1
    If the count is expensive already have you ever tried to retrieve the last pages? The paging alone will probably be extremely slow when you've got many rows. Do you really need to page that many results? Also be aware that you need to provide a unique order (PK will be fine) or else Oracle will treat some pages to be the same, that is, you will see the same results on page 56, 57 and 58 if you're unlucky. If you need to support that large datavolumes, try to optimize it in a way that keeps the records in a cache or in memory if possible. – Falcon Sep 22 '11 at 15:37
  • These approaches are SQL Serer specific, but the techniques should also be applicable to Oracle. Do any of them solve your problem? – Nick Chammas Sep 26 '11 at 20:34
  • 1
    For starters, use ROW_NUMBER() instead of ROWNUM and make sure that the columns you are ordering by are indexed appropriately. – Nick Chammas Sep 26 '11 at 20:36

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 /*+ first_rows(25) */
      Row_Number() Over (Order By something unique) rn
   FROM your_tables 

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.


Just like Leigh mentioned in another answer. Considering you case in terms of user behaviour and load is critical. Having said that, there are two options to do this. One is rownum and the more recent is the row_number analytic function. row_number can do more things but in this particular case there is no little to no difference in how the code would look.

select * from (
    select rownum rn, last_name, first_name from clients
    where rownum<=100100 Order By upper(last_name),upper(first_name))
where rn>=100000 order by rn


select * from (
   select /*+ first_rows(25) */
      last_name, first_name,
      row_number() over (order by upper(last_name),upper(first_name)) rn
   from clients_and_facilitators  
where rn between 100000 and 100100 order by rn

The first one took 269 msecs and the second 4000 msecs. Notice how I am used big numbers in the number of rows. If I take it down close to 1000-1100 then, the first one takes 40 msecs and the second still takes 4000 msecs.

These results appear to make some sense. The first query only sorts records until it hits the maximum row. Then the database then it just filters out the beginning rows. The second query is computing the row_number for every record before it filters them out. Which makes also makes sense since row_number can do a whole lot more, and may be harder to optimize. Even more so when the simpler rownum method is more optimizable way is already available.

Analyze table gives the two different evaluation methods. For the first query count stopkey is used and for the second window sort pushed rank is used.

Your Answer

