Restrictions on Join Indexes
The following restrictions apply to all join indexes.
Excluding the primary index, you can define a maximum of 32 indexes on a table. These
32 indexes can be any combination of secondary, hash, and join indexes.
Each multicolumn NUSI that specifies an ORDER BY clause counts as two consecutive
indexes in this calculation.
You cannot define join indexes for a global temporary trace table. See “CREATE GLOBAL
TEMPORARY TRACE TABLE” in SQL Data Definition Language Detailed Topics.
No more than 64 columns from a table in a join index definition.
No more than 128 columns can be defined for a row compressed join index, 64 each for
the fixed and variable parts.
There is no limit on how many total columns can be defined in an uncompressed join
index other than system restrictions on the amount of SQL text required to define
Although the Optimizer substitutes only one multitable join index per referenced table
in a query, it also considers additional single-table join indexes for inclusion in
the join plan after the optimal multitable join index has been substituted and evaluated
for the plan.
You cannot include columns having XML, BLOB, CLOB, Period, ARRAY, or VARRAY data types
in any join index definition. Only the portions of JSON data that are extracted may
be used as part of a join index.
Additionally, you cannot create a join index that has a partitioned primary index
if that join index uses row compression (see “Compression Types Supported by Teradata Database” on page 695).