Teradata Database tables can have a primary index, primary AMP index, or no primary index. If you do not define either PRIMARY INDEX, PRIMARY AMP INDEX, or NO PRIMARY INDEX explicitly when you create a table, then a default primary index for a table may be defined or the table may default to have no primary index (see Primary Index Defaults for details).
Use the CREATE TABLE statement to create primary indexes (see “CREATE TABLE” in SQL Data Definition Language).
Purposes of a Primary Index or Primary AMP Index
- To define the distribution of the rows to the AMPs.
With the exception of NoPI tables and join indexes, Teradata Database distributes table rows across the AMPs on the hash of their primary index or primary AMP index value. The determination of which AMP the row is to be stored on, is made solely on the value of the primary index or primary AMP index.
The choice of columns for the primary index or primary AMP index affects how even this distribution is. An even distribution of rows to the AMPs is usually of critical importance in picking a primary index or primary AMP index column set.
- To provide access to rows more efficiently than with a full-table scan.
If the values for all the primary index or primary AMP index columns are specified in a DML statement, single-AMP access can be made to the rows using that primary index or primary AMP index value.
With a row-partitioned primary index or primary AMP index, faster access is also possible when all the values of the partitioning columns are specified or if there is a constraint on partitioning columns. With a column-partitioned primary index or primary AMP index, only the column partitions with columns needed by the query are accessed.
Other retrievals might use a secondary index, a hash or join index, a full-table scan, or a mix of several different index types.
- To provide for efficient joins.
If there is an equijoin constraint on the primary index or primary AMP index of a table, it may be possible to do a direct join to the table (that is, rows of the table might not have to be redistributed, spooled, and sorted prior to the join).
- To provide a means for efficient aggregations.
If the GROUP BY key is on the primary index or primary AMP index of a table, it is often possible to perform a more efficient aggregation.
Restrictions on Primary Indexes and Primary AMP Indexes
- No more than one primary index or primary AMP index can be defined on a table.
You can also define base data tables that do not have a primary index or primary AMP index (see NoPI Tables, Column-Partitioned NoPI Tables, and Column-Partitioned NoPI Join Indexes and Column-Partitioned NoPI Tables and Join Indexes).
You cannot define a primary index or primary AMP index for a global temporary trace table. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language for details.
- No more than 64 columns can be specified in a primary index or primary AMP index definition.
- Primary index or primary AMP index columns cannot be defined on columns that have XML, BLOB, CLOB, BLOB-based UDT, CLOB-based UDT, XML-based UDT, Period, ARRAY, VARRAY, VARIANT_TYPE, Geospatial, or JSON data types.
- You cannot define a primary index or a primary AMP index for a NoPI table.
- Primary index or a primary AMP index columns cannot be defined on row-level security constraint columns.
- You cannot specify multivalue compression for primary index or a primary AMP index columns.
Primary Index Dimensions
Primary indexes are defined in two ways:
- Unique versus nonunique (see Unique and Nonunique Primary Indexes).
- Partitioned versus nonpartitioned (see Row-partitioned and Nonpartitioned Primary Indexes).
A partitioned primary index can be partitioned on anywhere from 1 to 62 levels for:
- Base tables that are not queue tables
- Global temporary tables
- Volatile tables
- Uncompressed join indexes
At most one level can be column partitioned; the other levels, if any, can be row partitioned. The following types of tables and indexes cannot be partitioned:
- Queue tables
- Row-compressed join indexes
- Hash indexes
- Journal tables
See Row-partitioned and Nonpartitioned Primary Indexes, Single-Level Partitioning, and Multilevel Partitioning.
For hash and join indexes only, the primary index can also be value-ordered.