Friday, 28 March 2008

Using ROWNUM in Oracle

ROWNUM is an Oracle pseudo column which numbers the rows in a result set.
SELECT rownum, table_name
FROM user_tables;
ROWNUM        TABLE_NAME                     
------------- -----------------
1 EMP
2 DEPT
3 BONUS
4 SALGRADE
5 DUMMY
5 rows selected
Here is a summary of how ROWNUM can be used.

Limiting Rows

ROWNUM can be used to limit the number of rows returned by a query in a similar way to LIMIT in Postgres and MySql, TOP in SQL Server and FETCH FIRST in DB2.
SELECT rownum, table_name
FROM user_tables
WHERE rownum <=3;
ROWNUM        TABLE_NAME                     
------------- -----------------
1 EMP
2 DEPT
3 BONUS
3 rows selected

ROWNUM with DML

The use of ROWNUM is not restricted to select statements. It can be used with DML statements that update the database too.
CREATE TABLE o AS
SELECT *
FROM all_objects
WHERE rownum <= 1000;
Table created

UPDATE o
SET object_id = rownum,
created = created + INTERVAL '1' MINUTE * rownum
WHERE rownum <= 100;
100 rows updated

DELETE FROM o
WHERE OWNER = 'SYS'
AND rownum = 1;
1 rows deleted
ROWNUM is particularly useful, when used in conjunction with the CONNECT BY LEVEL clause, for creating arbitrary rows in the database. See the article on generating rows in Oracle for more details.

Offsetting Rows

Rows can also be skipped at the beginning of a result set using ROWNUM.
SELECT rnum, table_name
FROM
(SELECT rownum rnum, table_name
FROM user_tables)
WHERE rnum > 2;
RNUM     TABLE_NAME                     
-------- ----------------
3 SALGRADE
4 DUMMY
5 DEPT
3 rows selected
You will notice that an inline view has been introduced to transform the ROWNUM pseudo column into a 'real' column before we do the comparison.

It is tempting to write the above SQL as follows.
SELECT table_name
FROM user_tables
WHERE rownum > 2;
TABLE_NAME                     
------------------------------
0 rows selected
However, this query will always return zero rows, regardless of the number of rows in the table.

To explain this behaviour, we need to understand how Oracle processes ROWNUM. When assigning ROWNUM to a row, Oracle starts at 1 and only only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.

The bottom line is that conditions such as the following will work as expected.

.. WHERE rownum = 1;

.. WHERE rownum <= 10;



While queries with these conditions will always return zero rows.

.. WHERE rownum = 2;

.. WHERE rownum > 10;

Top-n Query

Typically, a top-n query sorts data into the required sequence and then limits the output to a subset of rows.

For example, suppose we wish to retrieve the top three earners from our employee table.
SELECT ename, sal
FROM (
SELECT ename, sal
FROM emp
ORDER BY sal DESC)
WHERE rownum <=3;
ENAME      SAL                    
---------- ---------
KING 5000
SCOTT 3000
FORD 3000
3 rows selected
The inline view (the inner select) sorts the rows and passes the result up to the outer select. The outer select then limits the output to three rows.

It may seem more natural to use the following SQL.
SELECT ename, sal
FROM emp
WHERE rownum <=3
ORDER BY sal DESC;
ENAME      SAL                    
---------- ----------------------
ALLEN 1600
WARD 1250
SMITH 800
3 rows selected
However, this does not give us the result we want because Oracle assigns the ROWNUM values to the rows before it does the sort.

In this example, Oracle will retrieve three rows from the table, any three rows, and sort only these three rows. We really need Oracle to sort all the rows and then return the first three. The inline view will ensure that this will happen.

Sort Performance

Limiting rows on a sorted result set using ROWNUM can also provide an added performance benefit. Rather than physically sorting all the rows to retrieve just the top few, Oracle maintains an array which contains just the highest or the lowest values (depending on whether we specified ASC or DESC in the ORDER BY clause). The size of the array will be the number of rows we wish to return. As rows are processed, only the highest (or lowest) values are retained in the array. All other rows are discarded.

Pagination

Next, we will see how ROWNUM is used to select a range of rows from within a result set. This is useful if we are to provide pagination on a web screen, for example.

Suppose we are paging through the employee table in name order and we wish to display rows six to ten inclusive.
SELECT rnum, ename, job
FROM
(SELECT /*+ FIRST_ROWS(10) */ rownum rnum, ename, job
FROM
(SELECT ename, job
FROM emp
ORDER BY ename)
WHERE rownum <= 10
)
WHERE rnum > 5;
RNUM     ENAME      JOB       
-------- ---------- ---------
6 JAMES CLERK
7 JONES MANAGER
8 KING PRESIDENT
9 MARTIN SALESMAN
10 MILLER CLERK
5 rows selected
We use nested inline views to retrieve and sort the data and then apply the range check using ROWNUM. We have split the upper and lower bound check, which allows Oracle to use COUNT(STOPKEY) in the execution plan when checking for ROWNUM <= 10. This is a performance optimization which, along with the sorting optimization described earlier, will ensure that our query runs efficiently as the table grows.

The FIRST_ROWS(n) hint also tells Oracle to optimize the query so that the first n rows are returned as quickly as possible.

Summary

ROWNUM provides a mechanism for returning a subset or range of rows from a query. It can be misleading at first if not properly understood but, once mastered, is invaluable for limiting result set output for pagination and top-n style queries.

For more information on ROWNUM, see Tom Kytes article on OTN.

For more information on Oracle, visit level up.

18 comments:

Hugo said...

wow! Thanks so much my friend
I'm a student from mexico city and I'm moving a database from Oracle to SQL SERVER (OLTP -> DWH) and you have saved my life jejeje
Thank you :)!!!!

Hugo said...

wow! Thanks so much my friend
I'm a student from mexico city and I'm moving a database from Oracle to SQL SERVER (OLTP -> DWH) and you have saved my life jejeje
Thank you :)!!!!

JoesPokerBlog said...

Although this was posted over a year ago, I stumbled across it and learned a great deal from it. The explanation was wonderful.

Chris said...

This is what i call a no-nonsense precise information.

shuvo said...

thanks so much.........

Bhuvana said...

Super! This helps!

Anonymous said...

Very informative .
Thanks ,
Preeti

Anonymous said...

wonderful explanation

devkishore said...
This comment has been removed by the author.
geekdev said...

When using pagination when i use order by on firstname..it is sorting record every page by page but when i change the query to sort all the records in a single time and then display 5 records every page it is running too slow.

Below are the details ..

The below results are returning fast
when i use ur query

page 1
Allen
Andrew
Bob
Cindey
David

Page 2
Anderson
Alex
Daniel
Ed
Emiley

But when i change the query to display results like below it is running very slow

page 1
Allen
Andrew
Anderson
Alex
Bob

page 2
Cindey
David
Daniel
Ed
Emiley

Any suggestion to improve performance ?

Thanks in advance !

Anonymous said...

this is superb.....helped lot

Matthew O. Smith said...

Great post. Very helpful.

Matthew O. Smith said...

Great post. Very helpful.

Anonymous said...

What if I don't need RNUM in the result? How to select data for Pagination without RNUM column?

Mark Lishman said...

If you don't want the rnum in the result, then simply leave it out of the select list..

SELECT ename, job
FROM
(SELECT /*+ FIRST_ROWS(10) */ rownum rnum, ename, job
FROM
(SELECT ename, job
FROM emp
ORDER BY ename)
WHERE rownum <= 10
)
WHERE rnum > 5;

Hai H. Phan said...

Does anyone complain about how unintuitive this rownum and row_number() work? Why we have to write nested queries to do these things? Why can't we use the standard LIMIT/OFFSET/TOP/whatever and let the query planner figure out how to do the rest? This is, after all, Oracle

sathish kannan said...

i have to update particular rows by using rownum..

Unknown said...
This comment has been removed by the author.