16.20 - Redefining a Primary Index, Primary AMP Index, or Partitioning Without Using an ALTER TABLE Request - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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

First method

  1. Copy the table into a newly defined table defined with a different primary index or primary AMP index (or without a primary index) and populate it using the CREATE TABLE … AS syntax. See CREATE TABLE (AS Clause).
  2. Catalog the privileges on the old table. See step 3 in Procedure to Change Column Data Types.
  3. Drop the original table. See DROP TABLE and Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
  4. Rename the new table. See Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
  5. Grant privileges on the new table. See “GRANT (SQL Form)” in Teradata Vantage™ SQL Data Control Language, B035-1149.

Second method

  1. Create a new table with a different name that specifies the new index.
  2. Populate the new table using an INSERT … SELECT request. See Teradata Vantage™ SQL Data Manipulation Language , B035-1146 .
  3. Catalog the privileges on the old table. See step 3 in Procedure to Change Column Data Types.
  4. Drop the original table. See DROP TABLE and Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
  5. Rename the new table with that of the old table. See “RENAME MACRO” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
  6. Grant privileges on the new table. See “GRANT (SQL Form)” in Teradata Vantage™ SQL Data Control Language, B035-1149.