Re: what is fastest way to add 4 columns to a 100 million row table

Why not add them as 'nulls allowed', when they finally fill, alter table to make them not null.

Benefit - do it now, no additional space until you need it. Flip side - potential to introduce fragmentation; worry about that when the data is in and you can defrag in one shot.

sdfsd wrote:

> we have a table 110 million rows, with 60 columns all not null. We need to
> add new 4 columns also not null but no data is avialable for the new
> columns they will become filled as time goes on.
>
> What is the fastest way to add 4 columns any ideas? With a table that large
> and our schedule we dont have the time to test out every idea so any
> suggesions from experience or theoritical basis will be appreciated
>
> some things we are thinking of:
>
> 1 export then import?
>
> 2. create a table in parallel that is copy of old
> then create new one with columns and do create as select in parallel
>
> 3 export to flat file and then load via sql*loader
>
> 4 alter tables add columns
>
> table has indices and forgien key constraints.
>
> hardware is sun 10k with 8 cpus and 8 gig of ram no partioning in oracle.
> table is 24 gigs plus indexes
Received on Sun May 13 2001 - 02:16:13 CDT