ROWID Columns - Teradata Database

ROWID Columns

Every Teradata Database base table, join index, and hash index has a system-generated column named ROWID. The fields in this column contain the RowID value for their rows.

FOR a partitioned table or join index that has …	The data type for ROWID values is …
65,535 or fewer combined partitions	BYTE(10)
> 65,535 combined partitions	BYTE(16)

The system-derived column ROWID contains the internal row identifier associated with a row of a base table or join index.

With one exception, there is nothing different for a column‑partitioned table or join index. The exception is that the column partition is always 1 for the internal partition number in the ROWID of a column‑partitioned table or join index. If you only specify column partitioning when you create a column‑partitioned table or join index and do not specify the ADD option, the table or index always uses 2-byte partitioning.

As a user, you can only specify the ROWID keyword in a CREATE JOIN INDEX request to enable non‑covering join indexes to join with base table columns to optimize query processing (see “Partial Query Coverage” on page 455, “Restrictions on Partial Covering by Join Indexes” on page 526, and “CREATE JOIN INDEX” in SQL Data Definition Language). You cannot specify ROWID in any other context at any time.

The rules for using the ROWID keyword in a CREATE JOIN INDEX request are as follows.

You can optionally specify the ROWID for a base table in the select list of an nonpartitioned or PPI join index definition.

The select list for a column-partitioned join index must include the system‑derived column ROWID of the base table, and it must be specified with an alias.

If you reference multiple tables in the join index definition, then you must fully qualify each ROWID specification.

You can reference an alias for ROWID, or the keyword ROWID itself if no alias name has been specified for it, in the primary index definition or in a secondary index defined for the join index in its index clause.

This does not mean that you can reference a ROWID or its alias in the DDL you use to create a secondary index defined separately from CREATE TABLE using a CREATE INDEX request (see “CREATE INDEX” in SQL Data Definition Language) after the join index has been created.

If you reference a ROWID alias in the select list of a join index definition, then you can also reference that correlation name in a CREATE INDEX request that creates a secondary index on the join index.

Aliases are required to resolve any column name or ROWID ambiguities in the select list of a join index definition. An example is the situation where you specify ROWID for more than one base table in the index definition.

Aliases are mandatory for a ROWID specification in a column‑partitioned join index.

If you attempt to use the ROWID keyword in any other context, such as selecting or deleting from, updating, or inserting rows into base tables, views, or derived tables Teradata Database aborts the request and returns an error to the requestor.

These rules apply equally to a join index defined with a partitioned primary index and to a column‑partitioned join index whether the partitioning is single‑level or multilevel.