CREATE HASH INDEX Syntax Elements - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™
database_name
user_name
Name of the containing database or user for hash_index_name if other than the current database or user.
hash_index_name
Name given to the hash index created by this request.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
Hash index names conform to the rules for naming tables, including explicit qualification with a database or user name.
A hash index need not be defined in the same database as the base table represented in its definition.
column_name_1
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, Vantage 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:
  • BLOB
  • CLOB
  • ARRAY/VARRAY
  • VARIANT_TYPE
  • Period
  • LOB UDT
  • Geospatial
  • JSON
  • DATASET
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#L n 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 Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
table_name
Name of the base table on which the hash index is defined.
You cannot define a hash index on any of the following database objects:
  • Column-partitioned tables
  • Global temporary tables
  • Global temporary trace tables
  • Hash indexes
  • Join indexes
  • Journal tables
  • NoPI tables
  • Queue tables
  • Views (neither recursive nor nonrecursive)
  • Volatile tables
You cannot create a hash index on a PPI table if the partitions for the table are defined on PERIOD bound functions.
column_name_2
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 data type of BLOB, CLOB, LOB UDT, VARIANT_TYPE, ARRAY/VARRAY, Period, Geospatial, JSON, or DATASET.
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 Vantage 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.
ORDER BY
Row ordering on each AMP: either value-ordered or hash-ordered.
If you do not specify an ORDER BY clause, then the hash index rows have the same ordering as that specified for the base table on which the index is defined.
You must specify an ORDER BY clause that includes an explicit column list for a hash index defined on a partitioned primary index table.
If you specify a BY clause, you must specify an ORDER BY clause for the hash index definition.
If you specify neither a BY clause nor an ORDER BY clause, Vantage distributes the hash index rows by the hash of the primary index column set of the base table on which they are defined.
As a result, the hash index is AMP-local and its rows are ordered by the hash of those columns. This is also true if the primary index of the base table is defined on a UDT column.
If you specify the system-derived PARTITION column or the system-derived PARTITION#L n columns, where n is a number from 1 through 62, the request returns an error to the requestor.
You can specify a user-defined column named partition or partition#Ln.
If you specify ORDER BY VALUES with a column_name_3 list, the column_name_3 list is limited to a single numeric column with a size of 4 or fewer bytes.
In this case, the primary index of the base table must consist of a single column having a length of 4 or fewer bytes.
The base table primary index column does not have to be included in the column_name_1 set.
You cannot specify ORDER BY VALUES on a UDT column.
The hash index rows are stored in ascending order by the values of the column specified as the primary index of the base table.
The values specified for the column_name_3 list must have one of the following data types:
  • BYTEINT
  • DATE
  • DECIMAL
  • INTEGER
  • SMALLINT
If you specify ORDER BY HASH, you must specify a BY clause and the columns specified for the column_name_2 and column_name_3 sets must be identical.
The hash index rows are ordered on the row hash value of these columns.
You can specify ORDER BY HASH on a UDT column.
If you specify ORDER BY without specifying either the HASH or the VALUES keywords, Vantage orders the rows by the default, which is VALUES.
In this case, you must specify a column_name_3 list.
See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 for complete documentation of the ORDER BY clause.
VALUES
Value-ordering for the ORDER BY column.
Select VALUES to optimize queries that return a contiguous range of values, especially for a workload that requires a covering index or that commonly uses a nested join in its query plans.
You cannot specify ORDER BY VALUES on a UDT column.
HASH
Hash-ordering for the ORDER BY column.
Hash-ordering for the ORDER BY column.
column_name_3
Column set on which the index is to be ordered.
If you specify ORDER BY VALUES, then you can specify only one column for column_name_3.
The specified column set must be a subset of column_name_1.
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.
You cannot specify the same column twice in the column_name_3 list.
You cannot compress column values for a hash index.
You cannot order an index by a Period, BLOB, CLOB, LOB UDT, Geospatial, VARIANT_TYPE, or ARRAY/VARRAY column.