ROWNUM Vs. ROWID

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.

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

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

SQL> 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 a ROWID changes?

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

• When you update a partition key and rows moves 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 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

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.