大数据集的分页？ –在一定时间后中止计数(*)
我们在这里使用以下分页技术：
- get
count(*)of给定过滤器 - 获取给定过滤器的前 25 条记录
->在页面上呈现一些分页链接
只要 count(*) 速度相当快，这种方法就可以很好地工作。在我们的例子中，数据大小已经增长到非索引查询（尽管大多数内容都被索引覆盖）需要超过一分钟的程度。因此，此时用户等待一个几乎不重要的数字（匹配过滤器的总记录数、页数）。前 N 条记录通常很快就准备好了。
因此我有两个问题：
- 我可以将
count(*)限制为某个数字 - 还是可以按时间限制它？ （20 毫秒后没有
count()已知）
或者只是一般情况：是否有一些简单的方法可以避免该问题？我们希望尽可能保持系统不受影响。
数据库：Oracle 10g
更新
有几种情况
- a) 有索引 ->
count(*)和实际的选择都不应该成为问题 - b) 没有索引
count(*)非常大，需要很长时间才能确定 -> rownum 会有所帮助count(*)为零或非常低，这里时间限制会有所帮助。或者，如果结果集已经低于页面限制，我可以不执行count(*)。
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 matching 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 helpcount(*)is zero or very low, here a time limit would help. Or I could just dont do acount(*)if the result set is already below the page limit.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以使用 'where rownum < x' 限制要计数的行数。如果您需要向用户显示您有更多寄存器，您可以在计数中使用 x+1 来查看是否有超过 x 个寄存器。
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.