- 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.
Some modifications, such as partitioning and primary index columns, require an empty table.
- Automatically assigned by CREATE TABLE if you do not explicitly define a primary index. However, the best practice is to always specify the primary index, because the default may not be appropriate for the table.
- Can be composed of as many as 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.
Note that 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.