17.05 - ALTER TABLE MODIFY PRIMARY Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

Example: Modify a Primary Index Table to a Primary Index-Column Partitioned Table

This example modifies the following table definition:
CREATE TABLE pi3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute any one of the following ALTER TABLE statements on the empty table:
ALTER TABLE pi3 MODIFY PARTITION BY COLUMN;
ALTER TABLE pi3 MODIFY PRIMARY INDEX PARTITION BY COLUMN;
ALTER TABLE pi3 MODIFY PRIMARY INDEX (a) PARTITION BY COLUMN;
The following table definition results:
CREATE TABLE pi3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;

Example: Modify a Primary Index Table to a Primary Index-Column Partitioned-RP Table

This example modifies the following table definition:
CREATE TABLE pi4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute any one of the following ALTER TABLE statements on the empty table:
ALTER TABLE pi4 MODIFY
  PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));
ALTER TABLE pi4 MODIFY PRIMARY INDEX
    PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));
ALTER TABLE pi4 MODIFY PRIMARY INDEX (a)
  PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));
The following table definition results:
CREATE TABLE pi4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a)
  PARTITION BY (COLUMN, RANGE_N(b BETWEEN 1 AND 10 EACH 1));

Example: Modify a Primary Index-Column Partitioned Table to a Primary AMP Index-Column Partitioned Table

This example uses the following table definition:
CREATE TABLE p8 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;
Execute any one of the following ALTER TABLE statements on the empty table:
ALTER TABLE p8 MODIFY PRIMARY AMP INDEX;
ALTER TABLE p8 MODIFY PRIMARY AMP INDEX (a);
ALTER TABLE p8 MODIFY PRIMARY AMP INDEX (a) PARTITION BY COLUMN;
The following table definition results:
CREATE TABLE p8 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (b) PARTITION BY COLUMN;

Example: Modify a Primary Index-Column Partitioned Table to a NoPI-Column Partitioned Table

This example uses the following table definition:
CREATE TABLE p9 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a) PARTITION BY COLUMN;
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE p9 MODIFY NO PRIMARY INDEX;
ALTER TABLE p9 MODIFY NO PRIMARY INDEX PARTITION BY COLUMN;
The following table definition results:
CREATE TABLE p9 (a INTEGER, b INTEGER, c CHAR(10))
  NO PRIMARY INDEX PARTITION BY COLUMN;

Example: Modify a Primary Index Table to a Primary AMP Index-Column Partitioned Table

This example uses the following table definition:
CREATE TABLE p11 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE p11 MODIFY PRIMARY AMP INDEX PARTITION BY COLUMN;
ALTER TABLE p11 MODIFY PRIMARY AMP INDEX (a) PARTITION BY COLUMN;
The following table definition results:
CREATE TABLE p11 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;

Example: Modify a Primary Index Table to a Primary AMP Index-Column Partitioned Table with a Different Index Column

This example uses the following table definition:
CREATE TABLE p12 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);
Execute the following ALTER TABLE statement on the empty table:
ALTER TABLE p12 MODIFY PRIMARY AMP INDEX (b) PARTITION BY COLUMN;
The following table definition results:
CREATE TABLE p12 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (b) PARTITION BY COLUMN;

Example: Modify a Primary AMP Index Column Partitioned Table to a Primary Index Nonpartitioned Table

This example uses the following table definition:
CREATE TABLE pt3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE pt3 MODIFY PRIMARY INDEX (a) NOT PARTITIONED;
ALTER TABLE pt3 MODIFY PRIMARY INDEX NOT PARTITIONED;
The following table definition results:
CREATE TABLE pt3 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (a);

Example: Modify a Primary AMP Index Column Partitioned Table to a Primary Index Column Partitioned Table

This example uses the following table definition:
CREATE TABLE pt4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY AMP INDEX (a) PARTITION BY COLUMN;
Execute one of the following ALTER TABLE statements on the empty table:
ALTER TABLE pt4 MODIFY PRIMARY INDEX (b);
ALTER TABLE pt4 MODIFY PRIMARY INDEX (b) PARTITION BY COLUMN;
The following table definition results:
CREATE TABLE pt4 (a INTEGER, b INTEGER, c CHAR(10))
  PRIMARY INDEX (b) PARTITION BY COLUMN;