Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results.

We build 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?

share|improve this question
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
    
Thanks @Nick, I'll take a look. –  eagerMoose Sep 26 '11 at 21:14

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.