Redefining a Primary Index, Primary AMP Index, or Partitioning Without Using an ALTER TABLE Request - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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.