Oracle分页的最佳实践? [英] Best practice for pagination in Oracle?

问题描述

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

Problem: I need write stored procedure(s) that will return result set of a single page of rows and the number of total rows.

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

Solution A: I create two stored procedures, one that returns a results set of a single page and another that returns a scalar -- total rows. The Explain Plan says the first sproc has a cost of 9 and the second has a cost of 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编号添加到结果集中的每行中，我将所有内容放入单个sproc中.该解决方案让人觉得有些棘手，但它的成本为9，并且只有一个存储过程，因此我倾向于使用此解决方案.

Solution B: I put everything in a single sproc, by adding the same TotalRows number to every row in the result set. This solution feel hackish, but has a cost of 9 and only one sproc, so I'm inclined to use this solution.

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).

Is there a best-practice for pagination in Oracle? Which of the aforementioned solutions is most used in practice? Is any of them considered just plain wrong? Note that my DB is and will stay relatively small (less than 10GB).

我正在将Oracle 11g和最新的ODP.NET与VS2010 SP1和Entity Framework 4.4一起使用.我需要最终解决方案才能在EF 4.4中使用.我敢肯定，总体上来说，可能存在更好的分页方法，但是我需要它们与EF一起使用.

I'm using Oracle 11g and the latest ODP.NET with VS2010 SP1 and Entity Framework 4.4. I need the final solution to work within the EF 4.4. I'm sure there are probably better methods out there for pagination in general, but I need them working with EF.

推荐答案

如果您已经在使用Analytics(分析)(ROW_NUMBER() OVER ...)，则在同一分区上添加另一个分析函数将为查询带来可忽略的成本.

If you're already using analytics (ROW_NUMBER() OVER ...) then adding another analytic function on the same partitioning will add a negligible cost to the query.

另一方面，还有许多其他分页方法，其中一种使用rownum:

On the other hand, there are many other ways to do pagination, one of them using rownum:

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

如果在订购列上有适当的索引，则此方法会更好.在这种情况下，使用两个查询(一个用于总行数，一个用于结果)可能会更有效.

This method will be superior if you have an appropriate index on the ordering column. In this case, it might be more efficient to use two queries (one for the total number of rows, one for the result).

这两种方法都是合适的，但是通常，如果您想要行数和分页集，那么使用分析会更有效，因为您只查询行一次.

Both methods are appropriate but in general if you want both the number of rows and a pagination set then using analytics is more efficient because you only query the rows once.

这篇关于Oracle分页的最佳实践?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！