15.00 - PrimaryIndexDefault - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
15.00
Content Type
Configuration
Publication ID
B035-1102-015K
Language
English (United States)
Last Update
2018-09-25

PrimaryIndexDefault

Purpose  

The CREATE TABLE statement optionally can include several modifying clauses that determine how a table is created. The PRIMARY INDEX, NO PRIMARY INDEX, PRIMARY KEY, and UNIQUE clauses affect whether the table includes a primary index. The PrimaryIndexDefault field determines whether a table that is created without any of these modifiers will have a primary index created automatically by Teradata Database, or will be created as a NoPI table, lacking a primary index.

Loading data into NoPI tables is faster than loading data into tables with primary indexes. When there is no primary index, data is distributed among AMPs randomly, which is faster than distributing the data according to a hash of the primary index. Because rows can be appended quickly to the end of a NoPI table, these kinds of tables can provide a performance advantage when loading data. Consequently, NoPI tables can be used as staging tables where data can be loaded quickly, and from which the data is applied later to indexed target tables using INSERT... SELECT, UPDATE ... FROM, or MERGE INTO statements.

Field Group

General

Valid Settings

 

Setting

Description

D

Sets or resets this field to the factory default.

Teradata Database automatically creates primary indexes for tables created with CREATE TABLE statements that lack PRIMARY INDEX, NO PRIMARY INDEX, PRIMARY KEY, and UNIQUE modifiers. The first column of the table serves as a non-unique primary index (NUPI).

P

Teradata Database automatically creates primary indexes for tables created with CREATE TABLE statements that lack PRIMARY INDEX, NO PRIMARY INDEX, PRIMARY KEY, and UNIQUE modifiers. The first column of the table serves as a NUPI.

N

Teradata Database does not create primary indexes for tables created with CREATE TABLE statements that lack PRIMARY INDEX, NO PRIMARY INDEX, PRIMARY KEY, and UNIQUE modifiers.

Default

D

Changes Take Effect

After the DBS Control record has been written.

Usage Notes  

Regardless of the PrimaryIndexDefault setting, tables created using PRIMARY KEY and UNIQUE constraints will have unique indexes:

  • If PRIMARY INDEX is explicitly specified, the table is created with a primary index, and the columns specified as PRIMARY KEY or UNIQUE become a unique secondary index.
  • If NO PRIMARY INDEX is explicitly specified, the table is created as a NoPI table, and the columns designated as PRIMARY KEY or UNIQUE become a unique secondary index.
  • If neither PRIMARY INDEX nor NO PRIMARY INDEX are explicitly specified, the columns specified as PRIMARY KEY or UNIQUE become a unique primary index.
  • The best practice is to explicitly specify PRIMARY INDEX or NO PRIMARY INDEX in CREATE TABLE statements, rather than relying on the PrimaryIndexDefault setting.

    Note: PrimaryIndexDefault has no effect on temporal tables, which must each have a primary index specified explicitly in their table definitions. It has no effect on column-partitioned tables, which must always be NoPI tables.

    Related Topics

    For the rules governing how system-defined primary indexes are created, see SQL Data Definition Language.