(from: http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html)
The following sql doesn’t work as many expected.
select * from t where ROWNUM > 1;
It will return 0 rows, because a ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned.
select * from ( select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from ( your_query_goes_here, with order by ) a where ROWNUM <= :MAX_ROW_TO_FETCH ) where rnum >= :MIN_ROW_TO_FETCH;
- FIRST_ROWS(N) tells the optimizer, “Hey, I’m interested in getting the first rows, and I’ll get N of them as fast as possible.”
- :MAX_ROW_TO_FETCH is set to the last row of the result set to fetch—if you wanted rows 50 to 60 of the result set, you would set this to 60.
- :MIN_ROW_TO_FETCH is set to the first row of the result set to fetch, so to get rows 50 to 60, you would set this to 50.