ROWNUM gets values like 1,2,3,4…N where N is the maximum
number of rows used in the query. Assigning unique numbers to each
row/record is a runtime process. ROWNUM is never associated with any record
permanently. Therefore, one cannot ask for the 7th row of a table directly. So
it is not sensible to ask for the following code snippet.
Listing 1
select * from emp where rownum > n;
Also, at times people find difficulty in understanding when
the ROWNUM stuff actually gets generated. The ROWNUM gets generated only after
the query passes through a particular phase, but before any aggregation or
sorting. ROWNUM gets incremented only after it is assigned. ROWNUM can be used
as shown below.
Listing 2
select * from emp where rownum < n;
This will show any records with the matched criteria. Where
n is any integer.
But ROWID is like a pointer that holds the address to each
record of a table. They are unique identifiers for rows in a table. If we
delete and reinsert a row, its rowid may change. If we delete a row, then
Oracle may reassign its ROWID to a new row inserted later.
Listing 3 – Selection of nth record
select * from emp where rownum =1 and rowid not in
(select rowid from emp where rownum < n);
In order to reach for nth record of emp table, the query
first discards the top n-1 records and then selects the top record from the
remaining list.
Let me explain with the following example.
Let us say we require querying from emp table which is a default
one in Oracle 9i. Consider the first 15 records.
Listing 4
select * from emp where rownum <16;
Figure 1
Let us associate our records with respective
rowid.
Listing 5
select employee_id,first_name,rowid from emp where rownum <16;
Figure 2
Go for the third record in the table. Clearly it seems that
the FIRST_NAME is Lex with EMPLOYEE_ID 102 having ROWID AAAIRPAAJAAACaUAAC.
Our query given in Listing 1 works fine as long as the
remaining records (after excluding the top n-1 records) appear in a sequential
fashion. But there is no guarantee that the query will behave the same. We may
get the wrong record if the desired sequence is not followed.
This may be seen from the following query.
Listing 6
select EMPLOYEE_ID,FIRST_NAME,ROWID from emp where rowid not in
(select rowid from emp where rownum < 3) and rownum < 16;
Figure 3
We can clearly conclude from above that the above set of rowid
must contain 3rd rowid, AAAIRPAAJAAACaUAAC, in the beginning. Any failure in
having 3rd rowid in the beginning will not show the 3rd row as the output. This
is simply because rownum =1 will always pick the record that appears first.
This also reflects from the query itself. As expected the
query will not give the 3rd row, instead it will pick John having rowid
AAAIRPAAJAAACaUAAK as it appears on top.
Listing 7
select * from emp where rownum =1 and rowid not in
(select rowid from emp where rownum < 3);
Figure 4
Now let us find another solution.
Listing 8 – Selection of nth record (Better
Alternative Logic)
select * from emp where rownum < n+1 minus select * from emp where rownum < n;
This fetches the first n records and then rejects first n-1
records to get the nth record.
Now we will verify the table emp for the 3rd record.
Listing 9
select * from emp where rownum < 4 minus select * from emp where rownum < 3;
Figure 5
And indeed we can reach 3rd record through the above code.
