Re: Reduce the No. of Fields in a Oracle Table

In <3v2v8p$5mm_at_xenon.brooks.af.mil> Richard Wark <warkr_at_vanadium.brooks.af.mil> writes:
>
>If it suits your need, you may want to try the following construct:
>
>CREATE TABLE mynewtable as
> SELECT field1, field2, field3...field80
> FROM myoldtable;
>
>This will give you a new table with just the fields you want, but I
 may be
>over-simplifying the problem...
>
>Hope this helps (or gives you other ideas),
>
>Richard Wark
>Computer Sciences Corporation warkr_at_vanadium.brooks.af.mil
>Brooks AFB, San Antonio TEXAS
>http://www.txdirect.net:80/users/rwark/
>
>
>madhu2_at_ix.netcom.com (Vir Madhu ) wrote:
>>We are re_writing a Oracle application and as a part of that, I have
>>to reduce no. of fields in the table( consisting of 85 fields ) by
>>eliminating 5 fields. I am writing a program in Pro*C that reads all
>>but those 5 fields and insert into a new table i.e., the new table
 will
>>have 80 fields. There are 1 million records in this table. I am new
 to
>>oracle application development and wondering if there is a Standard
>>Oracle Tool that does this. Any pointers from the knowledgeable
 netters
>>will be highly appreciated.
>>Thank you
>>Madhu.
>

The generalized solution as suggested by richard will work. However, I don't fully know what the semantics of rollback are when create as select is used. If it is the same as insert into table..select .from, there will be a large rollback segment requirement given the million rows with 85 columns.

One of the things that can be done (a kind of nasty way) is to set those fields to null.

A more organized way would be to create another table in the desired tablespace and use PRO*C or PL/SQL perferably to read chunks of the original table and insert into the new table with intermittent commits.

Indexing may also be an issue

-krish Received on Thu Jul 27 1995 - 00:00:00 CEST