Oracle分页查询性能

Question

我是Oracle的新手，我注意到分页查询采用这种格式。

select * 
from ( select /*+ FIRST_ROWS(n) */ 
         a.*, ROWNUM rnum 
       from
         ( your_query_goes_here with filter and order by ) a 
       where
         ROWNUM <= :MAX_ROW_TO_FETCH ) 
where
  rnum  >= :MIN_ROW_TO_FETCH;

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

为什么会有额外的外部选择来分隔min和max rownum where子句？这会不会是同样的表演和正确？

select /*+ FIRST_ROWS(n) */
  *
from 
  ( your_query_goes_here with filter and order by )
where
  ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

ROWNUM不应该已经正确了吗，因为内部查询过滤器和排序值？

Answer 1

不，第二个查询不起作用。如果:MIN_ROW_TO_FETCH大于1时，则始终返回0行。

从概念上讲，如果您有一个类似于

select * /*+ FIRST_ROWS(n) */ 
  from ( your_query_goes_here with filter and order by )
  where ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

在:MIN_ROW_TO_FETCH为11而:MAX_ROW_TO_FETCH为20的情况下，甲骨文将获取第一行，给它一个1的rownum，然后放弃它，因为它不满足谓词。然后从内部查询中获取第二行，给它一个1的rownum (因为还没有成功返回任何行)，然后因为它不满足谓词而放弃它。这将重复执行，直到每一行都被取下来，分配一个rownum值为1，然后丢弃。因此，查询将返回0行。

额外的嵌套层确保带有rnum >= :MIN_ROW_TO_FETCH的外部查询看到rnum值介于1和:MAX_ROW_TO_FETCH之间的行，并且完整的查询返回预期的行集。

如果使用12.1或更高版本，也可以使用OFFSET ... FETCH语法

SELECT *
  FROM table_name
 ORDER BY some_column
 OFFSET n ROWS
  FETCH NEXT m ROWS ONLY;