16.10 - Uniform Distribution of Data and Optimal Access Considerations - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
klx1480972732157.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval

When choosing the primary index for a table, there are two essential factors to keep in mind: uniform distribution of the data and optimal access.

With respect to uniform data distribution, consider the following factors:

  • The more distinct the primary index values, the better.
  • Rows having the same primary index value are distributed to the same AMP.
  • Parallel processing is more efficient when table rows are distributed evenly across the AMPs.

With respect to optimal data access, consider the following factors:

  • Choose the primary index on the most frequently used access path.
    For example
    • If rows are generally accessed by a range query, consider defining a PPI on the table that creates a useful set of partitions.
    • If the table is frequently joined with a specific set of tables, consider defining the primary index on the column set that is typically used as the join condition.
  • Primary index operations must provide the full primary index value.
  • Primary index retrievals on a single value are always one-AMP operations.

Although it is true that the columns you choose to be the primary index for a table are often the same columns that define the primary key, it is also true that primary indexes often comprise fields that are neither unique nor components of the primary key for the table.