16.20 - Why Consecutive Indexes Are Important For Value-Ordered NUSIs - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
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.