16.20 - Uniform Distribution of Data and Optimal Access Considerations - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantageâ„¢ SQL Fundamentals

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-02
dita:mapPath
zce1519094756513.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.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.