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

"sdfsd" <sdfes_at_dsf.com> wrote in message news:3afe9c9e$0$2901$45beb828_at_newscene.com...
> 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?

How do you *ever* check whether you've got row migration???????? (I'm presuming you *do* check about row migration from time to time??? Prepare for a shock or three if not)

Alter table blah compute statistics

The select chain_cnt from dba_tables, selecting for the relevant table name. If you've a number reported, you've row migration or possibly row chaining, but you'd have to look at the table definition to work out the liklihood of being the one or the other).

(My apologies: I've just read on a few paragraphs and discovered you are mainly a developer, not a DBA. That would explain it then).

Incidentally, I don't know whether anyone has mentioned it before or not, but you realise that this sort of DDL on a table will render all references procedures, packages, views and triggers invalid, and they'll need recompiling before they work again. Of course, you could let Oracle do the automatic recompile for you, but I don't rate your performance chances whilst that's going on. (Select * from dba_objects where staus='INVALID' if you're interested in finding out what falls over as a result of such extensive non-adverse DDL).

Regards
HJR
>
> >
> >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 Tue May 15 2001 - 07:12:31 CDT