Oracle ROWNUM : What it is and what it's not

What is ROWNUM ?

In Oracle, ROWNUM is a pseudocolumn added by Oracle after the data has been selected. The ROWNUM will be incremented only if the selected row satisfies the condition clause. A pseudocolumn does not physically exist, but it behaves like any table column. The first row returned by a query has a ROWNUM of 1, the second row has a ROWNUM of 2, etc... The ROWNUM can be used to retrieve the top n records of a query. For example, to return a maximum of ten cats from an animal table, the query would look like:

SELECT * from animals WHERE type='cat' and ROWNUM < 11

Pitfall 1: Ordered results

The preceding query returned ten cats. But it doesn't mean that the same ten cats will be returned each time we execute that query, because the fetched records are not ordered. However, adding a simple ORDER BY does not work:

SELECT * from animals WHERE type='cat' and ROWNUM < 11 ORDER BY name

because the results will be first fetched and then ordered. To get the first ten results of an ordered query, we have to fetch records and order them first :

SELECT * from ( SELECT * from animals WHERE type='cat' ORDER BY name) WHERE ROWNUM < 11

Pitfall 2: Getting the nth record

We may think that using the ROWNUM allows to fetch the nth record of a query. For example, getting the second cat of the list :

SELECT * from animals WHERE type='cat' and ROWNUM = 2

Unfortunatly, this doesn't work. In the first paragraph of this article, you might have noticed that some words are in bold : the ROWNUM is set after records were fetched and will be incremented only if the selected row satisfies the condition clause. What's the problem with that ? Imagine that there are ten cats in the animals table. After fetching ten cats, oracle will try to assign a ROWNUM to the first record. The first record ROWNUM is 1. But the query fetches only the record whose ROWNUM is 2. The first record will be skipped, but the ROWNUM will still be 1. The ROWNUM will not be incremented. Thus, the second record ROWNUM is 1. And again, the "ROWNUM = 2" condition is not satisfied. Finally, the ROWNUM will never be incremented, and no records will be fetched. The equality condition will only work with the first record (ROWNUM = 1).

Pitfall 3: Getting all records after the nth record

Well, the problem is the same as with getting the nth record. The condition will never be met. Having a condition looking like "WHERE ROWNUM > 10" will never return anything. The first record whose ROWNUM is 1 will not satisfy the condition, so 1 will be used again for the next record, and so on.

Execution plans

It's interesting to look at the difference of execution plan between "ROWNUM < 11" and "ROWNUM = 2". The first condition uses a "COUNT STOPKEY" operation, which means that Oracle performs a count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause. Even if there are one billion records in the animals table, only the first ten records will be read.

SELECT * from animals WHERE ROWNUM < 11

------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |    10 |   850 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY     |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| ANIMALS |    10 |   850 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM>10)

On the other hand, the erronous "ROWNUM = 2" condition produces the following plan:

SELECT * from animals WHERE ROWNUM = 2

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    80 |  6800 |     3   (0)| 00:00:01 |
|   1 |  COUNT              |         |       |       |            |          |
|*  2 |   FILTER            |         |       |       |            |          |
|   3 |    TABLE ACCESS FULL| ANIMALS |    80 |  6800 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=2)

We can see that Oracle goes through all records and tries to filter them using "ROWNUM = 2".

References

• Oracle Sql Language Reference : 3 Pseudocolumns, ROWNUM Pseudocolumn
• Ask Tom