SELECT ID, ROWID, ROWNUM From EMP_TEST;

ID ROWID ROWNUM
102 AAASd8AAEAAAAIcAAB 1
104 AAASd8AAEAAAAIcAAD 2
105 AAASd8AAEAAAAIcAAE 3
107 AAASd8AAEAAAAIcAAG 4
110 AAASd8AAEAAAAIcAAJ 5
103 AAASd8AAEAAAAIfAAA 6
106 AAASd8AAEAAAAIfAAB 7
108 AAASd8AAEAAAAIfAAC 8
109 AAASd8AAEAAAAIfAAD 9

9 rows selected.

When ROWID changes?

A ROWID is assigned to a row upon insert and is immutable (never changing). Changes happen in the following situations-

  • When you update a partition key and rows move the partition.
  • Flashback a table, since the flashback table command really issues a DELETE+INSERT to put the data back the way it was.
  • If you use alter table test shrink space compact, the ROWID could change.
  • When you move the table with Alter command- would change the ROWID’s.

SQL> CREATE TABLE MyTab As 2 SELECT ID, NAME From EMP_TEST Where ROWNUM<=4;

Table created.

SQL> SELECT ROWID, ROWNUM, ID, NAME From MyTab;

ROWID ROWNUM ID NAME
AAAS7GAAEAAAARjAAA 1 102 Ankit
AAAS7GAAEAAAARjAAB 2 104 Nikhil
AAAS7GAAEAAAARjAAC 3 105 Rajan
AAAS7GAAEAAAARjAAD 4 107 Karan

SQL> ALTER Table MyTab MOVE;

Table altered.

SQL> SELECT ROWID, ROWNUM, ID, NAME From MyTab;

ROWID ROWNUM ID NAME
AAAS7HAAEAAAARrAAA 1 102 Ankit
AAAS7HAAEAAAARrAAB 2 104 Nikhil
AAAS7HAAEAAAARrAAC 3 105 Rajan
AAAS7HAAEAAAARrAAD 4 107 Karan

What is the difference between ROWNUM and ROWID? Does Oracle make a distinction between a ROWID and ROWNUM?  

Rownum (Numeric) = Generated Sequence Number of your output. For each row returned by a query, the ROWNUM pseudo column returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. So you can limit the number of rows in your result.

Rowid (Hexadecimal) = Generated automatically at the time of insertion of row. For each row in the database, the ROWID pseudo column returns the address of the row.

S.No. ROWID ROWNUM
1 Physical address of the rows. Rownum is the sequential number, allocated to each returned row during query execution.
2 Rowid is permanent Rownum is temporary.
3 Rowid is 16-bit hexadecimal Rownum is numeric
4 Rowid gives address of rows or records Rownum gives count of records
5 Rowid is automatically generated unique id of a row and it is generated at the time of insertion of row. Rownum is a dynamic value automatically 
6 ROWID is the fastest means of accessing data. Retrieved along with select statement output.
7 They are unique identifiers for the any row in a table. It represents the sequential order in which Oracle has retrieved the row.

Check the below Query displaying placeholder and physical location of the row.

When ROWID changes?

A ROWID is assigned to a row upon insert and is immutable (never changing). Changes happen in the following situations-

  • When you update a partition key and rows move the partition.
  • Flashback a table, since the flashback table command really issues a DELETE+INSERT to put the data back the way it was.
  • If you use alter table test shrink space compact, the ROWID could change.
  • When you move the table with Alter command- would change the ROWID’s.