360gb is a rather smallish table.

It is highly unlikely you have 24 cpus (I say that, because if you spent that much for hardware, but your IO throughput is such that you need 5 hours to move a mere 360gb....) - so your parallel 96 was probably very high as well.


You don't want to have to rewrite the entire table, you should have just set it unused. We'd reclaim your space if you ever reorganized the table.

There is also the fact that you loaded this column and then you turn around and want to drop it???? Why would you do that? It seems the mistake here is not that "drop column isn't parallel" but rather that you loaded this column at all.

Set unused would have been instantaneous.

DBMS_REDEFINITION could have been used (in parallel) to "reorganize" the column away.

But not loading it in the first place is absolutely the right answer don't you think? Your IO isn't very fast if it took that long to write that much data - do you really want to have to read it all from your disk array, pull it over to the server, modify it, and write it back?


In 11g, you can add a column to a table with a default value instantly. We don't even touch the table anymore. We just change the structure in the dictionary and put the default value in the dictionary once. When we access the row later - we can tell (because the column won't appear on the block at all) - if the default value should be returned or not - so we don't have to update the block at all when we add it.

ops$tkyte%ORA11GR2> alter table big_table add x varchar(4000) default rpad('*',4000,'*') not null;

Table altered.

Elapsed: 00:00:00.12
ops$tkyte%ORA11GR2> select count(*) from big_table;

  COUNT(*)
----------
   1000000