What is ROWID?

ROWID is a pseudocolumn in Oracle that uniquely identifies the physical location of a row in a table.

• It represents the address of a row on disk, not a logical sequence number.
• Every row in an Oracle table has a unique ROWID.
• It is automatically assigned by Oracle when the row is inserted.

Characteristics of ROWID

Feature	Description
Unique	Each row has a unique ROWID within the database.
Physical Address	Contains data object number, datafile, block, and row position.
Read-Only	Cannot be updated or modified by users.
Fast Access	Using ROWID in queries provides very fast access to a specific row.
Visible	You can select ROWID in queries just like any column.

Syntax

SELECT ROWID, column1, column2
FROM your_table;

Example:

SELECT ROWID, first_name, last_name
FROM employees;

Sample Output:

ROWID	FIRST_NAME	LAST_NAME
AAAJbQAAEAAAAEJAAA	John	Smith
AAAJbQAAEAAAAEJABA	Sarah	Jones

How ROWID Works Internally

A ROWID encodes the physical location of a row in the database:

ROWID = (Data Object Number, File Number, Block Number, Row Number)

• Data Object Number: The table or index object ID
• File Number: Datafile containing the row
• Block Number: Block in the file
• Row Number: Position of the row in the block

Implication:

• ROWID changes if a row is moved (e.g., during table reorganization or export/import).
• It is not permanent, so should not be used as a primary key.

Advantages of Using ROWID

1. Fast access to rows:
   Queries using ROWID are extremely efficient because Oracle can directly access the physical location. SELECT * FROM employees WHERE ROWID = 'AAAJbQAAEAAAAEJAAA';
2. Useful for updates and deletes:
   You can update or delete a specific row using its ROWID: DELETE FROM employees WHERE ROWID = 'AAAJbQAAEAAAAEJABA';
3. Identify duplicate rows:
   Find and remove duplicates by combining ROWID with analytic functions: DELETE FROM employees WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM employees GROUP BY first_name, last_name );

Examples of Using ROWID

1. Select ROWID and Data

SELECT ROWID, employee_id, first_name, salary
FROM employees;

2. Delete a Specific Row

DELETE FROM employees
WHERE ROWID = 'AAAJbQAAEAAAAEJAAA';

3. Update a Specific Row

UPDATE employees
SET salary = salary * 1.1
WHERE ROWID = 'AAAJbQAAEAAAAEJABA';

4. Find Duplicate Rows Using ROWID

SELECT ROWID, first_name, last_name
FROM employees
WHERE (first_name, last_name) IN (
    SELECT first_name, last_name
    FROM employees
    GROUP BY first_name, last_name
    HAVING COUNT(*) > 1
)
ORDER BY first_name, last_name;

Important Notes About ROWID

Note	Explanation
Not Permanent	ROWID may change if the row is moved (e.g., ALTER TABLE MOVE).
Not a Substitute for Primary Key	Use a proper primary key for logical identification.
Efficient Access	Queries using ROWID are faster than queries using other columns.
Always Visible	Can be selected, but cannot be inserted or modified.

ROWID vs ROWNUM vs ROW_NUMBER()

Feature	ROWID	ROWNUM	ROW_NUMBER()
Type	Pseudocolumn (physical location)	Pseudocolumn (row sequence in result set)	Analytic function (logical numbering)
Unique?	Yes (for each row)	No	Yes
Order-aware?	No	No (assigned before ORDER BY)	Yes (after ORDER BY)
Usage	Fast access, delete/update specific row	Limit rows, pagination	Pagination, ranking, top-N queries