Teradata Database processes USIs and NUSIs very differently.
Consider the following statements that define a USI and a NUSI.
CREATE UNIQUE INDEX (customer_number) ON customer_table;
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.
SELECT * FROM customer_table WHERE customer_number=12;
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.