Rownum, Rowid, Urowid

What is ROWID ?

• ROWID is a pseudo column in a table which store and return row address in HEXADECIMAL format with database tables. ROWID is the permanent unique identifiers for each row in the database

• ROWID consists of 18 character string with the format. BBBBBBBBB.RRRR.FFFF Where B is Block, R is Row, F is FIle

What is pseudocolumn ?

A pseudocolumn behaves like a table column, but is not actually stored in the table. We can select from pseudocolumns, but cannot insert, update, or delete their values

An example query would be:

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 the duplicate records from a tame

What is ROWNUM ?

• For each row returned by a query, the ROWNUM pseudo column returns a number which indicates the order in which a row was selected from a table. For example, the 1st row gets the number 1, 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.

What is UROWID ?

UROWID (Universal ROWID) is a data type in Oracle used to store the address (location) of a row in the database. It represents the physical or logical address of a row in a table and is primarily used when dealing with tables that use rowid-based addressing, including those with index-organized tables (IOTs) or foreign tables (like those accessed via Oracle's object-relational features).

The main purpose of UROWID is to provide a uniform way to store any type of rowid, including those from:

• Heap-organized tables (ROWID)

• Index-organized tables

• External tables

• Tables from remote or non-Oracle systems (via gateways)

It generalizes the older ROWID datatype, which only works with heap tables.

Limitations

• UROWID values are mostly meaningful internally to Oracle.

• Cannot use UROWID to retrieve a row from a table unless the value was originally obtained from that specific table type.

• Comparing UROWIDs across different tables is generally not meaningful.

Declaration Example

DECLARE
    my_rowid UROWID;
BEGIN
    SELECT rowid INTO my_rowid FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE('Row ID: ' || my_rowid);
END;

Here, rowid is stored into a variable of type UROWID.

Using UROWID in a Table

CREATE TABLE rowid_tracker (
    id NUMBER PRIMARY KEY,
    ref_id UROWID
);

We can store the UROWID of a row in this table for reference or auditing purposes.

INSERT INTO rowid_tracker (id, ref_id)
SELECT 1, rowid FROM employees WHERE employee_id = 100;

Retrieving UROWID

SELECT ref_id FROM rowid_tracker;

Row Identifiers – Comparison Table

• ROWNUM: Use it when we want to limit results, like "first N rows".

• ROWID: Use it when we want to identify and access a specific row physically (heap-organized tables only).

• UROWID: Use it when dealing with index-organized, foreign, or logical rows; it's more flexible than ROWID.

Feature	ROWNUM	ROWID	UROWID
Type	Pseudocolumn	Pseudocolumn / Data type	Data type
Meaning	Returns the position of a row in query result	Physical address of a row in a heap-organized table	Logical address of a row (works for all table types)
Data Type	Number (generated per query)	VARCHAR2 (usually 18 characters, base64-encoded)	VARCHAR2 (variable length, up to 4000 bytes)
Usage Scope	Used to limit rows in result sets	Can be used to uniquely identify and fetch rows	Generalized form of ROWID, supports IOTs, foreign tables
Persistence	Exists only during a query execution	Permanently associated with a row in a heap table	Can store logical row addresses across table types
Modifiable	No	No	Can be stored as a column or variable
Used In WHERE?	Yes, e.g. WHERE ROWNUM <= 10	Yes, e.g. WHERE ROWID = 'AAAC3zAABAAAYz9AAA'	Yes, e.g. WHERE some_urowid_column = :val
Index Support	No	Indexed indirectly (via row structure)	No direct index, but can be compared or stored
Applicable Tables	All SQL queries	Heap-organized tables only	All table types (heap, IOT, external, remote)
Example Use	SELECT * FROM emp WHERE ROWNUM = 1	SELECT * FROM emp WHERE ROWID = 'AAAC...'	SELECT * FROM emp_audit WHERE row_ref = :urowid_val
Readability	Simple integer	Encoded string (base64-like)	Longer encoded string (can exceed ROWID length)
Introduced In	Early Oracle versions	Original Oracle versions	Oracle 9i onwards

Example

-- Use ROWNUM to get first 5 employees
SELECT * FROM employees WHERE ROWNUM <= 5;

-- Use ROWID to fetch a row by physical ID
SELECT * FROM employees WHERE ROWID = 'AAAFfZAAFAAAACHAAA';

-- Declare and store UROWID from index-organized table
DECLARE
  my_rowid UROWID;
BEGIN
  SELECT rowid INTO my_rowid FROM iot_table WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE(my_rowid);
END;