Primary Index, Primary AMP Index, and NoPI Objects - Teradata VantageCloud Lake

Lake - Database Reference
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549
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 or nonunique, partitioned or nonpartitioned, row distribution, primary index access to rows, performance considerations, duplicate row checking, and space usage.

  • 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 similar to those for selecting a PI, PA, or NoPI for a join index. Any considerations specific to join indexes are documented in Join Indexes.

An Object File System table has no primary index (NoPI). See Limitations Using Indexes with Object File System Tables for details.