Oracle paging query performance

Question

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 <= :MAX_ROW_TO_FETCH ) 
where
 rnum >= :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?

Answer 1

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;

• Ted Tomlinson: Thanks - I should have read that doc I linked to more closely. I see that it explains that ROWNUM is only assigned after the predicate phase so having a ROWNUM > X clause isn't very useful as an inclusion filter.