how to drop a column in a huge table [message #519007] |
Mon, 08 August 2011 20:34 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Dear all,
I want to drop a column in a huge table which contain about 420,000,000 rows,i use the alter table drop coumn command to execute,and found it takes a long time and generate huge redo.
Is there any quickly way to drop a column in a huge table?
|
|
|
|
|
Re: how to drop a column in a huge table [message #519010 is a reply to message #519009] |
Mon, 08 August 2011 20:53 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
andy huang
Messages: 498 Registered: July 2011
|
Senior Member |
|
|
Hi,
1) CREATE VIEW without the column. The method is not drop the column really.
2) alter column UNUSED The method is not drop the column really also.
3) DBMS_REDEFINITION The method can drop the column,but i think it will take a long time to create mv for base table .
|
|
|
|
Re: how to drop a column in a huge table [message #519030 is a reply to message #519010] |
Tue, 09 August 2011 01:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I'd set the column as UNUSED and then really drop it during a maintenance window.
If you have none, then use there is no other way then to use dbms_redifinition.
Regards
Michel
|
|
|