ROWID and ROWNUM in SQL

ROWNUM and ROWID are both referred to as pseudo-columns. That is, they are
not "real" columns that will show up when you DESC a table. They don't
actually exist anywhere in the database. But they're available for you to
use.

In fact, ROWNUM only exists for a row once it is retrieved from a query. It
represents the sequential order in which Oracle has retrieved the row.
Therefore it will always exist, be at least 1, and be unique (among the
rows returned by the query). Obviously it will change from query-to-query.
Let's look at a quick example:

scott@Robert> SELECT ROWNUM, ENAME, SAL
2 FROM EMP;

ROWNUM ENAME SAL
---------- ---------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250
4 JONES 2975
5 MARTIN 1250
6 BLAKE 2850
7 CLARK 2450
8 SCOTT 3000
9 VOLLMAN 5000
10 TURNER 1500
11 ADAMS 1100
12 JAMES 950
13 FORD 3000
14 MILLER 1300


Ok so let's say we want the 5 highest paid employees. Should be easy:

scott@Robert> SELECT ROWNUM, ENAME, SAL
2 FROM EMP
3 WHERE ROWNUM < 6
4 ORDER BY SAL DESC;

ROWNUM ENAME SAL
---------- ---------- ----------
4 JONES 2975
2 ALLEN 1600
3 WARD 1250
5 MARTIN 1250
1 SMITH 800


Whoops! Turns out ROWNUM is assigned before results are ordered, not after.
Knowing that, we can write it like this:

scott@Robert> SELECT ENAME, SAL
2 FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) E
3 WHERE ROWNUM < 6;

ENAME SAL
---------- ----------
VOLLMAN 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850


What about ROWID? ROWID actually represents the physical location of the
record/row in the database. That being the case, it is (according to Oracle
documentation) the fastest way to retrieve a particular row. Faster than an
index, even.

Can you use ROWID to differentiate between duplicate rows?
Yes, you can. Since it actually represents the physical location of a row,
no two rows within the same table will have the same ROWID. Notice the
caveat I added: within the same table. If you're using clustering, two
records from different tables could theoretically share the same ROWID.

Do ROWIDs change?
Yes, especially with index organized or partitioned tables. Because ROWIDs
represent the physical location of a record/row, the ROWID will change
every time the record is physically moved.

Can you use ROWID as a primary key?
No, that's not advisable. While the ROWID will be unique, you would ideally
want to use a primary key that doesn't change.

How do you use ROWID to figure out what was the last record that was
processed?
Using DBMS_SQL.LAST_ROW_ID to get the ROWID of the last row processed.

You'll see ROWNUM and ROWID pop up occasionally within solutions to
problems on AskTom and various Discussion Forums, so I recommend adding it
to your own toolbelt as well.

Scenario 1:

You want to know why this is not working:
delete employee where rownum not in (select min(rownum) from employee group
by name,eno)

Ans: 1. Rowid is totally different from the rownum that you used in your
query.

2. Rowid is Unique for a record in the table (In the schema
itself).While you can't believe rownum to work like rowid(It may vary from
quey to query for the same table).

3. That's why this works fine:

SQL> Delete from employee where rowid not in (select min(rowid) from
employee group by name,eno);

If you are still confuse rownum and rowid click bellow link:

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html