Unique Primary Indexes (UPIs) guarantee uniform distribution of table rows.
Nonunique Primary Indexes (NUPIs) can cause skewed data. While not a guarantor of uniform row distribution, the degree of uniqueness of the index determines the degree of uniformity of the distribution. Because rows with the same PI value are distributed to the same AMP, columns with a small number of distinct values that are repeated frequently are not good PI candidates.
The most efficient way to access data in a table is through the PI. When choosing a PI, consider the following:
- Distribution of data across the AMPs.
- Common usage pattern of the table.
If you do not explicitly specify a primary index when you create a table, Analytics Database uses the first column as the nonunique primary index.
NoPI tables are typically used as staging tables to allow faster data loading.