用Oracle分页

Question

用Oracle分页

我对甲骨文不太熟悉。我有大约250 K的记录，我想每页显示100张。目前，我有一个存储过程，它使用数据适配器将所有25万条记录检索到DataSet，还有DataSet和DataAdapter.Fill(DataSet)方法。如果我有“页面编号”和“每页记录数”作为整数值，我可以作为参数传递，那么返回该特定部分的最佳方法是什么。比方说，如果我从SELECT语句中传递10作为页码，120作为页数，它会给我1880到1200，或者诸如此类的东西，我脑子里的数学可能会消失。

我正在用C#在.NET中这样做，认为这并不重要，如果我能在SQL方面正确地实现它，那么我应该很酷。

更新：我能够使用Brian的建议，而且效果很好。我想进行一些优化，但是页面将在4到5秒内出现，而不是一分钟，而且我的分页控件能够很好地集成到我的新存储过程中。

Answer 1

像这样的事情应该有效：来自Frans Bouma的博客

SELECT * FROM(
    SELECT a.*, rownum r__    FROM
    (
        SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
        ORDER BY OrderDate DESC, ShippingDate DESC
    ) a    WHERE rownum < ((pageNumber * pageSize) + 1 ))WHERE r__ >= (((pageNumber-1) * pageSize) + 1)

Answer 2

关于分页和非常有用的解析函数。

这是该页的摘录：

select * from (
    select /*+ first_rows(25) */
     object_id,object_name,
     row_number() over
    (order by object_id) rn        from all_objects)
    where rn between :n and :m        order by rn;

Answer 3

为了完整起见，对于那些寻求更现代解决方案的人来说，Oracle 12c还有一些新特性，包括更好的分页和顶级处理。

寻呼

分页如下所示：

SELECT *FROM userORDER BY first_name
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

顶级N记录

获得最高记录如下：

SELECT *FROM userORDER BY first_nameFETCH FIRST 5 ROWS ONLY

请注意上述两个查询示例是如何ORDER BY条款。新命令尊重这些命令，并在已排序的数据上运行。

我找不到一个好的Oracle参考页面FETCH或OFFSET但本页对这些新特性有一个很好的概述。

性能

正如@wweicker在下面的注释中所指出的，性能是12c中新语法的一个问题。我没有一个18c的副本来测试甲骨文是否改进了它。

有趣的是，当我第一次在表(1.13亿多行)上为新方法运行查询时，返回实际结果的速度稍微快了一点：

• 新方法：0.013秒。
• 老方法：0.107秒。

然而，正如@wweicker所提到的，对新方法来说，解释计划看起来要糟糕得多：

• 新方法费用：300 110
• 旧方法费用：30

新语法导致对我列上的索引进行了全面扫描，这是整个成本。很可能，当限制未编入索引的数据时，情况会变得更糟。

当在上一个数据集中包含一个未索引列时，让我们看一看：

• 新方法时间/成本：189.55秒/998，908
• 旧方法时间/成本：1.973秒/256个

摘要：小心使用，直到Oracle改进了此处理。如果您有一个要使用的索引，也许您可以使用新的方法。

希望我很快就能有一份18c的拷贝，并能更新。