5

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?

3

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;
  • 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. – Ted Tomlinson May 14 '15 at 18:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.