Does Oracle's ROWNUM build the whole table before it extract the rows you want?

Question

I need to make a navigation panel that shows only a subset of a possible large result set. This subset is 20 records before and 20 records after the resulted record set. As I navigate the results through the navigation panel, I'll be applying a sliding window design using ROWNUM to get the next subset. My question is does Oracle's ROWNUM build the whole table before it extracts the rows you want? Or is it intelligent enough to only generate the rows I need? I googled and I couldn't find an explanation on this.

Answer 1

The pre-analytic-function method for doing this would be:

select col1, col2 from (
    select col1, col2, rownum rn from (
        select col1, col2 from the_table order by sort_column
      )
      where rownum <= 20
  )
  where rn > 10

The Oracle optimizer will recognize in this case that it only needs to get the top 20 rows to satisfy the inner query. It will likely have to look at all the rows (unless, say, the sort column is indexed in a way that lets it avoid the sort altogether) but it will not need to do a full sort of all the rows.