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

In article <3afdc2d5$0$2901$45beb828_at_newscene.com>, sdfsd <sdfes_at_dsf.com> writes:
>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
>

Just alter the tables adding the 4 new nullable columns Modify application to update new columns

New rows will have data but existing rows will not

If you can provide the missing data then run update job to populate the null columns. Depending on the size of the data and existing pctfree setting some row chaining may occur. Since you want to declare the columns as 'not null' this is a required step.

Alter table modifying new coluns to 'not null'. This will block updaters.

During maintenance window rebuild table to eliminate row chaining (if necessary). If data is deleted from the table you may be able to run with columns being nullable until such time as the rows without data are purged then alter the new columns to 'not null' to ensure correct application behavior.

• Mark D Powell --
  • Posted via NewsOne.Net: Free (anonymous) Usenet News via the Web ----- http://newsone.net/ -- Free reading and anonymous posting to 60,000+ groups NewsOne.Net prohibits users from posting spam. If this or other posts made through NewsOne.Net violate posting guidelines, email abuse_at_newsone.net