16.10 - Secondary Index Build and Access Operations Summarized - Teradata Database

Teradata Database Design

Teradata Database
Release Number
Release Date
June 2017
Content Type
User Guide
Publication ID
English (United States)

The following table summarizes how the system processes USIs and NUSIs, based on the following simple SQL requests.

  • Supporting USI build request.
         CREATE UNIQUE INDEX (customer_number)
         ON customer_table;
  • Supporting NUSI build request.
         CREATE INDEX NUSI_name (customer_name)
         ON customer_table;
  • Supporting USI access request.
         SELECT *
         FROM customer_table
         WHERE customer_number=12;
  • Supporting NUSI access request.
         SELECT *
         FROM customer_table
         WHERE customer_name=‘SMITH’;

Secondary Index Build Process Table

Stage Process
Build Access
1 Each AMP accesses its subset of the base table rows.   The supplied index value hashes to the corresponding secondary index row and the base table rowID is located. A message containing the secondary index value and its row hash for an equality condition, or a condition on an index value, is broadcast to every AMP.
2 Each AMP copies the secondary index value and appends the rowID for the base table row. Each AMP builds a spool containing each secondary index value found followed by the rowID for the row it came from. The retrieved base table rowID is used to access the specific data row. For an equality condition, the system does a hash lookup to find the rows that satisfy the predicate.

For an inequality condition, there are two possibilities.

  • A full subtable scan.

    If this is too costly, the Optimizer does not use it, but it is a legitimate possibility.

  • A hash lookup.

    This is a good choice when a predicate specifies a small range or if the statistics indicate that few rows satisfy the predicate.

    It is also a good choice if the base table is very wide, but the NUSI is very narrow.

Otherwise, the system attempts to combine several NUSIs using bit mapping. Failing that, no index is used, and the system does a full-table scan.

For a hash-ordered NUSI, each AMP uses the row hash and uses it to access its portion of the index subtable to see if a corresponding index row exists.

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

On a range predicate, the system can enter the index at its lowest value and scan to its last value. This cannot be done with ORed ranges, so a full subtable scan must be used.

If not a range predicate, then the system must scan the entire index.

3 Each AMP creates a Row Hash on the secondary index value and puts the value, the hash, and the row ID values onto the BYNET.

The receiving AMP puts these values into the index subtable, sorts on the row hash, and adds a uniqueness value.

For hash-ordered NUSIs, each AMP sorts the row hash and row values for each secondary index value into ascending order.

For value-ordered NUSIs, the rows are sorted in NUSI value order.

The process is complete.

This is typically a two-AMP operation.

If an index row is found, the AMP uses the base table rowID set to access the corresponding base table rows.
4 The process is complete. Rows having the same row value are collapsed into one index row or multiple rows if the row length limit is reached. The process is complete.
5 The process is complete.