15.00 - Restrictions on Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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 them.
  • 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).