ALTER TABLE MODIFY PRIMARY Examples - Advanced SQL Engine

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

Published

September 2020

Language

English (United States)

Last Update

2021-01-23

dita:mapPath

wgr1555383704548.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1144

lifecycle

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;