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. |