Duration of adding versus dropping a column I have a table containing more than 50 million rows.

When I add a column:

ALTER TABLE my_table ADD my_column NUMBER;


It's very fast.

Just after adding the column, I drop it.

ALTER TABLE my_table DROP COLUMN my_column;


It takes 3 minutes!

Why is adding a column so fast, when removing it without populating it is slow?
When you add a column that is nullable and don't give a default value, the database doesn't have to do anything on the table data itself. The rows are unchanged, only the table metadata is altered.

(This is due to the on-disk row format used. Null columns at the end of a row are not stored in the row pieces, in general/under normal circumstances/for ordinary data types.)

On the other hand, when you physically drop a column (rather than hide/set unused), all the rows need to be at least inspected to check if any data needs to be removed. So the theoretical best case cost is a full table scan. (There's some table locking involved too I believe.) Worst case all rows have to be rewritten. This can be quite expensive.