17.10 - Why Consecutive Indexes Are Important For Value-Ordered NUSIs - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)

Value-ordered multicolumn NUSIs created with an ORDER BY clause consume two consecutive index numbers of the allotted 32 index numbers. One index of the consecutively numbered pair represents the column list, while the other index in the pair represents the ordering column.

Consider the following scenario:

  1. You create 32 indexes on a table, none of which is value-ordered.
  2. You drop every other index on the table, meaning that you drop either all the odd-numbered indexes or all the even-numbered indexes.

    For example, if you had dropped all the even-numbered indexes, there would now be 16 odd-numbered index numbers available to be assigned to indexes created in the future.

  3. You attempt to create a multicolumn NUSI with an ORDER BY clause.

    The system returns an error message because two consecutive index numbers were not available for assignment to the ordered multicolumn NUSI.

    You are still able to create 16 additional value-ordered single column NUSIs, non-value-ordered NUSIs, USIs, hash indexes, or join indexes, but you cannot create any ordered multicolumn NUSIs.

To work around this problem, perform the following procedure:

  1. Drop any index on the table.

    This action frees 2 consecutive index numbers.

  2. Create the ordered multicolumn NUSI that failed previously.
  3. Recreate the index you dropped to free the consecutive index numbers.