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

lorenzen_at_winternet.com (James Lorenzen) wrote:

>Vir Madhu (madhu2_at_ix.netcom.com) 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.
 

>It would be much simpler to either create the new table, selecting all
>except the the five dropped columns.
> CREATE TABLE tablename AS SELECT [the 80 columns] FROM [source table]
>-- or -
> create the table and then insert into it:
> INSERT INTO tablename SELECT [the 80 columns] FROM [source table]
 

>HTH
> James

It would be easier to write as James suggests but running it would be a problem. You would have a lot of problems with rollback segment size trying to insert 1 million rows as one transaction. Unless you can set aside a large amount of space for a huge rollback segment I think you will need commit points every n records during the insert.

Russell

--
All opinions expressed above are mine, and not my employer 
Russell McDonald  e-mail mcdonaldr_at_logica.com