Importance of Consecutive Indexes for Value-Ordered NUSIs - Teradata Vantage

The system assigns incrementally increasing numbers to indexes created on a table. This is not important externally except for composite value-ordered NUSIs, which consume two consecutive index numbers of the allotted 32 index numbers in the pool. One index of the consecutively numbered pair represents the column list, and the other 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 drop the even-numbered indexes, 16 odd-numbered index numbers are available to assign to indexes created later.

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, 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,
  name    CHAR(25) CHARACTER SET LATIN CASESPECIFIC 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.Indexeswith 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 or join indexes that can be defined for TransSupplier.

SELECT s.CreateTimestamp, s.IndexType, s.IndexNumber, t.TVMName,
       s.FieldName
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;

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,
       f.FieldName
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 and join indexes has been consumed by the creation of this NUSI.