15.00 - Restrictions on Number of Join Indexes Defined Per Base Table - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Restrictions on Number of Join Indexes Defined Per Base Table

The maximum number of secondary, hash, and join indexes that can be defined for a table, in any combination, is 32. This includes the system‑defined 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 “Importance of Consecutive Indexes for Value‑Ordered NUSIs” on page 485). You cannot define join, or any other, indexes on global temporary trace tables. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language Detailed Topics.

Suppose you have four tables, each with multiple secondary, hash, and join indexes defined on them:

  • 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 composite NUSI 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 composite NUSI 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.