16.20 - Example: CREATE … AS Requests That Produce a NoPI Table - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

Assume the following primary-indexed source table definition for the following example set.

     CREATE TABLE source_pi (
       column_1 INTEGER
       column_2 INTEGER)
     UNIQUE PRIMARY INDEX (column_1);

Also assume you have created the following NoPI table definition for the example set:

     CREATE TABLE source_nopi (
       column_1 INTEGER
       column_2 INTEGER)
     NO PRIMARY INDEX;

The following CREATE TABLE … AS requests all produce a NoPI table because in each case you have explicitly specified NO PRIMARY INDEX:

     CREATE TABLE target_nopi AS
       source_pi
     WITH DATA 
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS
       source_nopi
     WITH DATA
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS (
       SELECT column_1, column_2
       FROM source_pi) 
     WITH DATA 
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS (
       SELECT column_1, column_2
       FROM source_nopi) 
     WITH DATA 
     NO PRIMARY INDEX;

Unlike the preceding examples, the outcome of the following CREATE TABLE request depends on the setting of the DBS Control parameter PrimaryIndexDefault.

     CREATE TABLE target AS (
       SELECT column_1, column_2
       FROM source_nopi) 
     WITH DATA;
IF the value for PrimaryIndexDefault is … THEN the resulting table has …
N no primary index.

Even though you have not explicitly specified NO PRIMARY INDEX, you also have not specified any PRIMARY KEY or UNIQUE constraints on the columns of the table, so Teradata Database defines target as a NoPI table by default.

D or P a nonunique primary index.

The NUPI is defined by default, following the rules for selecting the primary index for a table when none is specified.

In this case, the primary index is defined on the first column defined for target , which is column_1.

The primary index is defined as a NUPI because no PRIMARY KEY or UNIQUE constraints are defined on any of the columns of target.