Indexing and Partitioning Tables | CREATE TABLE | Teradata Vantage - 17.10 - About Primary-Indexing, Row-Partitioning, Column-Partitioning, NoPI Tables, and Secondary Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
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.

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), Vantage 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).

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.