Oracle Paging: Analytic function ROW_NUMBER() VS ROWNUM

Question

When Paging needs to be done in an web site... Which method performs better?

Analytic function - ROW_NUMBER()

• Oracle 10G

• http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html

  SELECT columnA, 
      columnB
  FROM (SELECT columnA,
             columnB,
             row_number() over (order by columnB) rn
        FROM table)
  WHERE rn BETWEEN LOW_LIMIT AND OFFSET;
  

ROWNUM

• http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o56asktom-086197.html

• INMHO I find this approach a more human-readable code

  SELECT * FROM (
    SELECT rownum rn, a.* 
    FROM(
      SELECT columnA, columnB
      FROM table 
      ORDER BY columnB
    ) a 
    WHERE rn <= OFFSET
  )
  WHERE rnum >= LOW_LIMIT
  

  • Note: I understand that there are RANK and DENSE_RANK analytic functions, but lets assume I just need page through deterministic queries.

  • Note 2: To retrieve the total amount of records I am thinking in using a separate simple query count(*)

Solution 1

I thought this question was interesting, so I tried a few things out.

I have table called large_t, that contains about 1.1M rows.

Then I have two queries:

select * 
from
(
  select rownum rnum, a.*
  from (
         select owner, object_name, object_id
         from large_t
         order by object_id
       ) a
  where rownum   <= 30      
) where rnum > 20;

And

select *
from
(
select owner, object_name, object_id,
       row_number() over (order by object_id) rnum
from large_t
) where rnum > 20 and rnum <= 30;

If you look at the plans the two queries generate, the first has an operation:

SORT ORDER BY STOPKEY

While the analytic query contains an operation called

WINDOW SORT PUSHED RANK

The SORT ORDER BY STOPKEY is a more efficient sort operation that a plain ORDER BY. I am not sure how a WINDOW SORT PUSHED RANK works, but it seems to work in a similar fashion.

Looking at v$sql_workarea after running both queries, both only required a sort_area of 4096 bytes.

In contrast, if I ran the query without a paging query:

select owner, object_name, object_id
from large_t
order by object_id

Then the sort area required is 37M, proving the sort in both queries is about the same.

Normally, if you want to efficiently return the TOP N of a sorted query, you will want an index on the sorting column - that will prevent Oracle needing to sort at all. So, I created an index on OBJECT_ID, and then explained both queries again.

This time the first query used the index and returned in 0.2 seconds, while the second query didn't use the new index and was much slower.

So my conclusion from this quick bit of analysis is that in the general case using rownum to filter or the analytic row_number function both perform about the same. However, the rownum example automatically started using the index I created on the table when row_number did not. Maybe I could get it to use the index with some hints - that is something else you can experiment with.

Solution 2

Apart from the other differences mentioned in answers, you should also consider performance. There is a non-authoritative but very interesting report here, comparing various means of pagination, among which the use of ROWNUM compared to ROW_NUMBER() OVER():

https://web.archive.org/web/20160901191310/http://www.inf.unideb.hu:80/~gabora/pagination/results.html

Comments

[Jimmy]

I would do a test myself but I have no privileges over the DB to create table. And btw I found a great article of how you can create dummy tables/records. [link] (blog.lishman.com/2008/02/…)

[Vadzim]

The link is dead and I was unable even to find it in Web Archive.

[Lukas Eder]

@Vadzim: How did you not find it? :-) Anyway, thanks for the hint. I've updated the answer