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

Your question needs some clarification.
You can add the columns empty with an 'alter table' command. You can then declare a not null constraint on them that is enabled but not validated; so new, or updated, data has to meet the constraint, but old data is not checked. You can then validate the constraint (without implicitly locking the table when you think the data has been brought up to date.

That solves 'adding the columns quickly'. But you also have to consider the effects of extending every row by 4 columns as the data is brought up to date, and the question of how you are going to do the update with minimal overhead.

Possibly your PCTFREE is sufficiently large that 4 more columns will fit in most rows without pushing any rows to migrating - possibly you will end up with one migrated row per block.

You might be able to use rowid ranges to do a series of logged updates, to reduce the gross impact of rollback and redo - you might have a method of 'create table as select nologging'.

One thing you may want to consider - since the table needs a large physical manipulation - is the possibility of partitioning to reduce the table from 24GB to a number of more conveniently managed chunks (say 8 hash partitions of 3GB), or 12 range partitions of 2G (depending which is more appropriate) with local indexes. Each partition could then be updated separately, with less of a one-time impact on the whole system.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



sdfsd wrote in message <3afdc2d5$0$2901$45beb828_at_newscene.com>...


>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

>