In-place alter

The in-place alter algorithm provides numerous performance advantages over the slow alter algorithm

The in-place alter algorithm:
  • Increases table availability

    Other users can access the table sooner when the ALTER TABLE operation uses the in-place alter algorithm, because the database server locks the table for only the time that it takes to update the table definition and rebuild indexes that contain altered columns.

    This increase in table availability can increase system throughput for application systems that require 24 by seven operations.

    When the database server uses the in-place alter algorithm, it locks the table for a shorter time than the slow alter algorithm because the database server:

    • Does not make a copy of the table to convert the table to the new definition
    • Does not convert the data rows during the ALTER TABLE operation
    • Alters the physical columns in place with the latest definition after the alter operation when you later update or insert rows. The database server converts the rows that reside on each page that you updated.
  • Requires less space than the slow alter algorithm

    When the ALTER TABLE operation uses the slow alter algorithm, the database server makes a copy of the table to convert the table to the new definition. The ALTER TABLE operation requires space at least twice the size of the original table plus log space.

    When the ALTER TABLE operation uses the in-place alter algorithm, the space savings can be substantial for very large tables.

  • Improves system throughput during the ALTER TABLE operation

    The database server does not log any changes to the table data during the in-place alter operation. Not logging changes has the following advantages:

    • Log space savings can be substantial for very large tables.
    • The alter operation is not a long transaction.

If the check_for_ipa Scheduler task is enabled, each table that has one or more outstanding in-place alter operations is listed in the ph_alert table in the sysadmin database. The alert text is: Table database:owner.table_name has outstanding in place alters. The alert type is informative.