Home » RDBMS Server » Performance Tuning » Drop Unused columns (12.1.0.2.0,Linux x86_64)
Drop Unused columns [message #639801] Thu, 16 July 2015 08:14 Go to next message
preet_kumar
Messages: 199
Registered: March 2007
Senior Member
I need to drop few columns from a table with over billion records and i tried both the below methods which takes almost 6hours

alter table abc set unused columns col1;
alter table abc drop unused columns;
AND
alter table abc set unused (col1,col2,col3,col4,col5);
alter table abc drop unused columns;

Is there any way reduce the drop time ?
Re: Drop Unused columns [message #639804 is a reply to message #639801] Thu, 16 July 2015 08:20 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
>Is there any way reduce the drop time ?
no, no way
Re: Drop Unused columns [message #639805 is a reply to message #639801] Thu, 16 July 2015 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 13015
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd try create table as select from original table minus the columns you don't want.
Re: Drop Unused columns [message #639809 is a reply to message #639805] Thu, 16 July 2015 09:28 Go to previous message
bpeasland
Messages: 51
Registered: February 2015
Location: United States
Member

I might be faster to just mark the columns as unused and leave it at that. You will still have the columns there, technically. But dropping the column isn't going to get space back anyway.

Cheers,
Brian
Previous Topic: Help for tuning the sql Query
Next Topic: Tuning data deletion that is taking long time
Goto Forum:
  


Current Time: Fri Feb 23 04:57:56 CST 2018

Total time taken to generate the page: 0.07589 seconds