oracle - Oracle 分页的最佳实践？

Question

问题:我需要编写存储过程，它将返回单页行的结果集和总行数。

解决方案 A:我创建两个存储过程，一个返回单个页面的结果集，另一个返回标量 - 总行数。解释计划表示第一个存储过程的成本为 9，第二个存储过程的成本为 3。

SELECT  *
FROM    ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC ) AS RowNum, ...
        ) AS PageResult
WHERE   RowNum >= @from
    AND RowNum < @to
ORDER BY RowNum

SELECT  COUNT(*)
FROM    ...

解决方案 B:我将所有内容放入一个存储过程中，方法是将相同的 TotalRows 数字添加到结果集中的每一行。这个解决方案感觉很hackish，但是成本为9，而且只有一个存储过程，所以我倾向于使用这个解决方案。

SELECT * 
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC  ) RowNum, COUNT(*) OVER () TotalRows,
WHERE RowNum >= from
        AND RowNum < to
ORDER BY RowNum;

Oracle 中是否有分页的最佳实践？上述哪种解决方案在实践中最常用？他们中的任何一个被认为是完全错误的吗？请注意，我的数据库现在和将来都相对较小(小于 10GB)。

我正在使用 Oracle 11g 和最新的 ODP.NET 以及 VS2010 SP1 和 Entity Framework 4.4。我需要在 EF 4.4 中工作的最终解决方案。我确信一般情况下可能有更好的分页方法，但我需要它们与 EF 一起使用。

Answer 1

如果您已经在使用分析 (ROW_NUMBER() OVER ...)，那么在同一分区上添加另一个分析函数将为查询增加的成本可以忽略不计。

另一方面，还有许多其他方法可以进行分页，其中之一是使用 rownum:

SELECT * 
  FROM (SELECT A.*, rownum rn
          FROM (SELECT *
                  FROM your_table
                 ORDER BY col) A
         WHERE rownum <= :Y)
 WHERE rn >= :X

如果您在排序列上有适当的索引，则此方法会更优越。在这种情况下，使用两个查询可能会更有效(一个查询总行数，一个查询结果)。

这两种方法都是合适的，但一般来说，如果您同时需要行数和分页集，那么使用分析会更有效，因为您只需查询行一次。