Order of operations for the ALTER TABLE statement

Operations for the ALTER TABLE statement are performed in a defined order.

The ALTER TABLE statement is performed as this set of steps shows:

  1. Drop constraints
  2. Drop materialized query table
  3. Drop partition information
  4. Drop columns for which the RESTRICT option was specified
  5. Alter column definitions (this includes adding columns and dropping columns for which the CASCADE option was specified)
  6. Add or alter materialized query table
  7. Add partitioning to a table
  8. Add constraints

Within each of these steps, the order in which you specify the clauses is the order in which they are performed, with one exception. If any columns are being dropped, that operation is logically done before any column definitions are added or altered, in case record length is increased as a result of the ALTER TABLE statement.

Parent topic: Changing a table definition