DIFF ORCL

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

       110 AAASd8AAEAAAAIcAAJ          5

       105 AAASd8AAEAAAAIcAAE          3

       107 AAASd8AAEAAAAIcAAG          4




       103 AAASd8AAEAAAAIfAAA          6

       106 AAASd8AAEAAAAIfAAB          7

       109 AAASd8AAEAAAAIfAAD          9

       108 AAASd8AAEAAAAIfAAC          8

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.




ROWID                  ROWNUM         ID NAME

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




AAAS7GAAEAAAARjAAA          1        102 Ankit

------------------ ---------- ---------- ----------

AAAS7GAAEAAAARjAAB          2        104 Nikhil

SQL> ALTER Table MyTab MOVE;

AAAS7GAAEAAAARjAAC          3        105 Rajan

AAAS7GAAEAAAARjAAD          4        107 Karan

AAAS7HAAEAAAARrAAA          1        102 Ankit





Table altered.

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




ROWID                  ROWNUM         ID NAME

------------------ ---------- ---------- ----------

AAAS7HAAEAAAARrAAD          4        107 Karan

AAAS7HAAEAAAARrAAB          2        104 Nikhil

AAAS7HAAEAAAARrAAC          3        105 Rajan