Understanding the Effects of Altering Tables - Teradata VantageCloud Lake

Lake - Database Reference
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549

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.