Archives of the TeradataForum

Message Posted: Tue, 23 Sep 2014 @ 23:17:53 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

This is from our Temporal Table Support manual:

Indexes for Primary Key and Unique Constraints

Because of the way rows are duplicated as a result of modifications to temporal tables, most primary key and unique constraints defined on temporal tables are implemented by means of system-defined join indexes (SJIs). These indexes enforce the uniqueness on an appropriate subset of rows, according to whether the constraint is current, sequenced, or nonsequenced.

Example

A current unique constraint on a bitemporal table causes an SJI to be created and maintained automatically from selected columns of the temporal table. The primary index of the SJI is the constrained column or columns of the temporal table. The valid-time and transaction-time columns are selected using an appropriately qualified WHERE clause that limits the rows in the index to current and future rows:

     CREATE JOIN INDEX tablename_TJInumber
     AS SELECT ROWID, ConstrainedColumn, VTColumn, TTcolumn
     FROM tablename
     WHERE END(VTColumn >= CURRENT_DATE - INTERVAL '2' DAY AND END(TTcolumn) IS UNTIL_CLOSED
     PRIMARY INDEX
     (ConstrainedColumn);

INTERVAL '2' DAY is required because current rows could be inserted in a time zone that is up to two days prior to the date for the time zone in which the index is created. SJIs are created in the same database as the constrained temporal table.

They are named automatically by the system using a naming convention of tablename_TJInumber, where tablename is the name of the temporal table for which a PK or unique constraint has been defined, and number is the index ID of the constraint, a unique number that identifies the SJI.

Note: These indexes are created, maintained, and deleted automatically as needed by the system. They should not be directly modified or deleted.

The PK or unique constraint is not allowed if the associated SJI would cause the maximum permitted number of secondary indexes to be exceeded for the table.

As time passes, the values of CURRENT_DATE and CURRENT_TIMESTAMP differ from the values that were used when the SJI was created. Because of this, current and future rows in SJIs, over time, become history rows, and therefore no longer needed in the index to enforce the current constraint.

Use the ALTER TABLE TO CURRENT statement periodically to update SJIs and PPIs created for temporal tables. ALTER TABLE TO CURRENT transitions history rows out the SJIs created for current primary key and unique constraints. For more information on ALTER TABLE TO CURRENT, see SQL Data Definition Language.

Because nonsequenced constraints treat temporal columns as if they were nontemporal, a nonsequenced valid-time PK or unique constraint on a valid-time table is implemented automatically by making the constrained column a USI. For bitemporal tables, a PK or unique constraint must be limited to rows that are open in transaction time, so an SJI is used. The SJI uses a WHERE clause to select only the open rows from the transaction-time column.

Note: Because identity columns are not allowed in join indexes, PK and unique constraints cannot be defined on identity columns in temporal tables.

Hope this helps.

-- garyZ
Gary Zimmerman

	<Prev	Next>		<<First	<Prev	Next>	Last>>