This topic lists several restrictions on hash index definitions. For complete details about hash index syntax, see "CREATE HASH INDEX" in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
Restrictions for NoPI Tables
You cannot create a hash index on a NoPI table.
Restrictions on Number of Hash Indexes Defined Per Base Table
The maximum number of secondary, hash, and join indexes that can be defined for a table is 32, in any combination. This includes the system-defined unique secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.
Each composite NUSI that specifies an ORDER BY clause counts as 2 consecutive indexes in this calculation (see Value-Ordered NUSIs and Range Conditions). You cannot define hash, or any other, indexes on global temporary trace tables. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
- table_1 has 32 secondary indexes and no hash or join indexes.
- table_2 has 16 secondary indexes, no hash indexes, and 16 join indexes.
- table_3 has 10 secondary indexes, 10 hash indexes, and 12 join indexes.
- table_4 has no secondary or hash indexes, but has 32 join indexes.
Each of these combinations is valid, but they all operate at the boundaries of the defined limits.
Note that if any of the secondary indexes defined on tables 1, 2, or 3 is a NUPI defined with an ORDER BY clause, the defined limits are exceeded, and the last index you attempt to create on the table will fail. Because each NUPI defined with an ORDER BY clause counts as 2 consecutive indexes in the count against the maximum of 32 per table, you could define only 8 of them on table_2, for example, if you also defined 16 join indexes on the table.
Restrictions on Number of Columns Per Referenced Base Table
The maximum number of columns that can be specified in a hash index is 64.
Restrictions on the Use of the System-Derived PARTITION[#L n] Column in a Hash Index Definition
You cannot use the system-derived PARTITION[#L n] column in the definition of a hash index.
Restriction on Number of Hash Indexes Selected Per Base Table
The Optimizer can use several hash indexes for a single query, selecting one or more multitable join indexes as well as additional hash indexes for its join plan. The hash indexes selected depend on the structure of the query, and the Optimizer might not choose all applicable hash indexes for the plan. Always examine your EXPLAIN reports to determine which hash indexes are used for the query plans generated for your queries. If a hash index you think should have been used by a query was not included in the query plan, try restructuring the query and then EXPLAIN it once again.
The join planning process selects a multitable join index to replace any individual table in a query when the substitution further optimizes the query plan. For each such table replaced in the join plan by a multitable join index, as many as two additional hash indexes can also be added if their inclusion reduces the size of the relation to be processed, provides a better distribution, or offers additional covering.
The limit on the number of hash indexes substituted per individual table in a query is enforced to limit the number of possible combinations and permutations of table joins in the Optimizer search space during its join planning phase. The rule helps to ensure that the optimization is worth the effort: in other words, that the time spent generating the query plan does not exceed the accrued performance enhancement.