Paging with Oracle

我对甲骨文并不是很熟悉。我有一些25万记录，我想显示他们每页10万。目前，我有一个存储过程，它使用数据适配器、数据集和数据适配器将25万条记录全部检索到一个数据集。在存储过程的结果上填充(数据集)方法。如果我有“页码”和“每页记录的数量”作为整数值，我可以作为参数传递，什么是最好的方式返回特定的部分。比如说，如果我把10作为页码，把120作为页数，从 select 语句得到1880到1200，或者类似的数字，我脑子里的数学可能出错了。

我在做这个。NET 使用 C # ，认为这不重要，如果我能在 sql 方面得到它，那么我应该很酷。

更新: 我能够使用布莱恩的建议，它是工作的伟大。我想进行一些优化工作，但页面出现在4到5秒钟，而不是一分钟，我的分页控制能够很好地与我的新存储进程集成。

156906 次浏览
小开
最佳答案

像这样的东西应该可以工作: 来自 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)
小开

问 Tom 关于分页和非常非常有用的分析函数。

以下是该页的节选:

select * from (
select /*+ first_rows(25) */
object_id,object_name,
row_number() over
(order by object_id) rn
from all_objects
)
where rn between :n and :m
order by rn;
小开

试试以下方法:

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 ]

小开

为了保证完整性，对于寻求更现代化解决方案的人来说，神谕12c有一些新特性，包括更好的分页和顶级处理。

呼叫

这个页面看起来是这样的:

SELECT *
FROM user
ORDER BY first_name
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;

Top N Records

排名第一的记录是这样的:

SELECT *
FROM user
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY

注意以上两个查询示例都有 ORDER BY子句。新命令遵循这些规则，并在排序后的数据上运行。

我找不到一个很好的关于 FETCH或 OFFSET的 Oracle 参考页面，但是 这一页对这些新特性有很好的概述。

Performance

正如@wweicker 在下面的注释中指出的，12c 中的新语法存在性能问题。我没有18c 的拷贝来测试 Oracle 是否改进了它。

有趣的是，当我第一次在我的表(1.13亿多行)上运行新方法的查询时，我的实际结果返回得稍微快一些:

  • 新方法: 0.013秒。
  • Old method: 0.107 seconds.

然而，正如@wweicker 提到的，对于新方法来说，解释计划看起来糟糕得多:

  • 新方法成本: 300,110
  • 旧方法成本: 30

新语法导致对我的列的索引进行全面扫描，这就是全部成本。当限制未索引的数据时，情况可能会变得更糟。

让我们看看在前一个数据集中包含单个未索引列时的情况:

  • 新方法时间/成本: 189.55秒/998,908
  • 旧方法时间/成本: 1.973秒/256

总结: 在 Oracle 改进此处理之前，请谨慎使用。如果要使用索引，也许可以使用新方法。

Hopefully I'll have a copy of 18c to play with soon and can update

小开

Just want to summarize the answers and comments. There are a number of ways doing a pagination.

在 Oracle 12c 之前，没有 offSET/FETCH 功能，所以按照@jasonk 的建议看看 白皮书。这是我找到的关于不同方法的最完整的文章，其中详细解释了各种方法的优缺点。在这里复制粘贴它们需要花费大量的时间，所以我不会这样做。

还有一篇来自 jooq 创建者的好文章，解释了 Oracle 和其他数据库分页的一些常见注意事项

好消息，因为 Oracle 12c 我们有了一个新的 offSET/FETCH 功能。 OracleMagazine 12c 新功能。请参考“ Top-N Query and Pagination”

您可以通过发出以下语句来检查 Oracle 版本

SELECT * FROM V$VERSION
小开

在我的项目中，我使用了 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;
}
小开 
    In SomeServiceClass
using npoco
    

public async Task<List<SomeModel>> SomeServiceMethod(int pageIndex, int pageSize)
    

int lowerLimit;
int higherLimit;
//This would help limit the result to 300 max. If the PageSize is stated as 290
//we get the 1st to 289th result. If page size is 1845.
//It returns 1845 - 299 = 1546. 1546th element to 1844th element
    

if((pageSize) < 300)
{
lowerLimit = 1;
higherLimit = pageSize;
}
else
{
int subtract = 300 - 1;
lowerLimit = pageSize - subtract;
higherLimit = pageSize;
}
    

//Using Brian Schmitt script


List<SomeModel> someVariableName = db.Query<SomeModel>(SELECT * FROM
(
SELECT a.*, rownum r__
FROM
(
SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
ORDER BY OrderDate DESC, ShippingDate DESC
) a
WHERE rownum < (@0)
)
WHERE r__ >= (@1), higherlimit, lowerlimit).ToList();
    

    

    

In SomeControllerClass


[HttpGet]
[Route("SomeControllerMethod")]
public async Task<SomeResponseModel> SomeControllerMethod(int pageIndex, int pageSize)
    

SomeServiceClass ssc = new SomeServiceClass();
SomeResponseModel srm = new SomeResponseModel();
    

List<SomeModel> resp = await ssc.SomeServiceMethod(pageIndex, pageSize);
    

//Paging on API level, solution is from CSharpCorner. This would return a //max of 20 elements per page.
//So if you have a result of 300. It would be divided into 15 pages.
//20 results per page.


int totalRecords = resp.Count();
    

    

const int maxPageSize = 20;
pageSize = (pageSize > maxPageSize) ? maxPageSize : pageSize;
int pageNum = pageIndex;
int recordToTake = totalRecords - (pageNum - 1) * pageSize;
int CurrentPage = pageNum;
int TotalPages = (int)Math.Ceiling(totalRecords / (double)pageSize);
var previousPage = CurrentPage > 1 ? "Yes" : "No";
var nextPage = CurrentPage < TotalPages ? "Yes" : "No";
List<SomeModel> filteredResult = resp.Skip((CurrentPage - 1) * pageSize).Take(pageSize).ToList();
    

    

                    

srm.records = totalRecords;
srm.previousPage = previousPage;
srm.currentPage = $"Page: {CurrentPage} / {TotalPages}";
srm.nextPage = nextPage;
srm.totalPages = TotalPages;
srm.someIEnumerableProperty = filteredResult;
    

return srm;




//How to use endpoint
//http://localhost:somePort/someControllerClass/SomeControllerMethod?pageIndex={pageIndex}&pageSize={pageSize}


//pageIndex will take value 1 to 15 since we have a max of 300 and 20 results per page.
//pageSize will be used to determine our higherlimit

提交答案