ROWID is a pseudocolumn that uniquely defines a single row in a
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 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.
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
- Do not use
ROWIDas the primary key of a table.
ROWIDof a deleted row can later be reassigned to a new row.
ROWIDvalue is associated with a table row when the row is created.
ROWIDvalues are unique within a table, but not necessarily unique within a database.
- If you delete and re-import a row in a table, the
ROWIDvalue 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:
You can also use
ROWID in JDBC queries; for example:
() CallableStatement.setRowId(int, RowId); () PreparedStatement.setRowId(int, RowId);
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
UPDATE mytable SET c=100 WHERE rowid=(SELECT rowid FROM mytable WHERE b=10);