- Defined with the CREATE TABLE data definition statement.
CREATE INDEX is used only to create secondary indexes.
- Modified with the ALTER TABLE data definition statement.
Modifications such as partitioning and primary index columns may require an empty table.
- Automatically assigned by CREATE TABLE if you do not explicitly define a primary index. However, the best practice is to specify the primary index, because the default may not be appropriate for the table.
- A primary index can be composed of up to 64 columns.
- A maximum of one can be defined per table.
- Can be partitioned or nonpartitioned.
Partitioned primary indexes are not automatically assigned. You must explicitly define a partitioned primary index.
- Can be unique or nonunique.
A partitioned primary index can only be unique if all the partitioning columns are also included as primary index columns. If the primary index does not include all the partitioning columns, uniqueness on the primary index columns may be enforced with a unique secondary index on the same columns as the primary index.
- Defined as nonunique if the primary index is not defined explicitly as unique or if the primary index is specified for a single column SET table.
- Controls data distribution and retrieval using the Teradata hashing algorithm.
- Improves performance when used correctly in the WHERE clause of an SQL data manipulation statement to perform the following actions.
- Single-AMP retrievals.
- Joins between tables with identical primary indexes, the optimal scenario.
- Partition elimination when the primary index is partitioned.