16.10 - Unique and Nonunique Secondary Indexes - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Published
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)
Last Update
2018-04-25

Teradata Database processes USIs and NUSIs very 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 found followed by the RowID for the row it came from.
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 receives the data and creates a row in the index subtable.

If the AMP receives 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 it 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 always 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; in this case, a full table scan is done, or, if appropriate, partition scans are done.