不使用TABLE FULL SCAN数据访问方法的Oracle查询结果分页 [英] Oracle query results pagination without TABLE FULL SCAN data access method

问题描述

关于stackoverflow，如何正确执行分页有很多问题，对于oracle，最流行的答案是这样的:

There are plenty of questions on stackoverflow on how to perform pagination correctly, for oracle the most popular answer is something like this:

select * from (
    select row_.*, rownum rownum_ 
    from (select * from some_table) row_ 
    where rownum <= N) 
where rownum_ > M;

我到处都可以看到这个查询，并且Hibernate也会为分页生成它.在大多数情况下可能还可以，但是它需要全表扫描，并且在处理大量数据时会显着降低速度.

I've seen this query everywhere and Hibernate also generates it for pagination. It's probably ok for most cases but it requires full table scan and slows down significantly on huge amounts of data.

有一个提示应该有助于选择前N行

There is a hint that's supposed to help selecting first N rows

/*+ FIRST_ROWS(5000) */

但是它在选择第二页时没有帮助，并且似乎也使用了全扫描，至少这是解释计划"对我说的.

but it doesn't help in case of selecting second page and seems to use full scan also, at least that's what "explain plan" says to me.

为解决此问题，我目前正在实现自定义分页-读取表中所有行的ID并将它们拆分为范围，以便分页查询看起来像这样:

To deal with this problem I'm currently implementing custom pagination - reading ids of all rows in a table and splitting them on ranges so that pagination query can look something like this:

select * from some_table where id between N and M;

我希望找到一个解决此问题的供应商解决方案，但到目前为止尚未成功.

I was hoping to find a vendor solution to this problem but haven't succeeded so far.

因此，问题是-我是否正在重新发明轮子，或者没有完全扫描就真的无法在oracle上实现分页吗?

更新:在Oracle 12c中，他们引入了用于分页的新语法:

Upd: in Oracle 12c they introduced a new syntax for paging:

OFFSET N ROWS FETCH NEXT M ROWS ONLY;

我尝试对此进行解释，这似乎只是它的别名

I've tried explain plan on this and it seems to be just an alias for

select * from (
    select row_.*, rownum rownum_ 
    from (select * from some_table) row_ 
    where rownum <= N) 
where rownum_ > M;

UPD2:刚刚发现了一个类似的问题- Oracle和分页 看起来我之前一直在寻找重复项时注意力不集中.因此，最有可能我的问题的答案是否定的，但从那以后也许还是有所改变...

UPD2: just found a similar question - Oracle and Pagination looks like I've been inattentive while searching for duplicates before. So, most probably the answer on my question is negative but still, maybe something has changed since then...

推荐答案

首先:全表扫描并非总是魔鬼.

First of all: A full table scan is not always the devil.

1. 在您的解释计划中检查无需分页的查询费用
2. 在分页解决方案中检查它

另外，在进行测试时，请尝试使用具有高分页值的大型表

Also when you do tests, try to use large tables with high values of pagination

其他要点:

• 不定顺序进行分页总是很危险的，因为您不能确定Oracle按照哪个顺序为您的下一个页面"提供结果->可能无法再现的结果
• 还有可能以有序结果显示，当前视图"之前的页面中的新条目"会影响您的下一页"

除了要在数据库中存储"查询并逐页获取数据，直到带下划线的数据有所变化之外，我是否要这么做?

I except, that you like to "store" a query in DB and get the data, page by page, until something changed in the underlined data?

这篇关于不使用TABLE FULL SCAN数据访问方法的Oracle查询结果分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！