16.10 - Secondary Considerations for Selecting a Primary Index - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

When you select primary indexes for your tables, there are several factors to keep in mind that are related to the three principle selection criteria described in Principal Criteria for Selecting a Primary Index.

These considerations include the following factors.

  • Performance (see Performance Considerations for Primary Indexes)
  • Duplicate row checks on NUPIs for multiset tables (see Duplicate Row Checks for NUPIs)
  • Space management

    Primary indexes in Teradata Database are not stored in an index subtable, but are just a column set that is designated as the primary index at the time the table is created (or, rarely, when its primary index is altered), space management issues that are unique to primary indexes are minimal.

    The only factors are the following two:

    • The partition number field in the row header (see Base Table Row Format), where each row of a table having a partitioned primary index requires an additional 2 or 8 bytes (partitioned table rows are 4 bytes wider if multivalue compression is specified for the table) in the row header to indicate the partition number to which the row belongs.

      If a table does not have a partitioned primary index, then the partition number for each row is assumed to be 0, and nothing is stored in the row header to represent a partition number other than flag bits that occur every row (see Storage and Other Overhead Considerations for Partitioning).

    • None of the columns in the primary index column set can be compressed (see Restrictions on Primary Indexes and Primary AMP Indexes).

Each of these considerations is described in further detail in additional topics in this chapter.