Why Consecutive Indexes Are Important For Value-Ordered NUSIs - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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.