ROWID pseudocolumn

A ROWID pseudocolumn allows you to quickly locate a row in a table. A ROWID is an encoded representation of the primary key and is not physically stored in the database. This topic describes how to use the ROWID value of the ROWID pseudocolumn.

For each row in the database, the ROWID pseudocolumn returns the encoded primary key obtained from Base64 encoding.

Typically, a ROWID value uniquely identifies a row in the database. However, rows stored in different tables in the same cluster may have the same ROWID. The ROWID pseudocolumn supports the UROWID data type.

A ROWID value has the following important uses:

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

• It serves as the unique identifier of a row in a table.

When you use a ROWID value, note the following points:

• The ROWID pseudocolumn cannot be used as the primary key of a table.

• You can use the ROWID pseudocolumn in the SELECT and WHERE query clauses. However, these pseudocolumn values are not stored in the database. Therefore, you cannot insert, update, or delete ROWID values.

The following are some examples of how to use a ROWID value:

1. Retrieve a 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 a ROWID value to perform 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