15.00 - Nonunique Primary Index Considerations - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Nonunique Primary Index Considerations

The following factors describe the general performance considerations associated with NUPIs.

  • At best, NUPIs distribute table rows evenly across the AMPs and hash values.
  • At worst, NUPI table row distribution can be skewed in various ways (see “SQL Scripts For Detecting Skew” on page 425) and, if so, decrement both retrieval and update performance.

  • NUPIs at best can effect good retrieval and update performance.
  • NUPI values can involve more than one base table row.
  • NUPI retrievals can require spool files.
  • Duplicate NUPI values hash to the same AMP and often are stored in the same data block.
  • If all the rows for a duplicate NUPI hash value fit into a single data block, then only 1 or 2 I/Os are required to store or access the entire set.
  • 2 I/Os are only required when the cylinder index is not cached.

  • If duplicate rows are excluded because the table is defined as SET and has no uniqueness constraint, then the system must make a duplicate row check for every table row inserted or updated.
  • See “Duplicate Row Checks for NUPIs” on page 444 and “Using Unique Secondary Indexes to Enforce Row Uniqueness” on page 457 for further details.