The maximum number of secondary 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). You cannot define join, or any other, indexes on global temporary trace tables.
Suppose you have four tables, each with multiple secondary and join indexes defined on them:
| Table | Secondary Indexes | Join Indexes |
|---|---|---|
| Table_1 | 32 | 0 |
| Table_2 | 16 | 16 |
| Table_3 | 20 | 12 |
| Table_4 | 0 | 32 |
Each combination is valid, but all operate at the boundaries of the defined limits.
If a secondary index defined on Table_1, Table_2, or Table_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 fails. 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 can define only 8 on Table_2, for example, if you also defined 16 join indexes on the table.