Conditions for in-place alter operations

The database server can use the in-place alter algorithm to process only certain ADD, DROP, or MODIFY operations of the ALTER TABLE statement, and only if the table schema or the ALTER TABLE statement does not require a slow alter algorithm.

ALTER TABLE operations that can be done in place

The database server can use the in-place alter algorithm in the following ALTER TABLE operations:
  • Add columns of built-in data types, except the data types that are listed in Conditions that prevent in-place alter operations.
  • Drop a column of built-in data types, except a column that contains TEXT or BYTE data types, or a column that was created with the ROWIDS keyword.
  • In Enterprise Replication, add or drop a column that is created with the CRCOLS keyword.
  • Modify a column for which the database server can convert all possible values of the old data type to the new data type.
  • Modify a column that is part of the fragmentation expression for its table, only if value changes do not require any data row to move from one fragment to another fragment after data type conversion.

The following table shows the conditions under which the ALTER TABLE MODIFY statement uses the in-place alter algorithm to convert columns of supported data types.

Key:
  • All = The database server uses the in-place alter algorithm for all cases of the specific column operation.
  • nf = The database server uses the in-place alter algorithm when the modified column is not part of the table fragmentation expression.
Table 1. MODIFY operations and conditions that use the in-place alter algorithm
Operation on Column Condition
Convert a SMALLINT column to an INTEGER column All
Convert a SMALLINT column to a BIGINT column All
Convert a SMALLINT column to an INT8 column All
Convert a SMALLINT column to a DEC(p2,s2) column p2-s2 >= 5
Convert a SMALLINT column to a DEC(p2) column p2-s2 >= 5 OR nf
Convert a SMALLINT column to a SMALLFLOAT column All
Convert a SMALLINT column to a FLOAT column All
Convert a SMALLINT column to a CHAR(n) column n >= 6 AND nf
Convert an INT column to an INT8 column All
Convert an INT column to a DEC(p2,s2) column p2-s2 >= 10
Convert an INT column to a DEC(p2) column p2 >= 10 OR nf
Convert an INT column to a SMALLFLOAT column nf
Convert an INT column to a FLOAT column All
Convert an INT column to a CHAR(n) column n >= 11 AND nf
Convert a SERIAL column to an INT8 column All
Convert a SERIAL column to a DEC(p2,s2) column p2-s2 >= 10
Convert a SERIAL column to a DEC(p2) column p2 >= 10 OR nf
Convert a SERIAL column to a SMALLFLOAT column nf
Convert a SERIAL column to a FLOAT column All
Convert a SERIAL column to a CHAR(n) column n >= 11 AND nf
Convert a SERIAL column to a BIGSERIAL column All
Convert a SERIAL column to a SERIAL8 column All
Convert a SERIAL8 column to a BIGSERIAL column All
Convert a BIGSERIAL column to a SERIAL8 column All
Convert a DEC(p1,s1) column to a SMALLINT column p1-s1 < 5 AND (s1 == 0 OR nf)
Convert a DEC(p1,s1) column to an INTEGER column p1-s1 < 10 AND (s1 == 0 OR nf)
Convert a DEC(p1,s1) column to an INT8 column p1-s1 < 20 AND (s1 == 0 OR nf)
Convert a DEC(p1,s1) column to a SERIAL column p1-s1 < 10 AND (s1 == 0 OR nf)
Convert a DEC(p1,s1) column to a BIGSERIAL column p1-s1 < 20 AND (s1 == 0 OR nf)
Convert a DEC(p1,s1) column to a SERIAL8 column p1-s1 < 20 AND (s1 == 0 OR nf)
Convert a DEC(p1,s1) column to a DEC(p2,s2) column p2-s2 >= p1-s1 AND (s2 >= s1 OR nf)
Convert a DEC(p1,s1) column to a DEC(p2) column p2 >= p1 OR nf
Convert a DEC(p1,s1) column to a SMALLFLOAT column nf
Convert a DEC(p1,s1) column to a FLOAT column nf
Convert a DEC(p1,s1) column to a CHAR(n) column n >= 8 AND nf
Convert a DEC(p1) column to a DEC(p2) column p2 >= p1 OR nf
Convert a DEC(p1) column to a SMALLFLOAT column nf
Convert a DEC(p1) column to a FLOAT column nf
Convert a DEC(p1) column to a CHAR(n) column n >= 8 AND nf
Convert a SMALLFLOAT column to a DEC(p2) column nf
Convert a SMALLFLOAT column to a FLOAT column nf
Convert a SMALLFLOAT column to a CHAR(n) column n >= 8 AND nf
Convert a FLOAT column to a DEC(p2) column nf
Convert a FLOAT column to a SMALLFLOAT column nf
Convert a FLOAT column to a CHAR(n) column n >= 8 AND nf
Convert a CHAR(m) column to a CHAR(n) column n >= m OR (nf AND not ANSI mode)
Increase the length of a character-type column Not in ANSI mode databases
Increase the length of a DECIMAL or MONEY column All
Convert an INT column to a SERIAL column All
Convert an INT column to a BIGSERIAL column All
Convert an INT column to a SERIAL8 column All
Convert a BIGINT column to a BIGSERIAL column All
Convert a BIGINT column to a SERIAL8 column All
Convert a INT8 column to a BIGSERIAL column All
Convert a INT8 column to a SERIAL8 column All
Note: If first column of an index is altered, the operation to find the next serial value is very fast as it can make use of the index. If altered column is not first column of an index, the operation will do a sequential scan of the table to find the next serial value.

If you supply the serial value of the altered column, the operation is fast as the serial value is provided and does not require any calculation.

Conditions that prevent in-place alter operations

When the table contains an opaque data type, a user-defined data type, an LVARCHAR data type, a BOOLEAN data type, or a smart large object (BLOB or CLOB), the database server does not use the in-place alter algorithm, even when the column that is being altered is of a data type that can support in-place alter operations.

The in-place alter algorithm is not used if the ALTER TABLE DROP statement specifies BYTE or TEXT columns, or the ROWIDS keyword, or if the ALTER TABLE ADD statement includes the ROWID keyword.

If any column data types in an ALTER TABLE MODIFY statement cannot be converted by in-place alter operations, or if data movement is required for a fragmented table, the database server uses the slow alter algorithm for data type conversion instead of using the in-place alter algorithm.

For example, the database server does not use the in-place alter algorithm in the following situations:

  • When more than one algorithm is needed

    For example, assume that an ALTER TABLE MODIFY statement converts a SMALLINT column to a DEC(8,2) column and converts an INTEGER column to a CHAR(8) column. The conversion of the first column is an in-place alter operation, but the conversion of the second column is a slow alter operation. The database server uses the slow alter algorithm to execute this statement.

  • When the ALTER TABLE operation moves data records to another fragment

    For example, suppose you have a table with two integer columns and the following fragment expression:

    col1 < col2 IN dbspace1, REMAINDER IN dbspace2 

    If you issue an ALTER TABLE MODIFY statement to convert the integer values to character values, the database server stores the row (4, 30) in dbspace1 before the alter operation, but stores it in dbspace2 after the alter operation, not as integers, 4 < 30, but as characters, '30' < '4'.

  • When the database server cannot convert all possible values of the old data type to the new data type.

    For example, you cannot convert a BIGSERIAL column to a SERIAL column, because the modified column cannot store BIGSERIAL values that are beyond the range of SERIAL values. (However, you can change a column from SERIAL to BIGSERIAL with an in-place alter operation, if other columns in the table do not conflict with any of the other restrictions on in-place alter operations.)


Copyright© 2019 HCL Technologies Limited