Unique and Nonunique Secondary Indexes - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Vantage processes USIs and NUSIs differently.

Consider the following statements that define a USI and a NUSI.

Secondary Index Statement
USI 
CREATE UNIQUE INDEX (customer_number) 
ON customer_table;
NUSI 
CREATE INDEX (customer_name) 
ON customer_table;

The following table highlights differences in the build process for the preceding statements.

USI Build Process NUSI Build Process
Each AMP accesses its subset of the base table rows. Each AMP accesses its subset of the base table rows.
Each AMP copies the secondary index value and appends the RowID for the base table row. Each AMP builds a spool file containing each secondary index value followed by the RowID for the indexed row.
Each AMP creates a Row Hash on the secondary index value and puts all three values onto the BYNET. For hash-ordered NUSIs, each AMP sorts the RowIDs for each secondary index value into ascending order.

For value-ordered NUSIs, the rows are sorted by NUSI value order.
The appropriate AMP gets the data and creates a row in the index subtable.

If the AMP gets a row with a duplicate index value, an error is reported.

 For hash-ordered NUSIs, each AMP creates a row hash value for each secondary index value on a local basis and creates a row in its portion of the index subtable.

For value-ordered NUSIs, storage is based on NUSI value rather than the row hash value for the secondary index.

Each row contains one or more RowIDs for the index value.

Consider the following statements that access a USI and a NUSI.

Secondary Index Statement
USI 
SELECT * FROM customer_table 
WHERE customer_number=12;
NUSI 
SELECT * FROM customer_table 
WHERE customer_name = 'SMITH';

The following table identifies differences for the access process of the preceding statements.

USI Access Process NUSI Access Process
The supplied index value hashes to the corresponding secondary index row. A message containing the secondary index value is broadcast to every AMP.
The retrieved base table RowID is used to access the specific data row. For a hash-ordered NUSI, each AMP creates a local row hash and uses that row to access its portion of the index subtable to see if a corresponding row exists.

Value-ordered NUSI index subtable values are scanned only for the range of values specified by the query.
The process is complete.

This is typically a two-AMP operation.

 If an index row is found, the AMP uses the RowID or value order list to access the corresponding base table rows.
  The process is complete.

This is an all-AMP operation, with the exception of a NUSI that is defined on the same columns as the primary index.
The NUSI is not used if the estimated number of rows to be read in the base table is equal to or greater than the estimated number of data blocks in the base table. Here, a full table scan is done, or, if appropriate, partition scans are done.