Register Login

Difference between ROWID and ROWNUM

Updated Mar 15, 2024

What is ROWID in SQL

  • ROWID is a pseudo column in a table in Oracle that stores and returns row addresses in HEXADECIMAL format with database tables.
  • ROWID is the permanent unique identifier for each row in the database
  • ROWID consists of an 18-character string with the format. BBBBBBBBB.RRRR.FFFF Where B is Block, R is Row, F is FIle

ROWID in Oracle Example

SELECT ROWID, first_name  
   FROM employees
   WHERE department_id = 30;

  • A user can access a row quickly and easily using its row ID.
  • ROWID can also be used to delete duplicate records from a tame.

What is ROWNUM in SQL

  • ROWNUM is representative of the sequence allocated to any data retrieval bunch.
  • For each row returned by a query, the ROWNUM pseudo column returns a number that indicates the order in which a row was selected from a table. For example, the 1st row gets the number 1, the 2nd gets the number 2, and so on.
  • ROWNUM can be used to limit the number of rows returned by a query, as shown in the example below:

SELECT * FROM employees WHERE ROWNUM < 10;

  • The row numbers of the records might change if order by clause is used in the query.
  • ROWNUM can also be used for assigning unique values for every row in a table.
  • The user can also use ROWNUM to present the dataset in a report with serial numbers.

RowNum and RowId in Oracle

ROWID is representative of the allocation of physical memory. ROWNUM is representative of the sequence allocated to any data retrieval bunch.
ROWID is the permanent identity or address of a row. ROWNUM is a temporarily assigned sequence to a row.
ROWID is a 16-digit Hexadecimal number in the format BBBBBBBB.RRRR.FFFF. Here B is Block, R is Row, and F is File. ROWNUM is a numeric sequence number.
The output of ROWID is the physical address of a row. The output of ROWNUM is the sequence number of a row.
ROWID helps to retrieve data from a row. ROWNUM allows retrieving a row containing data.
ROWID comprises of the position of the row, the data object number, the data block in the data file, as well as data file in which row resides. ROWNUM comprises of sequence numbers of the rows.
Oracle automatically generates a unique ROWID at the time of insertion of a row. ROWNUM is a dynamic value that is retrieved automatically with specific statement outputs.
The fastest way of accessing data is by using ROWID. Accessing data is unrelated to ROWNUM.


ROWID and ROWNUM are both valuable tools in Oracle for working with table data. However, they serve different purposes.

  • ROWID provides a permanent, physical address for each row, allowing for very fast access. It's ideal for situations where you need to directly retrieve specific data based on its location.
  • ROWNUM assigns a temporary sequence number to each row within a query's result set. It's helpful for tasks like limiting the number of returned rows, adding sequential numbering to reports, or assigning temporary unique values.