Altering a Table Definition

The database server uses one of several algorithms to process an ALTER TABLE statement in SQL:

• Slow alter
• In-place alter
• Fast alter

Slow Alter

When the database server uses the slow alter algorithm to process an ALTER TABLE statement, the table can be unavailable to other users for a long period of time because the database server:

• Locks the table in exclusive mode for the duration of the ALTER TABLE operation
• Makes a copy of the table in order to convert the table to the new definition
• Converts the data rows during the ALTER TABLE operation
• Can treat the ALTER TABLE statement as a long transaction and abort it if the LTXHWM threshold is exceeded

The database server uses the slow alter algorithm when the ALTER TABLE statement makes column changes that it cannot perform in place:

• Adding or drop a column created with the ROWIDS keyword
• Dropping a column of the TEXT or BYTE data type
• Modifying a SMALLINT column to SERIAL or SERIAL8 or converting an INT column to SERIAL or SERIAL8
• Modifying the data type of a column so that some possible values of the old data type cannot be converted to the new data type

  For example, if you modify a column of data type INTEGER to CHAR(n), the database server uses the slow alter algorithm if the value of n is less than 11. An INTEGER requires 10 characters plus one for the minus sign for the lowest possible negative values.

• Modifying the data type of a fragmentation column in a way that value conversion might cause rows to move to another fragment
• Adding, dropping or modifying any column when the table contains user-defined data types or smart large objects.

In-Place Alter

The in-place alter algorithm provides the following performance advantages over the slow 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 7 operation.

  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 subsequently 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 need to 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.

When the Database Server Uses the In-Place Alter Algorithm

The database server uses the in-place alter algorithm for certain types of operations that you specify in the ADD, DROP, and MODIFY clauses of the ALTER TABLE statement:

• Add a column or list of columns of any data type except columns that you add with the ROWIDS keyword.
• Drop a column of any data type except TEXT or BYTE and columns created with the ROWIDS keyword.
• Add or drop a column 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 if value changes do not require a row to move from one fragment to another fragment after conversion.

Table 7 shows the conditions under which the ALTER TABLE MODIFY statement uses the in-place alter algorithm to process the ALTER TABLE MODIFY Statement.

Notes:

• The column type DEC(p) refers to non-ANSI databases in which this data type is handled as floating point.
• In ANSI databases, DEC(p) defaults to DEC(p,0) and uses the same alter algorithm as DEC(p,s).
• The condition nf indicates that the database server uses the in-place alter algorithm when the modified column is not part of the table fragmentation expression.
• The condition All indicates that the database server uses the in-place alter algorithm for all cases of the specific column operation.

Performance Considerations for DML Statements

Each time you execute an ALTER TABLE statement that uses the in-place alter algorithm, the database server creates a new version of the table structure. The database server keeps track of all versions of table definitions. The database server resets the version status and all of the version structures and alter structures until the entire table is converted to the final format or a slow alter is performed.

If the database server detects any down-level version page during the execution of data manipulation language (DML) statements (INSERT, UPDATE, DELETE, SELECT), it performs the following actions:

• For UPDATE statements, the database server converts the entire data page or pages to the final format.
• For INSERT statements, the database server converts the inserted row to the final format and inserts it in the best-fit page. The database server converts the existing rows on the best-fit page to the final format.
• For DELETE statements, the database server does not convert the data pages to the final format.
• For SELECT statements, the database server does not convert the data pages to the final format.

  If your query accesses rows that are not yet converted to the new table definition, you might notice a slight degradation in the performance of your individual query, because the database server reformats each row before it is returned.

Performance Considerations for DDL Statements

The oncheck -pT tablename option displays data-page versions for outstanding in-place alter operations. An in-place alter is outstanding when data pages still exist with the old definition.

Figure 33 displays a portion of the output that the following oncheck command produces after four in-place alter operations are executed on the customer demonstration table:

oncheck -pT stores_demo:customer

...
Home Data Page Version Summary

            Version           Count

            0 (oldest)           2
            1           0
            2           0
            3           0
            4 (current)           0
...

The Count field in Figure 33 displays the number of pages that currently use that version of the table definition. This oncheck output shows that four versions are outstanding:

• A value of 2 in the Count field for the oldest version indicates that two pages use the oldest version.
• A value of 0 in the Count fields for the next four versions indicates that no pages have been converted to the latest table definition.

You can convert data pages to the latest definition with a dummy UPDATE statement. For example, the following statement, which sets a column value to the existing value, causes the database server to convert data pages to the latest definition:

UPDATE tab1 SET col1 = col1;

This statement does not change any data values, but it converts the format of the data pages to the latest definition.

After an update is executed on all pages of the table, the oncheck -pT command displays the total number of data pages in the Count field for the current version of the table.

Alter Operations That Do Not Use the In-Place Alter Algorithm

The database server does not use the in-place alter algorithm in the following situations:

• When more than one algorithm is in use

  If the ALTER TABLE statement contains more than one change, the database server uses the algorithm with the lowest performance in the execution of the statement.

  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 values have to move 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 execute the ALTER TABLE MODIFY statement in the following section, the database server stores a row (4, 30) in dbspace1 before the alter but stores it in dbspace2 after the alter operation because 4 < 30 but "30" < "4".

Altering a Column That Is Part of an Index

If the altered column is part of an index, the table is still altered in place, but in this case the database server rebuilds the index or indexes implicitly. If you do not need to rebuild the index, you should drop or disable it before you perform the alter operation. Taking these steps improves performance.

However, if the column that you modify is a primary key or foreign key and you want to keep this constraint, you must specify those keywords again in the ALTER TABLE statement, and the database server rebuilds the index.

For example, suppose you create tables and alter the parent table with the following SQL statements:

CREATE TABLE parent 
   (si smallint primary key constraint pkey);
CREATE TABLE child
   (si smallint references parent on delete cascade
   constraint ckey);
INSERT INTO parent (si) VALUES (1); 
INSERT INTO parent (si) VALUES (2); 
INSERT INTO child (si) VALUES (1); 
INSERT INTO child (si) VALUES (2); 
ALTER TABLE parent
     MODIFY (si int PRIMARY KEY constraint PKEY);

This ALTER TABLE example converts a SMALLINT column to an INT column. The database server retains the primary key because the ALTER TABLE statement specifies the PRIMARY KEY keywords and the PKEY constraint. However, the database server drops any referential constraints to that primary key. Therefore, you must also specify the following ALTER TABLE statement for the child table:

ALTER TABLE child 
   MODIFY       (si int references parent on delete cascade
         constraint ckey);

Even though the ALTER TABLE operation on a primary key or foreign key column rebuilds the index, the database server still takes advantage of the in-place alter algorithm. The in-place alter algorithm provides the following performance benefits:

• It does not make a copy of the table in order to convert the table to the new definition.
• It does not convert the data rows during the alter operation.
• It does not rebuild all indexes on the table.

Fast Alter

The database server uses the fast alter algorithm when the ALTER TABLE statement changes attributes of the table but does not affect the data. The database server uses the fast alter algorithm when you use the ALTER TABLE statement to:

• Change the next-extent size.
• Add or drop a constraint.
• Change the lock mode of the table.
• Change the unique index attribute without modifying the column type.

With the fast alter algorithm, the database server holds the lock on the table for just a short time. In some cases, the database server locks the system catalog tables only to change the attribute. In either case, the table is unavailable for queries for only a short time.