Usage of Indexes and Constraints | VantageCloud Lake - Secondary Indexes - Teradata Vantage

You can define up to 32 secondary and join indexes (in any combination) for 1 table, and secondary indexes can be either unique (USI) or nonunique (NUSI). A multicolumn NUSI that specifies an ORDER BY clause counts as two consecutive indexes in this calculation.

This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.

UNIQUE and PRIMARY KEY constraints that do not define the primary index for a table also count against this total because these constraints are defined internally as USIs for nontemporal tables. These constraints are defined internally as single-table join indexes, so any UNIQUE or PRIMARY KEY constraint counts equally against the maximum of 32 secondary or join indexes per table.

You cannot include BLOB, CLOB, BLOB-based UDT, CLOB-based UDT, Period, or Geospatial columns in a secondary index column set.

You cannot include the system-derived PARTITION or PARTITION#L n columns in any secondary index definition.

You can include row-level security columns in a secondary index definition.

You can add secondary indexes to an existing table using the CREATE INDEX statement.

When a nonprimary index uniqueness constraint is created for a table, Vantage implements the index internally as a USI.

Consider adding such constraints to your tables when they facilitate row access or joins. All manner of database constraints are often useful for query optimization, and the richer the constraint set specified for a database, the more opportunities exist to enhance query optimization.

The likely benefits of adding uniqueness constraints are not restricted to multiset NUPI tables. If you create a SET NUPI table, the system performs duplicate row checks by default unless you place a uniqueness constraint on the table. Unique constraint enforcement is often less a less costly method of enforcing row uniqueness than system duplicate row checks.

Avoid defining a uniqueness constraint on the primary or alternate key of a multiset NUPI table only to enforce row uniqueness, because MultiLoad and FastLoad do not support target tables with uniqueness constraints except those on the primary index.

You can avoid the MultiLoad and FastLoad problems associated with indexes and triggers by using one of these utilities (typically FastLoad) to load rows into a staging table that is identical to the target table except for its lack of constraints, triggers, or indexes. After loading the data into that table, you can then use either INSERT … SELECT or MERGE with error logging to move the data into the target table that has the desired constraints and indexes. See CREATE ERROR TABLE Usage Notes for details.

Furthermore, a USI imposes a performance cost because its index subtables must be maintained by the system each time the column set in its base table is updated.

ALWAYS GENERATED … NO CYCLE identity columns can be a better choice for the task of enforcing row uniqueness in multiset NUPI tables because they are supported by both MultiLoad and FastLoad. However, identity columns cannot be used to facilitate row access or joins.

There are reasons to avoid duplicate-row-management approaches that avoid using declarative constraints.

For example, if you define nonprimary index uniqueness constraints on a table, you must drop those indexes before using MultiLoad or FastLoad to load rows into that table and recreate the indexes after the load operation completes.

If the MultiLoad or FastLoad operation loads any duplicate rows into the table, you cannot recreate a uniqueness constraint on the table. You must first remove the duplicates from the table.

USIs are rarely recommended for tables in workloads that support OLTP applications.

Each secondary index has an associated secondary index subtable. Each row in a secondary index subtable contains the indexed column value and 1 or more row IDs that point to the base table data rows containing that value (see Secondary Index Access Summarized by Example for details of USI and NUSI subtable row layouts).

Secondary indexes can greatly reduce table search time for so-called set selections (this term, which refers to selecting multiple rows, is technically a misnomer because a set can also contain 0 or 1 elements). However, update operations (including delete, insert, and update) on tables with secondary indexes require more I/O to process because of the required transient journaling, the possibility that secondary index subtables must be updated (if the indexed column set was updated), fallback table updates, and so on.

USI subtables are hashed and are efficient for selecting a single row or a small number of rows.

NUSI bit mapping provides efficient retrieval when complex conditional expressions are applied to large tables. Weakly selective NUSIs can be overlapped and bit mapped to achieve greater selectivity.

Specifying the ALL option may also require additional index storage space.

You cannot specify multiple NUSIs that differ only by the presence or absence of the ALL option.

Test the performance effects of a secondary index before implementing it.

For more information about secondary indexes, see Secondary Indexes.