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

SQL Data Definition Language Syntax and Examples
Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
ft:locale
en-US
ft:lastEdition
2021-07-27
dita:mapPath
spp1591731285373.ditamap
dita:ditavalPath
spp1591731285373.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;