ROWID pseudo-column

The ROWID pseudo-column provides the ability to quickly locate a specific row in a table. The ROWID value is derived from the primary key and is not stored in the database. This topic describes how to use the ROWID value.

For each row in the database, the ROWID pseudo-column returns the encoded primary key, which is converted using Base64 encoding.

Typically, the ROWID value uniquely identifies a row in the database. However, rows in different tables within the same cluster can have the same ROWID. The ROWID pseudo-column supports the UROWID data type.

The ROWID value has the following important uses:

• It is the fastest way to access a single row.

• It serves as a unique identifier for rows in a table.

When using the ROWID value, keep in mind the following considerations:

• You cannot use the ROWID as the primary key of a table.

• Although you can use the ROWID pseudo-column in the SELECT and WHERE clauses of a query, you cannot insert, update, or delete ROWID values because these pseudo-column values are not actually stored in the database.

Here are some examples of using the ROWID value:

1. Query the ROWID value from the employees table.

   obclient> SELECT ROWID, last_name FROM employees WHERE department_id = 20;
   +-------------------+-----------+
   | ROWID             | LAST_NAME |
   +-------------------+-----------+
   | *AAIKAQAAAAAAAAA= | xxx       |
   +-------------------+-----------+
   1 row in set
   

2. Use the ROWID value for an UPDATE operation.

   obclient> UPDATE employees SET last_name = 'yyy' WHERE ROWID = '*AAIKAQAAAAAAAAA=';
   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   
   obclient> SELECT last_name, department_id FROM employees;
   +-----------+---------------+
   | LAST_NAME | DEPARTMENT_ID |
   +-----------+---------------+
   | yyy       |            20 |
   +-----------+---------------+
   1 row in set