index_column_name - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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.