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
(APress)", by Iggy Fernandez. First of all, I am not a DBA. I'm using Oracle at work, but, most of the time, I'm only using basic SQL. I have nothing to do with database administration, but recently I wanted to know more about Oracle, and this book is just what I needed. The author doesn't go deeply into details, which that is not the purpose of the book. He introduces the most important topics (installation, monitoring, backups, recoveries, tuning...) in a very understandable manner. I don't think that this book will turn anyone into an Oracle Professional, as the title suggests, but it does cover enough material to make you more than a novice. I recommend it highly to anyone who wants to know the basic notions of the Oracle architecture and administration.
" by Thomas Kyte, which is planned to be published this month. For the moment, I'll start reading the Oracle Concepts PDF file, which is available online.