15.10 - BY column_name_2 - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Content Type
Programming Reference
Publication ID
English (United States)

An optional, explicitly specified column set on which the hash index rows are distributed across the AMPs.

A column in a hash index cannot have a Period, Geospatial, BLOB, CLOB, JSON, LOB UDT, VARIANT_TYPE, or ARRAY/VARRAY data type.

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

The column set you specify must be drawn from the column set specified by column_name_1.

If a hash index is defined without either a BY clause or an ORDER BY clause, then Teradata Database distributes its rows by the hash of the primary index of its base table if the primary index is defined on a UDT column.

You cannot specify the same column twice in the column_name_2 list.

You cannot qualify any of the column names in the column_name_2 list.

However, you can specify a user-defined column named partition.

You cannot compress column values for a hash index.

This column set acts as the primary index for the hash index: its rows are hashed on the column set that you specify.

If you do not specify this column set, then the hash index rows are hashed on the primary index column set of the base table on which the index is defined, which makes it hash-local with respect to its base table.

You cannot specify a partitioned column set. This means that you cannot create a PPI for hash indexes.