甲骨文和放大器;分页 [英] Oracle & Pagination

问题描述

我用99896618记录计数一个Oracle表。

I have a oracle table with record count of 99896618.

我要取小块数据（可以说100条记录），以显示它的网页上，（在网络世界中，我们把它叫做分页）。目前我使用下面的查询来实现这一目标但是用户不满意的表现。

I need to fetch small chunk of data(lets say 100 records) to show it on a web page,(In web world we call it paging). Currently I am using the following query to accomplish that however users are not satisfied with the performance.

SELECT * FROM (select rownum rnum,f.* from  findings f where rownum<90000100 ) 
                    WHERE rnum > 90000000 

目前它正在1分22秒得到的结果。反正有做的更好。我当然打开任何类型的建议，包括修改表结构或类似添加索引。

Currently it is taking 1 min 22 seconds to get the results. Is there anyway to make it better. I am certainly open for any type of suggestions including modifying the table structure or like adding indexes.

（仅供参考，我使用ASP.NET为服务器端的web技术和ADO.NET数据访问层和Silverlight的客户端presentation）

(Just FYI,I am using ASP.NET as server side web technology and ADO.NET as data access layer and silverlight for client side presentation)

推荐答案

您的查询需要报数第一 90M 的记录，以获得下一个 100 ，所以几乎没有改善的余地。

Your query will need to count off the first 90M records to get the next 100, so there is hardly a room for improvement.

我没有看到 ORDER BY 子句中的子查询，但可能你拥有了它。在这种情况下，你可能要在其上创建索引。

I don't see an ORDER BY clause in your subquery, but probably you have it. In this case, you may want to create an index on it.

和一个问题：你的用户真的通过点击 900K 页抱怨演出前

And a question: do your users really click through 900K pages before complaining about performance?

更新：

如果你需要最新的网页，你需要重写你的 ORDER BY 列降序排序：

If you need the latest page, you need to rewrite your ORDER BY column in descending order:

SELECT  *
FROM    (
        SELECT  rownum rnum, f.*
        FROM    findings f
        ORDER BY
                record_ordering_column DESC
        ) 
WHERE   rnum > 900
        AND rownum <= 100

和创建 record_ordering_column

请注意，我混 ROWNUM 从嵌套查询的提高性能。

Note that I mix rownum's from the nested queries to improve performance.

有关详细请参阅本文中我的博客：

See this article in my blog for more detail:

• 甲骨文：ROW_NUMBER VS ROWNUM

这篇关于甲骨文和放大器;分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！