15.10 - BY column_name_2 - 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

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.