17.10 - index_column_name - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

The names of one or more columns whose values are to be indexed.

You can define a nonunique secondary index (NUSI) on the same columns included in a primary AMP index.

You can define a NUSI, but not a USI, on a LOB UDT column.

Both a NUSI and a USI can contain row level security constraint columns, but they are not required.

You can specify a user-defined column named partition or partition#L n, where n ranges from 1 through 62. You cannot specify the system-derived columns PARTITION or PARTITION#L n in the column name list.

You can specify up to 64 columns for the new index. The index is based on the combined values of each column. Unless you specify the ORDER BY clause, the index is hash-ordered on all its columns.

If you specify multiple columns, the index is created on the combined values of each column named. A combined maximum of 32 secondary, hash, and join indexes can be created for one table. This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints for nontemporal tables and the single-table join indexes used to implement PRIMARY KEY and UNIQUE constraints for temporal tables. For details, see Teradata Vantage™ - Temporal Table Support, B035-1182.

Each multicolumn NUSI defined with an ORDER BY clause counts as two consecutive indexes in this calculation.

See CREATE INDEX in Teradata Vantage™ - SQL Data Definition Language Detailed Topics, B035-1184.

An index on a geospatial column can only contain that column.

The number of system-defined single-table join indexes contributed by PRIMARY KEY and UNIQUE constraints on temporal table columns is included in the combined limit of 32 secondary, hash, and join indexes per base data table.

Multiple indexes can be defined on the same columns as long as each index differs in its ordering option (VALUES versus HASH).

You cannot include columns with the JSON data type in an index.