17.10 - Primary Index, Primary AMP Index, and NoPI Objects - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)
This section describes primary index (PI), primary AMP index (PA), and no primary index (NoPI) objects.
  • Primary indexed objects may be partitioned and the index may be unique or nonunique.

    The process of selecting a PI is given emphasis in this section. Other topics for primary indexes include unique versus nonunique, partitioned versus nonpartitioned, row distribution, primary index access to rows, various performance considerations, duplicate row checking, and space utilization.

  • Primary indexed objects must be column partitioned (with or without row partitioning). A PA is a nonunique index. Row distribution to AMPs and which AMPs to access for a PA are the same as for a PI. Accessing rows (or, more specifically, column partition values) on an AMP for a PA is the same as for a column-partitioned NoPI.
  • NoPI objects are tables and join indexes that neither have a primary index nor a primary AMP index. NoPI objects come in two forms.
    • Nonpartitioned NoPI objects
    • Column-partitioned NoPI objects (with or without row partitioning)

This section focuses on user tables, but the considerations for selecting a PI, PA, or NoPI for a table are largely identical to those for selecting a PI, PA, or NoPI for a join index. Any considerations specific to join indexes are documented in Join and Hash Indexes.