Home » Articles » 8i » Here |
|
Dropping Columns
Only with the release of Oracle 8i has it been possible to drop a column from a table. Prior to this it was neccessary to drop the entire table and rebuild it. Now you can mark a column as unused (logical delete) or delete it completely (physical delete).
Logical Delete
On large tables the process of physically removing a column can be very time and resource consuming. For this reason you may decide to logically delete it:
ALTER TABLE table_name SET UNUSED (column_name); ALTER TABLE table_name SET UNUSED (column_name1, column_name2);
Once this is done the columns will no longer be visible to the user. If at a later date you have time to physically delete the columns this can be done by:
ALTER TABLE table_name DROP UNUSED COLUMNS;
On large tables you can reduce the amount of undo logs accumulated by using the CHECKPOINT option which forces a checkpoint after the specified number of rows has been processed:
ALTER TABLE table_name DROP UNUSED COLUMNS CHECKPOINT 250;
The DBA_UNUSED_COL_TABS view can be used to view the number of unused columns per table.
Physical Delete
To physically drop a column you can use one of the following syntaxes, depending on whether you wish to drop a single or multiple columns:
ALTER TABLE table_name DROP COLUMN column_name; ALTER TABLE table_name DROP (column_name1, column_name2);
Dropping a column from a table will cause all unused columns in that table to be dropped at the same time.
Hope this helps. Regards Tim...