Primary and Secondary Index for performance
Don’t get confused with Primary Keys and Foreign Keys. Those are to relate tables with each other
| Primary Index (PI) | Secondary Index (SI) |
| The Primary Index can consist of up to sixteen different columns. These columns, when considered together, provide a comprehensive technique to derive a Unique Primary Index (UPI, pronounced as “you-pea”). Although uniqueness is good in most cases, Teradata does not require that a UPIbe used. It also allows for a Non-Unique Primary Index (NUPI, pronounced as new-pea). | A Secondary Index (SI) is used in Teradata as a way to directly access rows in the data, sometimes called the base table, without requiring the use of PI values. Unlike the PI, an SI does not effect the distribution of the data rows. Instead, it is an alternate read path and allows for a method to locate the PI value using the SI. Once the PI is obtained, the row can be directly accessed using the PI. Like the PI, an SI can consist of up to 16 columns. |
| Every table must have a PI and it is established when the table is created. If the DDL statement does not specify a PI, but it specifies a PRIMARY KEY (PK), the named column(s) are used as the UPI. Although Teradata does not use primary keys, the DDL may be ported from another vendor’s database system. | An SI can also be unique (USI, pronounced as you-sea) or non-unique (NUSI, pronounced as new-sea). If the SQL does not use a NUSI, you should consider dropping it, due to the fact that the subtable takes up PERM space with no benefit to the users. The Teradata EXPLAIN is the easiest way to determine if your SQL is using a NUSI. Furthermore, the optimizer will never use a NUSI without STATISTICS. |
To store the data, the value(s) in the PI are hashed via a calculation to determine which AMP will own the data. The same data values always hash the same row hash and therefore are always associated with the same AMP.
Use these links to learn about partitioned primary Index, advantages of partitioned primary index and secondary index
One thought on “Teradata Primary/Secondary Index”