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.

score 2 · Answer 1 · answered Dec 20 '11 at 17:35

Your solution will not work (as Bob correctly pointed out) but you can use row_number() to do what you want:

SELECT col1, 
       col2
FROM ( 
   SELECT col1, 
          col2, 
          row_number() over (order by some_column) as rn
   FROM your_table
) t
WHERE rn BETWEEN 10 AND 20

Note that this solution has the added benefit that you can order the final result on a different criteria if you want to.

Edit: forgot to answer your initial question:

With the above solution, yes Oracle will have to build the full result in order to find out the correct numbering.

With 11g and above you might improve your query using the query cache.

This solution is too slow for paging on large tables. – Vadzim Dec 21 '11 at 10:32 — Vadzim, Dec 21 '11 at 10:32

score 2 · Accepted Answer · answered Dec 20 '11 at 18:01

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.

score 2 · Answer 3 · edited May 23 '17 at 12:11

2

Concerning the question's title.

See http://www.orafaq.com/wiki/ROWNUM and this in-depth explanation by Tom Kyte.

Concerning the question's goal.

This should be what you're looking for: Paging with Oracle

edited May 23 '17 at 12:11

Community

1
1

answered Dec 20 '11 at 18:02

Vadzim

24,954
11
143
151

score 0 · Answer 4 · answered Aug 26 '12 at 23:27

0

It's an old question but you should try this - http://www.inf.unideb.hu/~gabora/pagination/results.html

answered Aug 26 '12 at 23:27

jasonk

969
5
9

score 0 · Answer 5 · answered Dec 20 '11 at 17:31

I don't think your design is quite going to work out as you've planned. Oracle assigns values to ROWNUM in the order that they are produced by the query - the first row produced is assigned ROWNUM=1, the second is assigned ROWNUM=2, etc. Notice that in order to have ROWNUM=21 assigned the query must first return the first twenty rows and thus if you write a query which says

SELECT *
  FROM MY_TABLE
  WHERE ROWNUM >= 21 AND
        ROWNUM <= 40

no rows will be returned because in order for there to be rows with ROWNUM >= 21 the query must first return all the rows with ROWNUM <= 20.

I hope this helps.

Yep, thanks for that. I'll keep looking for what methods industry experts use to solve my particular problem. If you know any good source of information for what I'm trying to do, feel free to pass it on. — enamrik, Dec 20 '11 at 17:34

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

5 Answers5