Understanding the Effects of Altering Tables - Teradata Database

Teradata Database Design
Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
Product Category
Software

Understanding the Effects of Altering Tables

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

Note: 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.