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
- 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.
- 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.
| 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 |
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 dbspace2If 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.)