ROWID

ROWID is a pseudocolumn that uniquely defines a single row in a database table.

The term pseudocolumn is used because you can refer to ROWID in the   WHERE clauses of a query as you would refer to a column stored in your database; the difference is you cannot insert, update, or delete ROWID values.

The ROWID value for a given row in a table remains the same for the life of the row, with one exception: the ROWID may change if the table is an index organized table and you change its primary key.

Syntax

ROWID

Usage

You can use a ROWID value to refer to a row in a table in the   WHERE clauses of a query. These values have several valuable uses:

  • They are the fastest way to access a single row.
  • They are a built-in, unique identifier for every row in a table.
  • They provide information about how the rows in a table are stored.

Some important notes about ROWID values:

  • Do not use ROWID as the primary key of a table.
  • The ROWID of a deleted row can later be reassigned to a new row.
  • A ROWID value is associated with a table row when the row is created.
  • ROWID values are unique within a table, but not necessarily unique within a database.
  • If you delete and re-import a row in a table, the ROWID may change.
  • The ROWID value for a row may change if the row is in an index organized table and you change the table’s primary key.

Using ROWID with JDBC

You can access ROWID with JDBC result sets; for example:

() ResultSet.getRowId(int);

You can also use ROWID in JDBC queries; for example:

() CallableStatement.setRowId(int, RowId);
() PreparedStatement.setRowId(int, RowId);

Examples

This statement selects the unique row address and salary of all records in the employees database in the engineering department:

splice> SELECT ROWID, DisplayName, Position
   FROM Players
   WHERE Team='Giants' and Position='OF';

ROWID                         |DISPLAYNAME             |POS&
------------------------------------------------------------
89                            |Greg Brown              |OF
93                            |Jeremy Packman          |OF
95                            |Jason Pratter           |OF
99                            |Reed Lister             |OF

4 rows selected

This statement updates column c in all rows in which column b equals 10:

UPDATE mytable SET c=100 WHERE rowid=(SELECT rowid FROM mytable WHERE b=10);

See Also