To Drop Or Not To Drop A Column – Find Out The Answer

Don’t you love it when people come to you for advice? I sure do!

I got a call the other day from a developer. He didn’t understand why adding a column is very fast, almost instantaneous, but dropping a column takes 6-10 min to complete.

I asked for all the details I needed, and told him I’ll get back within a few hours. Then I rolled up my sleeves and went into research mode.

It’s been a while since my Oracle days in school, so I needed a little refresher on what is happening when you drop a column,and what other alternatives exist.

And then I got curious. I asked myself some more questions that required some testing.

Now I am here sharing my findings with you!

1.What’s Happening Behind The Scene When You Drop A Column?
2.What Is The Alternative?
3.Various Questions Answered On Setting Column Unused.

1.What’s Happening Behind The Scene When You Drop A Column?

Dropping a column on a small table is no big deal. The operation completes pretty fast.
However, when you have a large table, the story is completely different.
The statement takes time to complete. One might think that Oracle is hung up.

Why is dropping a column so time consuming?

This is what happens when you issue the drop column command:

• Oracle reads each block of the table
• Oracle removes the column from each row
• Oracle rewrites the block where the column was removed.

While the column drop is in progress, Oracle is holding an exclusive lock on the table, which means no DDL and no DML is allowed on the table. Anyone who issues DDL or DML, will be waiting for the drop column to complete.
This could mean that parts of the application that runs on your database will not be available or will not be functioning properly while the drop command is running.

SQL Statement:
alter table test drop column X;

2.What Is The Alternative?

There is another solution!
You can set the column to unusable! Why is this a great approach?
Because it is very quick! No more reading and rewriting blocks of data. No.

All what’s happening is updates of the data dictionary.

What are the cons for setting the column unusable?
The only thing I can think of is the space that the column is using in the database. When you set the column to unused, the space is not freed.

After you set the column unusable, and at a convenient time, you could actually drop the unused columns on the table.

SQL Statement:
alter table test set unused column X;
alter table t drop unused columns;
alter table t drop unused columns checkpoint 5000;  --recommended. this will commit every 5000 rows

In 12c, there is another option as well. You can set the column invisible. This will be the topic of a separate post.

3.Various Questions Answered On Setting A Column Unusable

I can hear the questions coming…

Q: After I set column X unusable, can I add another column with the same name X?
A: Yes! Column is renamed to a different name, and it doesn’t show up anymore in the table

Q: Can I undo an unusable column statement?
A: No. Dropping or setting a column to unusable are irreversible statements. There is no undo or flashback. The only way to get it back is to restore your database.

Q: Will dbms_metadata extract the unusable columns as well, when getting the ddl of a table?
A: No. Column is no longer available in the table.

Q: Will export/import datapump, extract the unusable columns as well?
A: No. Column is no longer available in the table.

Q: Where can I find information about unusable columns?
A: dba_unused_col_tabs, dba_tab_cols

Example:

SQL>create table test (a number, b varchar2(2), c number);

Table created.

SQL>alter table test set unused column c;

Table altered.

SQL>select * from dba_unused_col_tabs where table_name='TEST';

OWNER        TABLE_NAME      COUNT
------------ --------------- ----------
DROBETE      TEST            1

SQL>select TABLE_NAME,COLUMN_NAME from dba_tab_cols where table_name='TEST';

TABLE_NAME         COLUMN_NAME
----------------- ------------------------------
TEST              SYS_C00003_17062122:43:09$     -->>Column C was renamed
TEST              B
TEST              A

SQL>desc test     -->>Column C doesn't show up
 Name      Null?    Type
 --------- -------- ------------------
 A                  NUMBER
 B                  VARCHAR2(2)

SQL>alter table test add c number;   -->> Add column C again

Table altered.

SQL>desc test
 Name      Null?    Type
 --------- -------- ------------------
 A                  NUMBER
 B                  VARCHAR2(2)
 C                  NUMBER

SQL>alter table test set unused column c;  -->>Drop column again

Table altered.

SQL>select * from dba_unused_col_tabs where table_name='TEST';

OWNER        TABLE_NAME                          COUNT
------------ ------------------------------ ----------
DROBETE      TEST                                    2  -->>Counts go up

SQL>select TABLE_NAME,COLUMN_NAME from dba_tab_cols where table_name='TEST';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
TEST                           SYS_C00004_17062122:50:23$
TEST                           SYS_C00003_17062122:43:09$
TEST                           B
TEST                           A

--Extract DDL - no sign of unused column

SQL>select dbms_metadata.get_ddl('TABLE','TEST','DROBETE') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST','DROBETE')
-----------------------------------------------------

  CREATE TABLE "DROBETE"."TEST"
   (    "A" NUMBER,
        "B" VARCHAR2(2)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

--Lets drop unused columns

SQL>alter table drobete.test drop unused columns;

Table altered.

SQL>select * from dba_unused_col_tabs where table_name='TEST';

no rows selected

SQL>select TABLE_NAME,COLUMN_NAME from dba_tab_cols where table_name='TEST';

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
TEST                           B
TEST                           A

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana