16.10 - PrimaryIndexDefault - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-26
dita:mapPath
zll1480972831047.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval

Purpose

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 Teradata Database, 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 Teradata Database 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 Teradata Database 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 Topics

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