ROWID

Purpose

Every row of a base table in the database has a unique address, the so-called ROWID. Read access to this address can be obtained through the ROWID pseudo column which has the data type DECIMAL(36,0).

Usage Notes

The ROWID column can be used in, among others, the following SQL constructs:

• In all the column lists of SELECT statements.
• In VIEW definitions (see also CREATE VIEW) in which case you have to define a column alias
• In conditions of INSERT, UPDATE, MERGE, and DELETE statements.

The ROWIDs of a table are managed by the DBMS. It ensures that the ROWIDs within a table are distinct - in contrast, it is quite acceptable for ROWIDs of different tables to be the same. Using DML statements such as INSERT, UPDATE, MERGE, DELETE or TRUNCATE, all the ROWIDs of the relevant tables are invalidated and reassigned by the DBMS. This compares to structural table changes such as adding a column, which leaves the ROWIDs unchanged.

The ROWID pseudo column is only valid for base tables, not for views. An example of using ROWIDs would be the targeted deletion of specific rows in a table, for example, to restore the UNIQUE attribute.

Examples

SELECT ROWID, i FROM t;

Result

-- Restore the uniqueness of i
DELETE FROM t WHERE NOT EXISTS (
  SELECT r FROM (SELECT MIN(ROWID) r FROM t GROUP BY i) h
  WHERE t.ROWID=h.r);
  
CREATE VIEW v AS SELECT ROWID r, i FROM t;
SELECT * FROM v;

Result