Posted by You’ve got a huge table right? Massive! Immense! And then something bad happens. You get asked to remove one of the columns from that table.
“No problem” you think. “I won’t run the ‘drop column’ command because that will visit every block and take forever!”
So you settle on the perfect tool for such a scenario – simply mark the column as unused so that it is no longer available to application code and the developers that write that code.
But there’s a catch that not many people know about. SET UNUSED is meant to be just a trivial database dictionary operation. We don’t touch the data, we only manipulate the column definition and thus an execution of SET UNUSED should be instantaneous. This is indeed the case most of the time, but as the example below shows – the way the column was added to the table, can have a bearing on what it costs to have that column removed.
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--
-- large-ish table to monitor the impact
--
SQL> create table t1
2 as
3 select d.*
4 from dba_objects d,
5 ( select 1 from dual connect by level <= 100);
Table created.
SQL> set timing on
SQL> select count(*) from t1;
COUNT(*)
----------
8713700
Elapsed: 00:00:01.92
--
-- Take an existing column to UNUSED ... instantaneous!
--
SQL> alter table t1 set unused column created;
Table altered.
Elapsed: 00:00:00.06
--
-- Add a new column, and then make it UNUSED ... instantaneous!
--
SQL> alter table t1 add new_col1 timestamp;
Table altered.
Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col1;
Table altered.
Elapsed: 00:00:00.00
--
-- Add a new columns with a not null default, and then make it UNUSED ... careful!
--
SQL> alter table t1 add new_col2 timestamp default sysdate not null;
Table altered.
Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col2;
Table altered.
Elapsed: 00:01:35.39
You will not get the same issue in 12c.
SQL> select banner from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> create table t1 nologging
2 as
3 select d.*
4 from dba_objects d,
5 ( select 1 from dual connect by level <= 100);
Table created.
SQL>
SQL> set timing on
SQL> select count(*) from t1;
COUNT(*)
----------
7951500
Elapsed: 00:00:02.20
SQL> alter table t1 set unused column created;
Table altered.
Elapsed: 00:00:00.08
SQL>
SQL> alter table t1 add new_col1 timestamp;
Table altered.
Elapsed: 00:00:00.00
SQL> alter table t1 set unused column new_col1;
Table altered.
Elapsed: 00:00:00.00
SQL>
SQL> alter table t1 add new_col2 timestamp default sysdate not null;
Table altered.
Elapsed: 00:00:00.01
SQL> alter table t1 set unused column new_col2;
Table altered.
Elapsed: 00:00:00.01
SQL>
Learning is not a spectator sport 
Oooooh! Nice!
Another chance for me to convince the PTB to let me upgrade all our dbs to 12.2!