Understanding the Effects of Altering Tables - Teradata Vantage - Analytics Database

Database Design
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ogg1628096130566.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
zqc1472244571611
lifecycle
lifecycle
Product Category
Teradata Vantage™

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

Changes to the Data Dictionary resulting from 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.

You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
Approximately half the required permanent space.
Drop a column All table rows are changed. Decrease in required permanent space.