ROWNUM vs. ROWID: What's the difference? ( Basic 1 - 2)

ROWNUM and ROWID are two Oracle pseudocolumns that are used to work with query results data. However, they have important differences in terms of their definition, usage, and behavior.

ROWNUM

ROWNUM is a pseudocolumn that returns the number of a row in the query results set. This number is assigned sequentially, starting with 1, for each row returned by the query.

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

SQL

SELECT * FROM EMP_TEST WHERE ROWNUM <= 10;

ROWNUM can also be used to order the results of a query by its row number. For example, the following query will return the results of the EMP_TEST table ordered by its row number, in descending order:

SQL

SELECT * FROM EMP_TEST ORDER BY ROWNUM DESC;

ROWID

ROWID is a pseudocolumn that returns the physical address of a row in the table. ROWID is a unique identifier for each row in the table and does not change, even if the row is updated.

ROWID can be used to access a specific row in the table, even if you do not know its primary key value. For example, the following query will return the row in the EMP_TEST table with the ROWID AAASd8AAEAAAAIcAAB:

SQL

SELECT * FROM EMP_TEST WHERE ROWID = 'AAASd8AAEAAAAIcAAB';

ROWID can also be used to track the movement of data between tables or partitions. For example, you can use ROWID to determine if a row has been moved to a different partition after an update.

Differences between ROWNUM and ROWID

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

Property	ROWNUM	ROWID
Definition	Sequential number of a row in the query results set	Physical address of a row in the table
Usage	Limit the number of rows returned by a query or order the results of a query by its row number	Access a specific row in the table or track the movement of data between tables or partitions
Behavior	ROWNUM changes if the query is altered	ROWID does not change, even if the row is updated

Examples of use

Here are some examples of how to use ROWNUM and ROWID:

SQL

-- Use ROWNUM to limit the number of rows returned by a query
SELECT * FROM EMP_TEST WHERE ROWNUM <= 10;

-- Use ROWNUM to order the results of a query by its row number, in descending order
SELECT * FROM EMP_TEST ORDER BY ROWNUM DESC;

-- Use ROWID to access a specific row in the table
SELECT * FROM EMP_TEST WHERE ROWID = 'AAASd8AAEAAAAIcAAB';

-- Use ROWID to track the movement of data between tables or partitions
CREATE TABLE MyTab AS
  SELECT ID, NAME FROM EMP_TEST WHERE ROWNUM <= 4;

ALTER TABLE MyTab MOVE;

SELECT ROWID, ROWNUM, ID, NAME FROM MyTab;

Conclusion

ROWNUM and ROWID are two important Oracle pseudocolumns that can be used to work with query results data. ROWNUM is used to limit the number of rows returned by a query or order the results of a query by its row number. ROWID is used to access a specific row in the table or track the movement of data between tables or partitions.