Pagination on large data sets? – Abort count(*) after a certain time

We use the following pagination technique here:

• get count(*) of given filter
• get first 25 records of given filter

-> render some pagination links on the page

This works pretty well as long as count(*) is reasonable fast. In our case the data size has grown to a point where a non-indexd query (although most stuff is covered by indices) takes more than a minute. So at this point the user waits for a mostly unimportant number (total records match开发者_如何学编程ing filter, number of pages). The first N records are often ready pretty fast.

Therefore I have two questions:

• can I limit the count(*) to a certain number
• or would it be possible to limit it by time? (no count() known after 20ms)

Or just in general: are there some easy ways to avoid that problem? We would like to keep the system as untouched as possible.

Database: Oracle 10g

Update

There are several scenarios

• a) there's an index -> neither count(*) nor the actual select should be a problem
• b) there's no index
  • count(*) is HUGE, and it takes ages to determine it -> rownum would help
  • count(*) is zero or very low, here a time limit would help. Or I could just dont do a count(*) if the result set is already below the page limit.

---

You could use 'where rownum < x' to limit the number of rows to count. And if you need to show to your user that you has more register, you could use x+1 in count just to see if there is more than x registers.