Using the ALTER TABLE statement can affect system performance and space requirements.
Changes to the Data Dictionary resulting from 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) |
|
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.
|
Change the format, title, default | No impact. | Unchanged. |
Change the cylinder free space percent |
|
Increase in required permanent space for operations such as default maximum, MultiLoad, restore. |
Change the maximum multirow block size |
|
|
Delete the FALLBACK option | FALLBACK subtable is deleted. Long-term performance effects. You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
|
Approximately half the required permanent space. |
Drop a column | All table rows are changed. | Decrease in required permanent space. |