Secondary Considerations for Selecting a Primary Index - Teradata Database

Secondary Considerations for Selecting a Primary Index

When you select primary indexes for your tables, there are several factors to keep in mind that are not orthogonal to the three principle selection criteria described in “Principal Criteria for Selecting a Primary Index” on page 356.

These considerations include the following factors.

Performance (see “Performance Considerations for Primary Indexes” on page 389)

Duplicate row checks on NUPIs for multiset tables (see “Duplicate Row Checks for NUPIs” on page 392)

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” on page 671), 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 multi‑value 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” on page 279).

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

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