The ROWID pseudo-column enables you to quickly locate a specific row in a table. The ROWID value is not actually stored. This topic describes how to use the ROWID pseudo-column.
Note
Starting from V4.6.3, OceanBase AI Database supports querying the ROWID pseudo-column and using ROWID to locate and update or delete data. This capability is an extension of OceanBase AI Database.
Overview
For each row in the database, the ROWID pseudo-column returns a code value that identifies the row. The ROWID value is encoded using Base64 and has a data type of VARCHAR. The source of the ROWID value encoding is determined by the table type:
- Tables with a primary key: The ROWID value is derived from the table's primary key (or partitioning key for partitioned tables). As long as the primary key remains unchanged, the ROWID value also remains unchanged.
- Heap tables without primary keys: The ROWID value is encoded from the tablet number where the row resides and the value of a hidden auto-increment column.
ROWID values have the following important uses:
- It is the fastest way to access a single row. You can use
ROWID = ?in theWHEREclause ofUPDATEorDELETEstatements to locate a specific row. - Serve as a unique identifier for rows in a table.
The following considerations apply when using ROWID values:
- Typically, a ROWID uniquely identifies a row in a database. However, rows from different tables stored in the same cluster can have the same ROWID.
- ROWID cannot be used as the primary key of a table.
- Although you can use the ROWID pseudo-column in the
SELECTandWHEREclauses of a query, you cannot insert, update, or delete ROWID values because these pseudo-column values are not actually stored in the database.
ROWID values are returned as Base64-encoded VARCHAR strings: for tables with a primary key, the returned ROWID value starts with an *; for heap tables without a primary key, the returned ROWID value does not have an * prefix. Applications can read and save the ROWID value using ResultSet.getString(), and later pass the ROWID string back as a parameter to locate and update or delete the corresponding row.
Supported scenarios
Query tables
You can query the ROWID pseudo-column in a SELECT statement and assign an alias to the ROWID:
SELECT ROWID AS __ds_rowid, c1, c2
FROM t
WHERE ...;
You can also use a table alias to qualify the ROWID, in which case the ROWID is bound to the corresponding base table:
SELECT t.ROWID AS __ds_rowid, t.c1, t.c2
FROM t;
In multi-table queries, ROWIDs specified by table aliases are bound to the corresponding base tables:
SELECT t1.ROWID, t2.ROWID, t1.c1, t2.c2
FROM t1
JOIN t2 ON t1.id = t2.id;
Query views
For a view, you can query the ROWID of the base table if the view is a single-table query. The ROWID of the view is the same as that of its base table:
CREATE VIEW v AS
SELECT c1, c2
FROM t
WHERE c1 > 10;
SELECT v.ROWID, v.c1
FROM v;
Update and delete data by ROWID
In the WHERE clause of UPDATE or DELETE statements, you can use ROWID values to locate a single row for update or deletion:
UPDATE t
SET c2 = ?
WHERE ROWID = ?;
DELETE FROM t
WHERE ROWID = ?;
Name resolution rules
rowid is not a reserved word, so users can define a real column named rowid for a table. Therefore, name resolution for ROWID follows this rule:
- If an identifier (
rowid,ROWID,RowId, or any other case combination) referenced in a statement corresponds to an existing real column with the same name in the table, it is parsed as that regular column. - This identifier is resolved as the ROWID pseudo-column of the base table only if no real column with the same name exists in the table.
Limitations
- Using ROWID in multi-target table
UPDATE(updating multiple tables with a single statement) is not currently supported. - External tables do not support queries by ROWID.
Usage examples
Query the ROWID value from table
t1.obclient> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR(20)); Query OK, 0 rows affected obclient> INSERT INTO t1 VALUES (1, 'a'), (2, 'b'); Query OK, 2 rows affected obclient> SELECT ROWID, c1, c2 FROM t1 WHERE c1 = 1; +--------------+----+------+ | ROWID | c1 | c2 | +--------------+----+------+ | *AAEEAQAAAA== | 1 | a | +--------------+----+------+ 1 row in setQuery the ROWID value from the heap table without a primary key,
t2.obclient> CREATE TABLE t2 (c1 INT, c2 VARCHAR(20)) ORGANIZATION = HEAP; Query OK, 0 rows affected obclient> INSERT INTO t2 VALUES (1, 'x'); Query OK, 1 row affected obclient> SELECT ROWID, c1, c2 FROM t2 WHERE c1 = 1; +--------------------------+----+------+ | ROWID | c1 | c2 | +--------------------------+----+------+ | sAAAAAAABAEAAAAAAAAAAQ== | 1 | x | +--------------------------+----+------+ 1 row in setPerform an
UPDATEoperation using the ROWID value. You can simply pass back the ROWID string obtained from the previous query as the condition.obclient> UPDATE t1 SET c2 = 'c' WHERE ROWID = '*AAEEAQAAAA=='; Query OK, 1 row affectedPerform a
DELETEoperation using the ROWID value.obclient> DELETE FROM t2 WHERE ROWID = 'sAAAAAAABAEAAAAAAAAAAQ=='; Query OK, 1 row affected