15.00 - Performance Implications of Altering a Primary Index Using ALTER TABLE Requests - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Performance Implications of Altering a Primary Index Using ALTER TABLE Requests

You should consider the following information about the immediate performance impact of altering the primary index for a table to best schedule the operation in a way that has the least impact on your production workload.

  • Altering an empty table is fairly quick.
  • Altering a primary‑indexed table to have a unique or non‑unique primary index is fairly quick.
  • Altering a primary‑indexed table to change the primary index name is fairly quick.
  • The following cumulative performance issues are all concerned with dropping or adding new ranges or partitions to a row‑partitioned table.
  • When dropping or adding new ranges or partitions for a populated table, the operation can be fairly quick because rows that remain in the retained ranges and partitions need not be processed or repartitioned.
  • There is an additional small overhead if dropped ranges and partitions are populated, and still further overhead if any referential integrity constraints are defined on the table.
  • There is additional overhead if new ranges are added and there are populated NO RANGE [OR UNKNOWN) or UNKNOWN partitions or rows in dropped ranges that need to be moved to the added ranges because rows must be processed to determine if any of them need to be assigned to one of the new ranges and, if so, to move them to the correct range.
  • You must update any secondary, join, or hash indexes on the table.
  • Updating secondary, join, and hash indexes can be lengthy operations and depend on several factors, including the size of the table and indexes and the number of rows deleted or moved.

  • There is additional overhead if the deleted rows are inserted into a save table. The degree of this overhead depends on the number of rows that must be inserted into the save table and the other standard performance issues associated with inserting rows into a table.
  • If a table is defined with a NO RANGE partition, specifying a WITH DELETE or WITH INSERT INTO clause in an ALTER TABLE request used to change its definition has no effect.
  • In this case, rows from deleted partitions and rows whose partition number evaluates to something other than 1 - 65,535, inclusive, for 2‑byte partitioning or something other than 1 - 9,223,372,036,854,775,805, inclusive, for 8‑byte partitioning, are retained in the NO RANGE partition rather than being moved to the target table specified in the WITH DELETE or WITH INSERT INTO clause.

    See “Purpose and Behavior of the NO RANGE and UNKNOWN Partitions” on page 640 and “Rules For Altering the Partitioning Expression For a Table” on page 110 for additional information.