CODEWITHSUNDEEP
oracle-databasepaginationrow-numberrownum
Jimmy
Jimmy

Reputation: 377

Oracle Paging: Analytic function ROW_NUMBER() VS ROWNUM

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

Analytic function - ROW_NUMBER()

ROWNUM

Upvotes: 2

Views: 18277

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221106

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

Upvotes: 4

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

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.

Upvotes: 7

Ollie
Ollie

Reputation: 17558

To generate your own empirical results:

-- Create test table
CREATE TABLE test_large_tab (
  tlt_id   NUMBER,
  tlt_data VARCHAR2(50)
);

-- Load with data
BEGIN
   FORALL i IN 1 .. 1000000
      INSERT INTO test_large_tab
      (
       tlt_id,
       tlt_data
      )
      VALUES
      (
       i,
       TO_CHAR(sysdate-i, 'FMMon ddth, YYYY')
      );
END;

Of course, you can increase the size of the table to suit your testing purposes!

Set timing on and run both queries against the large table.

Change the table structure to better suit your test as you may want some columns to be indexed for your query too etc. but essentially it is a simple test and won't take you long to run.

If both come out roughly the same timings then use the most readable (and hence supportable) version.

Upvotes: 0

Related Questions