SQL 使用 Oracle 进行分页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/241622/
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
Paging with Oracle
提问by stephenbayer
I am not as familiar with Oracle as I would like to be. I have some 250k records, and I want to display them 100 per page. Currently I have one stored procedure which retrieves all quarter of a million records to a dataset using a data adapter, and dataset, and the dataadapter.Fill(dataset) method on the results from the stored proc. If I have "Page Number" and "Number of records per page" as integer values I can pass as parameters, what would be the best way to get back just that particular section. Say, if I pass 10 as a page number, and 120 as number of pages, from the select statement it would give me the 1880th through 1200th, or something like that, my math in my head might be off.
我对 Oracle 并不像我希望的那样熟悉。我有大约 25 万条记录，我想每页显示 100 条记录。目前我有一个存储过程，它使用数据适配器、数据集和 dataadapter.Fill(dataset) 方法将一百万条记录中的所有四分之一检索到数据集。如果我将“页码”和“每页记录数”作为整数值，我可以作为参数传递，那么返回该特定部分的最佳方法是什么。比如说，如果我通过 10 作为页码，120 作为页数，从 select 语句中它会给我第 1880 到 1200，或者类似的东西，我脑子里的数学可能会出错。
I'm doing this in .NET with C#, thought that's not important, if I can get it right on the sql side, then I should be cool.
我正在使用 C# 在 .NET 中执行此操作，认为这并不重要，如果我可以在 sql 方面正确处理，那么我应该很酷。
Update: I was able to use Brian's suggestion, and it is working great. I'd like to work on some optimization, but the pages are coming up in 4 to 5 seconds rather than a minute, and my paging control was able to integrate in very well with my new stored procs.
更新：我能够使用布赖恩的建议，而且效果很好。我想进行一些优化，但页面会在 4 到 5 秒而不是一分钟内出现，而且我的分页控件能够与我的新存储过程很好地集成。
回答by Brian Schmitt
Something like this should work: From Frans Bouma's Blog
这样的事情应该有效：来自 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)
回答by Chobicus
回答by JoelC
In the interest of completeness, for people looking for a more modern solution, in Oracle 12cthere are some new features including better paging and top handling.
为了完整起见，对于寻求更现代解决方案的人们，Oracle 12c 中有一些新特性，包括更好的分页和顶部处理。
Paging
分页
The paging looks like this:
分页如下所示：
SELECT *
FROM user
ORDER BY first_name
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
Top N Records
前 N 条记录
Getting the top records looks like this:
获取最高记录如下所示：
SELECT *
FROM user
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY
Notice how both the above query examples have ORDER BYclauses. The new commands respect these and are run on the sorted data.
请注意上述两个查询示例如何都有ORDER BY子句。新命令尊重这些并在排序后的数据上运行。
I couldn't find a good Oracle reference page for FETCHor OFFSETbut this pagehas a great overview of these new features.
我无法找到一个很好的Oracle参考页FETCH或OFFSET但这个页面有这些新功能很好的概述。
Performance
表现
As @wweicker points out in the comments below, performance is an issue with the new syntax in 12c. I didn't have a copy of 18c to test if Oracle has since improved it.
正如@wweicker 在下面的评论中指出的那样，性能是 12c 中新语法的一个问题。我没有 18c 的副本来测试 Oracle 是否对其进行了改进。
Interestingly enough, my actual results were returned slightly quicker the first time I ran the queries on my table (113 million+ rows) for the new method:
有趣的是，当我第一次在我的表（1.13 亿多行）上为新方法运行查询时，我的实际结果返回得稍微快一点：
- New method: 0.013 seconds.
- Old method: 0.107 seconds.
- 新方法：0.013 秒。
- 旧方法：0.107 秒。
However, as @wweicker mentioned, the explain plan looks much worse for the new method:
然而，正如@wweicker 所提到的，新方法的解释计划看起来更糟：
- New method cost: 300,110
- Old method cost: 30
- 新方法成本：300,110
- 旧方法成本：30
The new syntax caused a full scan of the index on my column, which was the entire cost. Chances are, things get much worse when limiting on unindexed data.
新语法导致对我的列上的索引进行全面扫描，这是全部成本。当限制未索引的数据时，情况可能会变得更糟。
Let's have a look when including a single unindexed column on the previous dataset:
让我们看看在前一个数据集上包含单个未索引列的情况：
- New method time/cost: 189.55 seconds/998,908
- Old method time/cost: 1.973 seconds/256
- 新方法时间/成本：189.55 秒/998,908
- 旧方法时间/成本：1.973 秒/256
Summary: use with caution until Oracle improves this handling. If you have an index to work with, perhaps you can get away with using the new method.
总结：在 Oracle 改进此处理之前请谨慎使用。如果您有要使用的索引，也许您可以使用新方法。
Hopefully I'll have a copy of 18c to play with soon and can update
希望我很快就能玩到 18c 的副本并且可以更新
回答by Vadim Kirilchuk
Just want to summarize the answers and comments. There are a number of ways doing a pagination.
只是想总结一下答案和评论。有多种方法可以进行分页。
Prior to oracle 12c there were no OFFSET/FETCH functionality, so take a look at whitepaperas the @jasonk suggested. It's the most complete article I found about different methods with detailed explanation of advantages and disadvantages. It would take a significant amount of time to copy-paste them here, so I won't do it.
在 oracle 12c 之前，没有 OFFSET/FETCH 功能，因此请按照 @jasonk 的建议查看白皮书。这是我找到的关于不同方法的最完整的文章，其中详细说明了优缺点。将它们复制粘贴到此处需要花费大量时间，因此我不会这样做。
There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination. jooq's blogpost
jooq 创建者也有一篇很好的文章，解释了 oracle 和其他数据库分页的一些常见警告。jooq 的博文
Good news, since oracle 12c we have a new OFFSET/FETCH functionality. OracleMagazine 12c new features. Please refer to "Top-N Queries and Pagination"
好消息，从 oracle 12c 开始，我们有了新的 OFFSET/FETCH 功能。OracleMagazine 12c 新特性。请参考“Top-N查询和分页”
You may check your oracle version by issuing the following statement
您可以通过发出以下语句来检查您的 oracle 版本
SELECT * FROM V$VERSION
回答by Furetto
Try the following:
请尝试以下操作：
SELECT *
FROM
(SELECT FIELDA,
FIELDB,
FIELDC,
ROW_NUMBER() OVER (ORDER BY FIELDC) R
FROM TABLE_NAME
WHERE FIELDA = 10
)
WHERE R >= 10
AND R <= 15;
via[tecnicume]
通过[技术]
回答by Ferdous Wahid
In my project I used Oracle 12c and java. The paging code looks like this:
在我的项目中，我使用了Oracle 12c 和 java。分页代码如下所示：
public public List<Map<String, Object>> getAllProductOfferWithPagination(int pageNo, int pageElementSize, Long productOfferId, String productOfferName) {
try {
if(pageNo==1){
//do nothing
} else{
pageNo=(pageNo-1)*pageElementSize+1;
}
System.out.println("algo pageNo: " + pageNo +" pageElementSize: "+ pageElementSize+" productOfferId: "+ productOfferId+" productOfferName: "+ productOfferName);
String sql = "SELECT * FROM ( SELECT * FROM product_offer po WHERE po.deleted=0 AND (po.product_offer_id=? OR po.product_offer_name LIKE ? )" +
" ORDER BY po.PRODUCT_OFFER_ID asc) foo OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ";
return jdbcTemplate.queryForList(sql,new Object[] {productOfferId,"%"+productOfferName+"%",pageNo-1, pageElementSize});
} catch (Exception e) {
System.out.println(e);
e.printStackTrace();
return null;
}
