Drop columns from table in fastest way

Columns in the oracle table can have more than 1000 rows of data. If the user asked to drop such columns from the table, dropping the same will take significant amount of time.

By using “Set Unused Column”, it is possible to reduce the significant amount of time while dropping the column from the table.

The syntax used to drop the column from the table is:

Alter table Table Name drop column Column Name;

Alternatively,

If the columns to be dropped are made unused before dropping the same, the Oracle deletes this column definition from the dictionary. Space allocated to this column is not freed up. It is freed up only when you execute ALTER TABLE <name> DROP UNUSED COLUMNS statement.

Syntax for columns to make unused is

ALTER TABLE <name> SET UNUSED COLUMN <col name>;

So oracle is not having this column's definition and therefore it returns an error if any attempt made to insert value in this column.

For example, create a table “Unused_Temp” having 4 columns of type number 6.

Describe the table “Unused_Temp”, it looks as

SQL> desc Unused_Temp

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 COL_1                                              NUMBER (6)

 COL_2                                              NUMBER (6)

COL_3                                               NUMBER (6)

COL_4                                              NUMBER (6)

Insert the data to the four columns of the table ‘Unused_Temp’.

Now, make the column col_4 as unused by using

Alter table Unused_Temp set unused column col_4;

The above statement delete the column col_4 from data dictionary and space allotted to this will not be freed.

SQL> alter table Unused_Temp set unused column col_4;

Table altered.

This does not drop the column col_4, but effectively removes the ability to access from the table Unused_Temp as shown below.

SQL> desc Unused_Temp

 Name                                      Null?    Type

 ----------------------------------------- -------- -------------

 COL_1                                              NUMBER (6)

 COL_2                                              NUMBER (6)

 COL_3                                              NUMBER (6)

Now, try to insert the data to the four columns of the table ‘Unused_Temp’.

!!!!!! Error: too many values

Insert the data to the remaining three columns of the table ‘Unused_Temp’, data will be inserted.

View the total number of unused columns in a table from “user_unused_col_tabs” table.

Now, use the DROP UNUSED COLUMNS command to free up the data storage during periods of low usage and syntax is as follows:

ALTER TABLE <TABLE_NAME> DROP UNUSED COLUMNS;

For Ex:

ALTER TABLE UNUSED_TEMP DROP UNUSED COLUMNS;

The above statement drops all the unused columns from the table UNUSED_TEMP.

The above statement removes all the columns from the table currently marked as unused. 

Advantages:

1. The above approach can be used mainly for increasing the performance
2. Decrease the outage window at the time of Production deployment,
3. Makes the lives of the developer very easy at the time of deployment, as the developer need not have to wait for the long time.

 Limitations:

·         UNUSED columns continue to count toward the absolute limit of 1000 columns per table.

·         As with all DDL statements, you cannot roll back the results of this clause. You cannot issue SET USED counterpart to retrieve a column that you have SET UNUSED.

·         Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.