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

In article <989749491.21223.0.nnrp-13.9e984b29_at_news.demon.co.uk>, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

I was trying to get at fastest way to do it

the table would have 4 new columns added to every existing row they would be empty for now but could get data in future for every row or just a subset of them

>
>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.

how can we determine if this is the case?

>
>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'.

do understand, logged updates when data comes to update the 4 new columns in existing rows? or in new rows created since adding columns

>
>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.
>

we love ot get partioning problem is Oracle want close to $400k upfront.

our dbas have going to be responsible for this but we, as Oracle developers want to make sure that the plans they have are solid.

Which goes back to orginal question. Given a 100 million row table we want to add 4 columns they will have no data intially but they might have it later.

two quesitons:

what is the 'FASTEST' way to do it. i think you said an alter table would do it

Is that the BEST way. is an alter table going to increase risk of row migartion if we update those columns later?

>
Received on Sun May 13 2001 - 09:42:08 CDT