Oralce ROWNUM Dymistified

(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.

Advertisements