Header Ads Widget

Difference Between ROWNUM and ROWID in ORACLE SQL

Both are know as Oracle Pseudo Column. They do not exist on table but during runtime of a query, we can fetch it.

The actual difference between rowid and rownum is, that rowid is a permanent unique identifier for that row. However, the rownum is temporary. If you change your query, the rownum number will refer to another row, the rowid won't.


So the ROWNUM is a consecutive number which applicable for a specific SQL statement only. In contrary the ROWID, which is a unique ID for a row.


rowid vs rownum


ROWID


For each row in the database, the ROWID pseudocolumn returns the address of the row.



SELECT ROWID, emp_name,  department_id

   FROM employees

   WHERE department_id = 20;


AAAR4AAAFAAGzg7AAA, Ritesh, 20                     

AAAR4AAAFAAGzg7AAB, Mahesh, 20


ROWNUM


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.


You can limit the amount of results with rownum like this:


SELECT * FROM employees WHERE ROWNUM < 10;



Difference


  • Rowid gives the address of rows or records. Rownum gives a count of records
  • Rowid is permanently stored in the database. Rownum is not stored in the database permanently.
  • Rowid is automatically assigned with every inserted into a table. Rownum is a dynamic value automatically retrieved along with select statement output.

Post a Comment

0 Comments