16.20 - About Primary-Indexing, Row-Partitioning, Column-Partitioning, NoPI Tables, and Secondary Indexes - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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 for details.

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 Teradata 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).

Note that you cannot reference BLOB, CLOB, Period, or Geospatial columns in any index definition. This means that none of the following clauses can specify a reference to a BLOB, CLOB, Period, or Geospatial column.

For more information about primary and secondary indexes, see Teradata Vantage™ - Database Design, B035-1094.