17.10 - PrimaryIndexDefault - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

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

The CREATE TABLE and CREATE JOIN INDEX statements optionally can include several modifying clauses that determine how a table or join index is created. The PRIMARY INDEX, PRIMARY AMP [INDEX], NO PRIMARY INDEX, PRIMARY KEY, UNIQUE, and PARTITION BY modifiers affect whether the table or join index includes a primary index or primary AMP index. The PrimaryIndexDefault field determines whether a table or join index that is created without any of these modifiers will have a primary index created automatically by Vantage, or will be created as a NoPI table, lacking a primary index.

Field Group

General

Valid Settings

Setting Description
D Sets or resets this field to the Teradata default.

This is the same as specifying P.

P Vantage automatically creates primary indexes for tables and join indexes created with CREATE TABLE and CREATE JOIN INDEX statements that lack PRIMARY INDEX, PRIMARY AMP [INDEX], NO PRIMARY INDEX, PRIMARY KEY, UNIQUE, and PARTITION BY modifiers. The first column of the table serves as a nonunique primary index (NUPI).
Using the first column may cause significant skew in the distribution of rows to the AMPs if this column is not a good choice as a primary index.
N Vantage does not create primary indexes for tables and join indexes created without PRIMARY INDEX, PRIMARY AMP [INDEX], NO PRIMARY INDEX, PRIMARY KEY, UNIQUE, and PARTITION BY modifiers.

Defaulting to no primary index (NoPI) avoids skew that might occur with using the first column as a primary index.

Default

D

Changes Take Effect

After the DBS Control record has been written.

Usage Notes

If a CREATE TABLE statement specifies a PARTITION BY clause but no PRIMARY INDEX, PRIMARY AMP [INDEX], or NO PRIMARY INDEX clause, the default is NO PRIMARY INDEX, regardless of the PrimaryIndexDefault setting, and regardless of whether the CREATE TABLE includes a PRIMARY KEY or UNIQUE constraint.

The best practice is to explicitly specify PRIMARY INDEX, PRIMARY AMP [INDEX], or NO PRIMARY INDEX in CREATE TABLE statements, rather than relying on the PrimaryIndexDefault field setting.

Related Information

For the rules governing how system-defined primary indexes are created, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.