15.10 - column_name_1 - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Name of a column to be contained in the hash index.

The maximum number of columns you can specify for a hash index, including the columns implicitly added to the index upon creation, is 64.

All columns defined in the column_name_1 list must be from the base table on which the hash index is defined.

If you specify more than one column name, 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.

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

Just as it does for secondary index definitions, Teradata Database extends the hash index definition transparently with additional elements that make it possible to provide an access path to the corresponding rows in the base table on which it is defined.

A column in a hash index cannot have any of the following data types:
  • ARRAY/VARRAY
  • BLOB
  • CLOB
  • Geospatial
  • JSON
  • LOB UDT
  • Period
  • VARIANT_TYPE

You cannot compress column values for a hash index.

If the base table is a row level security table, you must include all security constraint columns in the index definition.

You cannot qualify the columns in the column_name_1 list.

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

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

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