Dropping Column

You all have dropped or added a column to an employee table. How long does that take? couple seconds, or may be milli-seconds. ALTER is DDL command therefore, it is not reversible once the command is executed. The user can drop columns from a table owned by the user. You can drop column from another table only if you ALTER ANY TABLE SYS privilege. Generally, DBA role has this privilege.

SYNTAX:

ALTER table_name DROP COLUMN (column1);
ALTER table_name DROP COLUMN (column1, column2, column3);

Reason:  We drop column because it is no longer need by a table which also free up some space in a database. You cannot drop all the columns from a table, nor you can drop columns from tables owned by SYS. The DROP COLUMN statement removes the column descriptor and the data associated with the target column are removed from each row in the table. You can drop one or multiple columns at a time.

Restrictions: There are some restrictions on dropping a column for example, you cannot drop a column that is a partition key of a partitioned table. Also, you cannot drop a column that is part of primary key of an Index Organized Table (IOT). If there is parent child relationship, a parent column can't be dropped without dropping child column because a child cannot be orphaned or parentless. Child column should be dropped before Parent column or your FK constraints should be disabled. Disabling constraint is not the best thing to do and it is a dangerous thing.

Stop!: Never run a DROP COLUMN statement before checking the table size and how busy the table is. The table can be very small, medium, or large and either super busy, not busy or medium busy ( is this a term?). What is our definition of small, medium and large table? The size may very with individual and company. In our demo, a small table has below million, medium is under over million and less than 5 millions, and large is of course over 5 millions. Dropping strategy is different based on the table size, we will discuss the different strategy below.

Small Table:

ALTER table_name DROP COLUMN (column1);
ALTER table_name DROP COLUMN (column1, column2, column3);

This is very straight forward which everyone knows right from school and it does not take long if the table is not locked and the size is small.

Medium Table:

1. Create DDL for all the grants on this table.
2. Create another table using CTAS ignoring column(s) that you want to drop. (see how here)
3. Recreate Constraints if any because CTAS only copies data and table structure minus constraints.
4. Grant all the permission and privilege collected in step 1.

This is a multi-steps process which might take little longer and you need to make sure you have enough space for this job.

Large Table:

1. Mark deleting column as UNUSED.
2. Drop unused column during maintenance window.

This is what DBA do for big tables and medium table as well. Some people use Medium table strategy for everything but small tables. Medium table strategy is absolutely NO for Large Table scenario because it is time consuming and may not have space to accommodate temp table and involves too many steps.

I have not discussed making unused before therefore, I will discuss the syntax and the mechanism behind this here and I promise to make is simple, easy and short,

Marking Columns Unused: If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and it's name is removed so that a new column can reuse that name. All Constraints, Indexes, and Statistics defined on the column are also removed.
SYNTAX:

ALTER TABLE hr.employee SET UNUSED (column1);
ALTER TABLE hr.employee SET UNUSED (column1, column2);

You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

Removing Unused Columns: The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

DROP column command consumes a lot of database I/O and hence should be avoided during peak hours. Setting a column as UNUSED marks a column for logical deletion and the columns are no longer available for DML, DDL commands. But the data in the columns are still intact in the segment and not dropped.

UNUSED  is much faster and as it doesn't update the data file with the changes. The actual DROP will take place after issuing DROP UNUSED columns. DROP will drop the column data from the segment right away and therefore it takes a while.