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:
- The above approach can be used mainly for increasing the
performance
- Decrease the outage window at the time of Production deployment,
- 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.
No comments:
Post a Comment