Oracle中分页的最佳实践是什么？

Question

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

解决方案A：我创建了两个存储过程，一个返回单个页面的结果集，另一个返回标量--总行数。Explain Plan 显示第一个存储过程的成本为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一起工作。

- Howie

---

EF中的分页是与数据库无关的。 - Robert Harvey

是的，但我只是想明确一下，我不想使用ODP.NET或ADO.NET特定的代码，而是更倾向于保持在高级别上。 - Howie

Tom Kyte写了一篇关于使用rownum限制结果集的文章： http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html 它可能会回答你的一些问题。 - Rene

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

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

两种方法都是适当的，但通常情况下，如果您需要行数和分页集，则使用分析更有效，因为您只查询一次行。

- Vincent Malgrat

---

请参阅Tom Kyte在Oracle Magazine（2006年9月/10月）中的“关于ROWNUM和结果限制”的文章。 - Ludovic Kuty

我在第二行遇到了语法问题，似乎不喜欢*后面跟着逗号的语法。你需要使用A.*代替，然后在冒号后面加上A。 - JGFMK

为什么您在两个查询中都过滤了rownum，而不是一个查询中使用“WHERE rownum <= :Y AND rownum >= :X”？这样做会导致性能下降吗？ - Iúri dos Anjos

@IúridosAnjos：由Ludovic Kuty提供的Tom Kyte文章解释了为什么它不起作用：“rownum >= 2”始终为FALSE。 - Vincent Malgrat

@IúridosAnjos: 内部的 SELECT 用于排序，不能包含 ROWNUM，因为 ROWNUM 是在排序之前计算的。这里有一个使用 dbfiddle 的例子。当然，在 Oracle Magazine 文章 中也有解释，我再次鼓励你阅读 :) - Vincent Malgrat

Answer 2

在Oracle 12C中，你可以使用LIMIT和OFFSET来进行分页。

例如 - 假设你有一个表tab，需要按照日期数据类型列dt的降序来使用分页方式获取数据。

page_size:=5

select * from tab
order by dt desc
OFFSET nvl(page_no-1,1)*page_size ROWS FETCH NEXT page_size ROWS ONLY;

说明：

page_no=1 page_size=5

OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY - 只获取前5行数据

page_no=2 page_size=5

OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY - 获取接下来的5行数据

以此类推。

参考页面 -

https://dba-presents.com/index.php/databases/oracle/31-new-pagination-method-in-oracle-12c-offset-fetch

https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging

- Tajinder

---

遗憾的是，缺少COUNT(*)作为某种变量或具有给定条件下总记录数的内置列。我理解这可能会耗费时间，但开发人员仍然会这样做，以确定我们总共有多少页。 - zion

Answer 3

这可能会有所帮助：

   SELECT * FROM 
     ( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp)
     WHERE Row_Num BETWEEN 5 and 10;

- Art

---

这正是 OP 在问题中提出的内容……你需要解释为什么它会有帮助，因为 OP 正在寻找最高效的解决方案。为什么它比使用 rownum 更好，适用于什么情况？你有任何基准测试吗？ - Ben

好的，这是我理解问题的方式。希望我理解得对。在Oracle中，Rownum和Row_Number()之间的区别在于前者是伪列，而后者是分析函数。你可以同时使用两者。但是，Rownum并不总是与Order By一起使用。尝试在Rownum中添加Order by，你会看到差异。使用分析函数是最好的、最新的，并且通常可以提高性能。您可以在文档中了解更多关于Rownum和Row_Number()的信息。 谢谢。 - Art

是的，你说得对。当与ORDER BY一起使用时，rownum谓词将返回一个随机值，除非排序在子选择中完成。然而，我的观点是你的回答并没有回答问题。分析函数虽然“最近”，但在这种情况下，它们不一定比rownum更有效，因为它们需要对表进行额外的扫描，而rownum则不需要（正如Vincent的答案所解释的那样）。 - Ben

Answer 4

一个整洁的组织SQL代码的方法可以通过使用WITH语句实现。

简化版本还实现了结果总数和总页数。

例如：

WITH SELECTION AS (
    SELECT FIELDA, FIELDB, FIELDC FROM TABLE), 
NUMBERED AS (
    SELECT 
    ROW_NUMBER() OVER (ORDER BY FIELDA) RN, 
    SELECTION.*
    FROM SELECTION)
SELECT
    (SELECT COUNT(*) FROM NUMBERED) TOTAL_ROWS,
    NUMBERED.*
FROM NUMBERED
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)

这段代码提供了一个带有两个额外字段的分页结果集：

• TOTAL_ROWS，包含完整 SELECTION 的总行数
• RN，记录的行号

它需要两个参数：:page_size 和 :page_number，用于切片你的SELECTION。

简化版本

Selection 已经实现了 ROW_NUMBER() 字段。

WITH SELECTION AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY FIELDA) RN,
        FIELDA, 
        FIELDB, 
        FIELDC 
    FROM TABLE) 
SELECT
    :page_number PAGE_NUMBER,
    CEIL((SELECT COUNT(*) FROM SELECTION ) / :page_size) TOTAL_PAGES,
    :page_size PAGE_SIZE,
    (SELECT COUNT(*) FROM SELECTION ) TOTAL_ROWS,
    SELECTION.*
FROM SELECTION 
WHERE 
    RN BETWEEN ((:page_size*:page_number)-:page_size+1) AND (:page_size*:page_number)

- Stefano Giraldi

Answer 5

试试这个：

select * from ( select * from "table" order by "column" desc ) where ROWNUM > 0 and ROWNUM <= 5;

- Carlos

---

好的答案应该附带代码示例，并为将来的读者解释。尽管提问者可能理解您的答案，但解释如何得出答案将有助于其他无数人。 - Stonz2

嗯...如果我将 rownum > 0 改为 rownum > 1 ，它就不起作用了。我现在不知道为什么... - Pavel Biryukov

Answer 6

抱歉，这个需要排序才能正常工作：

SELECT * FROM (SELECT ROWNUM rnum,a.* FROM (SELECT * FROM "tabla" order by "column" asc) a) WHERE rnum BETWEEN "firstrange" AND "lastrange";

- Carlos

---

你好，欢迎来到StackOverflow。请注意，如果您有改进意见，不要创建新的答案，而应该编辑您现有的答案。谢谢！ - Fabian Fagerholm

Answer 7

我也遇到了类似的问题。我尝试了上面所有的解决方案，但都没有给我更好的性能。我有一个包含数百万条记录的表格，需要在屏幕上以每页20条的方式显示它们。我已经采取以下措施来解决这个问题。

1. 在表格中添加一个新列ROW_NUMBER。
2. 将该列设置为主键或在其上添加唯一索引。
3. 使用数据集成程序（在我的情况下是Informatica）来填充该列的rownum。
4. 使用between语句从表格中获取记录。（SELECT * FROM TABLE WHERE ROW_NUMBER BETWEEN LOWER_RANGE AND UPPER_RANGE）。

如果我们需要对一个巨大的表格进行无条件分页获取，那么这种方法是有效的。

- Ram Kiran