When Paging needs to be done in an web site... Which method performs better?
Analytic function - ROW_NUMBER()
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
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(*)
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.