15.00 - Importance of Consecutive Indexes for Value-Ordered NUSIs - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Importance of Consecutive Indexes 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 composite value‑ordered NUSIs, because these indexes 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.

This includes the system‑defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints.

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 value‑ordered multicolumn NUSI.

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 composite value‑ordered NUSI.

You are still able to create 16 additional non‑value‑ordered NUSIs, single‑column value‑ordered NUSIs, USIs, hash indexes, or join indexes, but you cannot create any composite value‑ordered 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 value‑ordered multicolumn NUSI that failed previously.

3 Recreate the index you dropped to free the consecutive index numbers.

Here is a simple example of why value‑ordered composite NUSIs consume two consecutive index numbers from the total available number of 32.

First define a table on which a NUSI is to be defined.

     CREATE MULTISET TABLE transsupplier (
       suppkey INTEGER NOT NULL,
       seccode INTEGER NOT NULL,
       groupID INTEGER NOT NULL)
     UNIQUE PRIMARY INDEX ( suppkey );

Then you create an ordered composite NUSI on seccode and groupID, ordering on groupID.

     CREATE INDEX (seccode, groupID)  
     ORDER BY VALUES (groupID) ON transsupplier;

You find two rows in DBC.Indexes with an IndexType code of V (meaning a value‑ordered secondary index) and an IndexNumber of 4 for this composite NUSI. The third row, with an IndexType of I and an IndexNumber of 8, represents the fact that the groupID column is the ordering column of the two columns making up the composite NUSI, so a single ordered composite NUSI uses up two of the available 32 secondary, hash, or join indexes that can be defined for TransSupplier.

     SELECT s.CreateTimestamp, s.IndexType, s.IndexNumber, t.TVMName,
     FROM DBC.Indexes AS s, DBC.TVM AS t
     WHERE s.tableID = t.TVMID
     AND   s.databaseID = t.databaseID
     AND   s.fieldID = s.fieldID
     AND   s.tableID = s.tableID
     AND   s.databaseID = s.databaseID
     AND   s.indextype <> 'P'
     AND   t.TVMName = 'Transsupplier'
     ORDER BY s.createtimestamp DESC;

Note that Index Number 1 is reserved for the primary index of a table (which is suppkey in the example, but is not included in the report to simplify its meaning), and subsequent indexes are numbered beginning with 4 in increments of 4, so indexes 4 and 8 are consecutively numbered.

Suppose you had instead created the following ordered single‑column NUSI on TransSupplier, this time on groupID only, ordering on groupID.

     CREATE INDEX (groupID)  
     ORDER BY  values (groupID) ON transsupplier;

You then run the same query against DBC.Indexes.   

     SELECT i.CreateTimestamp, i.IndexType, i.IndexNumber, t.TVMName,
     FROM DBC.Indexes AS i, DBC.TVM AS t, DBC.TVFields AS f
     WHERE i.tableID = t.TVMID
     AND   i.databaseID = t.databaseID
     AND   i.fieldID = f.fieldID
     AND   i.tableID = f.tableID
     AND   i.databaseID = f.databaseID
     AND   i.indextype <> 'P'
     AND   t.TVMName = 'Transsupplier'
     ORDER BY i.createtimestamp DESC;

The query produces a report something like this, where you find only one row in DBC.Indexes with an IndexType code of V for this single‑column NUSI, meaning that only one of the possible 32 secondary, hash, and join indexes has been consumed by the creation of this NUSI.


Index number 1, for primary index column suppkey, is not displayed in the report in order to simplify the point of the example.