This CREATE TABLE clause permits you to create 1 or no primary indexes and optional secondary indexes for a table.
A table can have no more than 1 primary index, which can optionally be partitioned. The partitioning option is available for global temporary tables, volatile tables, and standard base tables that have a primary index. Global temporary trace tables cannot be partitioned. See CREATE TABLE Global and Temporary.
If you want a table to have a primary index, you should always define the primary index explicitly. The same is true if you do not want a table to have a primary index.
Because, with the exception of nonpartitioned and column-partitioned tables (see Nonpartitioned NoPI Tables and Column-Partitioned Tables), Teradata Database assigns rows to AMPs based on the row hash of their primary index value, it is important to select a column set that distributes table rows fairly evenly when a nonunique primary index is defined for a primary-indexed table. This is critical whether the table is partitioned or not. When you allow the database to select a column set as the primary index for a table by default, you have no control over the evenness of its distribution across the AMPs, and this may result in a table with performance bottlenecks.
The Optimizer uses index definitions to plan how to access data in the least costly manner and AMP software uses them to physically access rows on disk in the least costly manner possible.
To define additional secondary indexes on a table after it has been created, use the CREATE INDEX statement (see CREATE INDEX).
To create a join index incorporating this table, use the CREATE JOIN INDEX statement (see CREATE JOIN INDEX).
To create a hash index on a table, use the CREATE HASH INDEX statement (see CREATE HASH INDEX).
- INDEX (see information about NUSIs in the topic Secondary Indexes).
- PRIMARY INDEX (see Primary Indexes and Partitioned and Nonpartitioned Primary Indexes).
- UNIQUE INDEX (see information about USIs in the topic Secondary Indexes).
- UNIQUE PRIMARY INDEX (see Primary Indexes and Partitioned and Nonpartitioned Primary Indexes).
For more information about primary and secondary indexes, see Teradata Vantage™ - Database Design, B035-1094.