Redefining the Primary, Primary AMP, or Partitioning | VantageCloud Lake - Redefining the Primary, Primary AMP, or Partitioning - 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

Learn how to work with primary indexes or partitioning for a table with or without the ALTER TABLE request, or alter the indexes using ALTER TABLE.

Redefining for a Table

To redefine the PRIMARY, PRIMARY AMP, NO PRIMARY and PARTITION BY or NOT PARTITIONED clauses for a table, use one of the following methods:
  • Use the ALTER TABLE MODIFY PRIMARY or PRIMARY AMP syntax to modify the primary index or primary AMP index for the table. See MODIFY PRIMARY, General Rules for the MODIFY PRIMARY Clause, and Modifying the Partitioning of a Table or Join Index.
  • To change the partitioning for a table, you can specify MODIFY without specifying PRIMARY, PRIMARY AMP, or NO PRIMARY. The PRIMARY, PRIMARY AMP, or NO PRIMARY keywords are only required to modify the primary index or primary AMP index of a table.
  • Create a new table with the primary index, primary AMP index, or partitioning defined properly and then use an INSERT ... SELECT to copy the data from the old table to the new table.
  • Use the CREATE TABLE AS syntax to:
    1. Create a new table with the primary index or primary AMP index and partitioning, if required.
    2. Copy the rows and, if desired, the statistics from the old table into the new table.

    See CREATE TABLE (AS Clause).

You can use ALTER TABLE to perform the following modifications to a primary index, primary AMP index, or partitioning for a table. See General Rules for the MODIFY PRIMARY Clause, Modifying the Partitioning of a Table or Join Index, and Rules for Altering a Partitioning for a Table.

Operation What to Specify
Change a NUPI to a UPI MODIFY UNIQUE PRIMARY or PRIMARY AMP in the MODIFY PRIMARY clause.
Change a UPI to a NUPI MODIFY NOT UNIQUE PRIMARY or PRIMARY AMP in the MODIFY PRIMARY clause.
Change a partitioned object to a nonpartitioned object NOT PARTITIONED in the MODIFY PRIMARY clause.

The table must be empty.
Change a nonpartitioned object to a partitioned object a PARTITION BY clause in the MODIFY PRIMARY clause.

The table must be empty.
Add or drop partitioning expression ranges an ADD RANGE or DROP RANGE clause within the Primary Index Change Options clause. See Modifying Partitioning Using the ADD RANGE and DROP RANGE Options.
Add or drop primary index or primary AMP index columns MODIFY PRIMARY clause.

The table must be empty.
Validate partitioning by regenerating table headers and correcting any errors in row partitioning a REVALIDATE PRIMARY INDEX clause within the REVALIDATE Options clause. See General Rules and Restrictions for the REVALIDATE Option.

If you alter a table to have a UPI and there is already a USI defined on the same column set, the system drops the USI automatically during the ALTER TABLE operation.

A primary index cannot contain any BLOB, CLOB, ARRAY, VARRAY, Period, or Geospatial columns.

You cannot change the primary index for an error table. See Rules and Restrictions for Error Tables.

Redefining without ALTER TABLE

To redefine the primary index or partitioning for a table without using an ALTER TABLE request, see Procedure to Change Column Data Types. See one of the following methods.

First method Second method
  1. Copy the table into a new table defined with a different primary index or primary AMP index (or without a primary index) using CREATE TABLE (AS Clause).
  2. Catalog the privileges on the old table. See Procedure to Change Column Data Types.
  3. Drop the original table. See DROP TABLE.
  4. Rename the new table. See RENAME TABLE.
  5. Grant privileges on the new table. See GRANT.
  1. Create a new table with a different name that specifies the new index.
  2. Populate the new table using an INSERT ... SELECT request.
  3. Catalog the privileges on the old table. See Procedure to Change Column Data Types.
  4. Drop the original table. See DROP TABLE.
  5. Rename the new table with that of the old table. See RENAME TABLE.
  6. Grant privileges on the new table. See GRANT.

Altering Use ALTER TABLE

  • Altering an empty table.
  • Altering a table with a primary index or primary AMP index to have a unique or nonunique primary index.
  • Altering a table to change the primary index or primary AMP index name.