Re: Removing a Column

Murthy S. Patamalla wrote:
>
> bjonnard_at_ix.netcom.com (Bill Jonnard) wrote:
> >This question has been bugging me for the last few years:
> >
> >Is there any way to remove a column from a table other than to
> >recreate the table (minus the offending column) and then inserting all
> >the rows into the new table?
>
> 1.I do not understand why can not do like
> CREATE TABLE(new table) xxxxx AS SELECT 1,2,3,... from TABLE(original
> table)
> This way the table is created as well as populated with data.
>
> >
> >I am attempting to remove a number of obsolete columns from a very
> >large table, and the prospect of having to solve this problem in the
> >manner described above almost makes it a pointless exercise due to the
> >amount of time, resources, etc that it would take.
>
> 2.I agree with you some extent if you are doing this for large number of
> tables but removing many columns from a single large table, this
> actually is the fastest and easiest way.
>
> >
> >Thanks for any advice. -- Bill bjonnard_at_ix.netcom.com
> >
> >
>
> OK, if I was of any help to you.
>
> Murthy

What if the 'original table' is referenced by (many) other tables and/or the original table references other tables? Then you are not only going to create a new table but (if you dont have the scripts) you will have to figure out the foreign keys and re-do them as well (definetely takes more time then simply saying 'ALTER TABLE table_name DROP column_name').

Also, depending on the storage parameters, you may copy this table to multiple extents instead of one extent (which you should try to place your table all into one extent).

Hakan Egeli
hegeli_at_ix.netcom.com Received on Sat Aug 24 1996 - 00:00:00 CEST