TeraTeam UK Teradata DBA Definition
Indexes:
The principal method by which a table is accessed and/or distributed. Here we will discuss only Primary and Secondary Indexes. For advice on more complex issues such as join indexes or aggregate join indexes, please contact TeraTeam UK.
Primary Indexes - Single or Multiple columns by which criteria alone a tables data is distributed across the Teradata Database AMPs.
To avoid skewing, these should be Unique values wherever possible. Sometimes access requirements will dictate otherwise.
Unique Primary Indexes (UPI)
Allows only unique values to be entered into the Primary Index columns - avoids Skewing of data and allows single Amp Access when used to access data row.
Non-Unique Primary Indexes (NUPI)
Allows non-unique values to be entered in to the primary index, where other considerations (access) mean a UPI is not appropriate. Care should be taken to avoid skewing of data.
Note: Where a Primary Index is not specified in the table DDL, the default is the first column. A common mistake that causes skewing, particularly when users create temporary or volatile table.
Secondary Indexes
These are put in place to aid joins on frequently joined columns, where making these columns the Primary Index is not practical - usually for data distribution purposes.
They can be either Unique (USI) or Non-unique (NUSI).
They take the form of another table and therefore take up disk space. The benefits are that they CAN turn access in to a 2 AMP operation rather than an all AMP operation when joining on the columns concerned.
WARNING: Secondary indexes often appear to the inexperienced DBA to be a solution to performance problems. However, in practice the optimiser may choose not to use them depending on circumstances (e.g. number of rows accessed or misleading or missing statistics) and they can become just an additional overhead. Experience is needed when introducing secondary indexes. Contact Terateam UK if you need advice.
