15.00 - Secondary Index Storage - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Secondary Index Storage

Secondary indexes are stored in secondary index subtables.

Teradata Database distributes unique secondary index rows on the rowhash for the index. The determination of which hash bucket the row belongs to, and hence which AMP the row is to be stored on, is made on the value of the unique secondary index. Because the rowhash bucket for a secondary index is usually different from the rowhash bucket for the referenced row, unique secondary indexes are generally stored on a different AMP than the row they point to.

Nonunique secondary indexes are not hash‑distributed and are always stored on the same AMP as the rows they point to.

Unique secondary indexes are always stored in hash order on an AMP, while nonunique secondary indexes can be stored either in hash order or in value order on an AMP, depending on their intended use.

Related Topic

For additional usage information and design tips about secondary indexes, see Chapter 10: “Secondary Indexes”.