Examples: ALTER TABLE MODIFY PRIMARY - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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);
Run 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);
Run 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;
Run 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;
Run 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);
Run 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);
Run 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;
Run 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;
Run 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;