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. When you specify a PRIMARY KEY constraint in the MODIFY clause, the database server also silently creates a NOT NULL constraint on the same primary key column. 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 can provide performance benefits, including the following:
  • 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.
Warning: If you alter a table that is part of a view, you must re-create the view to obtain the latest definition of the table.

Copyright© 2019 HCL Technologies Limited