Oracle 中分页的最佳实践？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13738181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best practice for pagination in Oracle?
提问by Howie
Problem: I need write stored procedure(s) that will return result set of a single page of rows andthe number of total rows.
问题：我需要编写存储过程（S）将返回行的单个页面的结果集和总的行数。
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.
解决方案 A：我创建了两个存储过程，一个返回单个页面的结果集，另一个返回标量 - 总行数。解释计划说第一个 sproc 的成本为 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 ...
Solution B: I put everything in a single sproc, by adding the same TotalRowsnumber to everyrow 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.
解决方案 B：通过向结果集中的每一行添加相同的TotalRows数字，我将所有内容都放在一个 sproc 中。这个解决方案让人感觉很hackish，但是成本是9，而且只有一个sproc，所以我倾向于使用这个解决方案。
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY D.ID DESC ) RowNum, COUNT(*) OVER () TotalRows,
WHERE RowNum >= from
AND RowNum < to
ORDER BY RowNum;
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 中是否有分页的最佳实践？上述哪种解决方案在实践中最常用？他们中的任何一个被认为是完全错误的吗？请注意，我的数据库现在并将保持相对较小（小于 10GB）。
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.
我正在使用 Oracle 11g 和最新的 ODP.NET 以及 VS2010 SP1 和 Entity Framework 4.4。我需要在 EF 4.4 中工作的最终解决方案。我确信一般来说可能有更好的分页方法，但我需要它们与 EF 一起工作。
回答by Vincent Malgrat
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.
如果您已经在使用分析 ( ROW_NUMBER() OVER ...)，那么在同一分区上添加另一个分析函数将为查询增加可忽略不计的成本。
On the other hand, there are many other ways to do pagination, one of them using rownum:
另一方面，还有许多其他方法可以进行分页，其中一种使用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.
这两种方法都是合适的，但一般来说，如果您想要行数和分页集，那么使用分析会更有效，因为您只查询一次行。
回答by Art
This may help:
这可能有帮助：
SELECT * FROM
( SELECT deptno, ename, sal, ROW_NUMBER() OVER (ORDER BY ename) Row_Num FROM emp)
WHERE Row_Num BETWEEN 5 and 10;
回答by Tajinder
In Oracle 12C you can use limit LIMITand OFFSETfor the pagination.
在 Oracle 12C 中，您可以使用限制LIMIT和OFFSET进行分页。
Example -
Suppose you have Table tabfrom which data needs to be fetched on the basis of DATEdatatype column dtin descending order using pagination.
示例 - 假设您有一个表tab，需要根据DATE数据类型列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;
Explanation:
解释：
page_no=1 page_size=5
page_no=1 page_size=5
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY- Fetch 1st 5 rows only
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY- 仅获取第一 5 行
page_no=2 page_size=5
page_no=2 page_size=5
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY- Fetch next 5 rows
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY- 获取接下来的 5 行
and so on.
等等。
Refrence Pages -
参考页面 -
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#paging
回答by Stefano Giraldi
A clean way to organize your SQL code could be trough WITHstatement.
一种干净的组织 SQL 代码的方法可能是低谷WITH语句。
The reduced versionimplements also total number of resultsand total pages count.
该简化版本工具也结果总数和总页数计数。
For example
例如
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)
This code gives you a paged resultset with two more fields:
此代码为您提供了一个包含另外两个字段的分页结果集：
TOTAL_ROWSwith the total rows of your fullSELECTIONRNthe row number of the record
TOTAL_ROWS与您完整的总行数SELECTIONRN记录的行号
It requires 2 parameter: :page_sizeand :page_numberto slice your SELECTION
它需要 2 个参数：:page_size并:page_number切片SELECTION
Reduced Version
精简版
Selection implements already ROW_NUMBER()field
选择工具已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)
回答by Carlos
Try this:
尝试这个：
select * from ( select * from "table" order by "column" desc ) where ROWNUM > 0 and ROWNUM <= 5;
回答by Carlos
Sorry, this one works with sorting:
对不起，这个适用于排序：
SELECT * FROM (SELECT ROWNUM rnum,a.* FROM (SELECT * FROM "tabla" order by "column" asc) a) WHERE rnum BETWEEN "firstrange" AND "lastrange";
回答by Ram Kiran
I also faced a similar issue. I tried all the above solutions and none gave me a better performance. I have a table with millions of records and I need to display them on screen in pages of 20. I have done the below to solve the issue.
我也遇到了类似的问题。我尝试了上述所有解决方案，但没有一个给我更好的性能。我有一个包含数百万条记录的表格，我需要将它们以 20 页的形式显示在屏幕上。我已执行以下操作来解决该问题。
- Add a new column ROW_NUMBER in the table.
- Make the column as primary key or add a unique index on it.
- Use the population program (in my case, Informatica), to populate the column with rownum.
- Fetch Records from the table using between statement. (SELECT * FROM TABLE WHERE ROW_NUMBER BETWEEN LOWER_RANGE AND UPPER_RANGE).
- 在表中添加一个新列 ROW_NUMBER。
- 将该列作为主键或在其上添加唯一索引。
- 使用填充程序（在我的例子中是 Informatica），用 rownum 填充列。
- 使用 between 语句从表中获取记录。（SELECT * FROM TABLE WHERE ROW_NUMBER BETWEEN LOWER_RANGE 和 UPPER_RANGE）。
This method is effective if we need to do an unconditional pagination fetch on a huge table.
如果我们需要对一个巨大的表进行无条件分页提取，这种方法是有效的。
