15.00 - Primary Indexed Tables, NoPI Tables, and Column-Partitioned Tables - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Primary Indexed Tables, NoPI Tables, and Column‑Partitioned Tables

Purpose of Primary Indexes

Teradata Database distributes tables horizontally across all AMPs on a system.

The system assigns primary‑indexed table rows to AMPs based on the value of their primary index (see “Row Allocation for Primary‑Indexed Tables” on page 235). The determination of which hash bucket, and hence which AMP the row is to be stored on, is made solely on the rowhash value of its primary index.

You can also create tables and join indexes that do not have a primary index. These tables are referred to as NoPI tables, column‑partitioned tables, and column‑partitioned join indexes. Nonpartitioned NoPI tables are generally used as staging tables for FastLoad and Teradata Parallel Data Pump Array INSERT bulk data loads. Teradata Database uses a slightly different mechanism to assign NoPI table rows to their AMPs (see “Row Allocation for Teradata Parallel Data Pump” on page 237).

Many retrievals from primary‑indexed tables also use the primary index, though others might use a secondary index, a hash or join index, a full‑table scan, or a mix of several different index types. Retrievals from NoPI tables often benefit from using secondary or join indexes to avoid full‑table scans.

Note that Teradata does not use the term primary index in the same way it is commonly used to describe a clustered index in an indexing system based on B+ trees.