17.10 - Defining Indexes for a Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

An index is a physical mechanism used to store and access the rows of a table. When you define a table, you can define a primary index or primary AMP index and one or more secondary indexes.

If you define a table and you do not specify a PRIMARY INDEX clause, PRIMARY AMP [INDEX], NO PRIMARY INDEX clause, PRIMARY KEY constraint, UNIQUE constraint, or PARTITION BY, the default behavior is for the system to create the table using the first column as the nonunique primary index. (If you prefer that the system creates a table without a primary or a primary AMP index, use DBS Control to change the value of the PrimaryIndexDefault General field.

If the PARTITION BY clause is specified without specifying a PRIMARY INDEX, PRIMARY AMP [INDEX], or NO PRIMARY INDEX clause, the default is NO PRIMARY INDEX regardless of the setting of this field or whether a PRIMARY KEY or UNIQUE clause is specified.

Related Information

For details on indexes, see Indexes. For details on NoPI tables, see No Primary Index (NoPI) Tables.

For details about creating a table without a primary or a primary AMP index, and using DBS Control to change the value of the PrimaryIndexDefault General field, see Teradata Vantage™ - Database Utilities, B035-1102.