ROWNUM vs. ROWID: What's the Difference? (Basic 1 - 21)

ROWNUM and ROWID are two pseudocolumns in Oracle SQL that are used to uniquely identify rows in a table. However, there are some key differences between the two.

ROWNUM

ROWNUM is a sequential number that is assigned to each row in the result set of a query. The first row has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. ROWNUM is a temporary value that is only valid for the duration of the query.

ROWNUM can be used to limit the number of rows returned by a query. For example, the following query will return the first 10 rows from the EMP_TEST table:

SQL

SELECT * FROM EMP_TEST WHERE ROWNUM <= 10;

ROWID

ROWID is a physical address that is assigned to each row in a table when it is inserted. ROWID is a permanent value that does not change, even if the row is updated or deleted.

ROWID can be used to quickly access a specific row in a table. For example, the following query will access the row with the ROWID 'AAASd8AAEAAAAIfAAA':

SQL

SELECT * FROM EMP_TEST WHERE ROWID = 'AAASd8AAEAAAAIfAAA';

Differences between ROWNUM and ROWID

The following table summarizes the key differences between ROWNUM and ROWID:

Feature	ROWNUM	ROWID
Type	Numeric	Hexadecimal
Permanence	Temporary	Permanent
Purpose	To limit the number of rows returned by a query or to reference a row within a fetched result set	To quickly access a specific row in a table

Example

The following example shows how to use ROWNUM and ROWID in a query:

SQL

SELECT ID, ROWNUM, ROWID FROM EMP_TEST;

Output:

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

Conclusion

ROWNUM and ROWID are two powerful tools that can be used to manipulate and access data in Oracle SQL. By understanding the differences between the two, you can choose the right tool for your needs.