16.10 - Understanding the Effects of Altering Tables - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Using the ALTER TABLE statement can affect system performance and space requirements.

Changes to the Data Dictionary resulting form these actions have minimal effect on performance.
Action Performance Impact Space Requirements
Add a column (COMPRESS, NULL) All table rows are changed if a new presence byte is added. Slight increase in required permanent space.
Add a column (NOT NULL, DEFAULT, and WITH DEFAULT)
  • All table rows change (without column partitioning).
  • Only affected column partitions change (with column partitioning).
Increase in required permanent space.
Add a column (NULL, fixed-length) All table rows are changed. Increase in required permanent space.
Add a column (NULL, variable length) All table rows are changed. Slight increase in required permanent space.
Add FALLBACK Entire table is accessed to create the fallback copy.

Long-term performance effects.

Approximately doubled the required permanent space.
Add CHECK constraints Takes time to validate rows, which impacts performance. Unchanged.
Add referential integrity Takes time to check data.

Impacts performance long term. Similar to adding indexes.

Possible large increase in the following.
  • Spool space.
  • Permanent space (for index if not soft batch).
Change the format, title, default No impact. Unchanged.
Change the cylinder free space percent
  • Raising the free space percent can make inserting new data less expensive by reducing migration and cylinder allocations.
  • Lowering the free space percent has the opposite effect.
Increase in required permanent space for operations such as default maximum, MultiLoad, restore.
Change the maximum multirow block size
  • If the table is not updated after the change, then there is no impact.
  • If the table is changed, there can be performance impact whether or not the IMMEDIATE clause is specified.
  • Slight increase in required permanent space for smaller values.
  • Slight decrease in required permanent space for larger values.
Delete the FALLBACK option FALLBACK subtable is deleted.

Long-term performance effects.

Approximately half the required permanent space.
Drop a column All table rows are changed. Decrease in required permanent space.