Understanding the Effects of Altering Tables - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kko1591750222108.ditamap
dita:ditavalPath
kko1591750222108.ditaval
dita:id
B035-1094
lifecycle
previous
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.