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

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Why Consecutive Indexes Are Important For Value‑Ordered NUSIs

The system automatically assigns incrementally increasing numbers to indexes when they are created on a table. This is not important externally except for the case of value‑ordered multicolumn NUSIs created with an ORDER BY clause, because they not only consume two of the allotted 32 index numbers from the pool, but those two index numbers are consecutive. One index of the consecutively numbered pair represents the column list, while the other index in the pair represents the ordering column.

To understand why this is important, 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 request aborts and the system returns an error message to you.

The reason the request aborts is that 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.