Home » SQL & PL/SQL » SQL & PL/SQL » Drop column query
Drop column query [message #190028] Tue, 29 August 2006 01:47 Go to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
Hi,

I fired a query(as mentioned below)in order to drop a column from a table consisting of 5 lac records. The column curr_no was having null in all the records. I found that it took approximately 12 secs for this column to get dropped. I was not able to understand the reason for this delay.

alter table account_mly drop column curr_no;

Thanks

Re: Drop column query [message #190036 is a reply to message #190028] Tue, 29 August 2006 02:02 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Although I haven't got the slightest idea what a lac is (in French, it is a lake Very Happy) but DDL demands quite a lot of resources and will take some time.

MHE

[Updated on: Tue, 29 August 2006 02:05]

Report message to a moderator

Re: Drop column query [message #190037 is a reply to message #190028] Tue, 29 August 2006 02:03 Go to previous messageGo to next message
gopi_ora
Messages: 13
Registered: August 2006
Location: Bangalore
Junior Member
Hi,

The reason for the time consumption is, the drop column statement will physically remove the column from the table and rewrite the whole table again ,also it will reclaim the disk space.

Thanks.
Re: Drop column query [message #190038 is a reply to message #190037] Tue, 29 August 2006 02:06 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
gopi_ora wrote on Tue, 29 August 2006 09:03

...and rewrite the whole table again ,also it will reclaim the disk space.
Are you sure about this?

MHE
Re: Drop column query [message #190061 is a reply to message #190028] Tue, 29 August 2006 03:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you can do is:
ALTER TABLE <table> SET UNUSED COLUMN <column>

This will make the column invisible and unreferencable.
You can then drop it later when you have the time.

I believe dropping the column will free up space in the individual blocks of the table, but it won't alter the total number of used blocks or the high watermark.
Re: Drop column query [message #190071 is a reply to message #190061] Tue, 29 August 2006 03:53 Go to previous messageGo to next message
be2sp1
Messages: 52
Registered: September 2005
Location: India
Member
I used to believe that dropping the column name will reclaim the space used by the data blocks and reset the HWM.

Thanks
Re: Drop column query [message #190089 is a reply to message #190071] Tue, 29 August 2006 04:45 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would require a full reorg of the table, which would invalidate the indexes and require them to be rebuiilt.
As this does not happen, we can conclude that the DROP COLUMN does not reclaim the space in this fashion.
Previous Topic: Select from List of numbers...interesting error!
Next Topic: Hiding Package
Goto Forum:
  


Current Time: Fri Dec 09 03:52:39 CST 2016

Total time taken to generate the page: 0.12059 seconds