15.00 - CREATE TABLE (Index Definition Clause) - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

CREATE TABLE (Index Definition Clause)

About Primary‑Indexed Tables, Row‑Partitioned Tables, Column‑Partitioned Tables, NoPI Tables, and Secondary Indexes

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 GLOBAL TEMPORARY TRACE TABLE” on page 317 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 unpartitioned and column‑partitioned tables (see “Unpartitioned NoPI Tables” on page 575 and “Column‑Partitioned Tables” on page 577), 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 non‑unique 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 in all likelihood will have a table that produces significant 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” on page 334).

To create a join index incorporating this table, use the CREATE JOIN INDEX statement (see “CREATE JOIN INDEX” on page 340).

To create a hash index on a table, use the CREATE HASH INDEX statement (see “CREATE HASH INDEX” on page 322).

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.

  • INDEX (see information about NUSIs in the topic “Secondary Indexes” on page 649).
  • PRIMARY INDEX (see “Primary Indexes” on page 575 and “Partitioned and Unpartitioned Primary Indexes” on page 603).
  • UNIQUE INDEX (see information about USIs in the topic “Secondary Indexes” on page 649).
  • UNIQUE PRIMARY INDEX (see “Primary Indexes” on page 575 and “Partitioned and Unpartitioned Primary Indexes” on page 603).
  • Also see Database Design for a wealth of information about primary and secondary indexes.