Oracle paging query performance

I'm new to Oracle and I've noticed that pagination queries take the format.

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

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

Why is there an extra outer select to split the min and max rownum where clause? Would this be just as performant and correct?

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

Shouldn't ROWNUM be already be correct since the inner query filters and orders the values?

No, the second query won't work. It will always return 0 rows if :MIN_ROW_TO_FETCH is greater than 1.

Conceptually, if you have a query like

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

where :MIN_ROW_TO_FETCH is 11 and :MAX_ROW_TO_FETCH is 20, Oracle would fetch the first row, give it a rownum of 1, then discard it because it didn't satisfy the predicate. It would then fetch the second row from the inner query, give it a rownum of 1 (since no rows have been successfully returned yet), then discard it because it didn't satisfy the predicate. That would repeat until every row had been fetched, assigned a rownum of 1, and discarded. So the query would return 0 rows.

The extra layer of nesting ensures that the outer query with rnum >= :MIN_ROW_TO_FETCH sees rows with rnum values between 1 and :MAX_ROW_TO_FETCH and that the full query returns the expected set of rows.

If you're using 12.1 or later, you can use the OFFSET ... FETCH syntax as well

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

StackExchange Database Administrators Q#101545, answer score: 3