Re: Removing a Column

In article <321E9997.5213_at_ix.netcom.com> Hakan Egeli <hegeli_at_ix.netcom.com> writes:
>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').

Well, if you are dropping columns that constrain other tables, you're going to have a lot of work to do. Otherwise, just drop the constraints while doing this process. You do have scripts to recreate your constraints, right?

>
>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

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O