17.00 - Example: CREATE … AS Requests That Produce a NoPI Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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 Vantage 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.