Row Distribution | Teradata Vantage - 17.00 - 17.05 - Primary Index and Skewed Row Distribution - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval

The hash value of the PI for a row determines where the row is stored on the AMP. In a normal environment, hash values are evenly distributed across the nodes and the AMPs within a node.

The less unique the values are for the index, the less evenly the rows of that table are distributed across the AMPs. If a table has a NUPI with thousands of instances of a single value, the table can become skewed.

Performance Effects of a Primary Index

  • The more unique the PI, the more unique the row hash value.
  • The more unique the row hash value, the more even the data distribution across all the AMP vprocs. Even data distribution enhances parallel efficiency on full table scan operations.
  • UPIs generate the most even distribution of table rows across all AMP vprocs.
  • NUPIs generate even row distribution to the same degree that values for a column or columns are unique. Rows with the same row hash always go to the same AMP, whether they are from the same table or from different tables.
To determine the best PI for a table, factor in the:
  • Extent of update activity
  • Number of full table scans
  • Join activity against the PI definition
  • Frequency of PI as a selectivity column and, therefore, a potential access path

If a PI causes uneven data distribution, you should re-specify it according to the primary index design best practices shown in Teradata Vantage™ - Database Design, B035-1094.