Re: Migrate 200 Million of rows

nuno.tavares_at_ams.com (Nuno) wrote in message news:<b3a1403c.0307230813.293b9d97_at_posting.google.com>...
> "Ryan" <rgaffuri_at_cox.net> wrote in message news:<MFcTa.863$5b7.152_at_lakeread01>...
> > 200 million records isnt that much depending on your bandwidth between
> > instances(assuming different servers).
> >
> > avoid the insert append unless there arent that many records.
> >
> > do the create table as first. This should not take longer than an hour
> > unless you have a really slow box and a really slow connection. If your
> > worried run it over night.
>
> The connection is just between two database that are located in the
> same physical machine.
>
> > Do the create table as with the join. Anyway you can make the table you are
> > going to join the 200 million record table to REALLY small. Are just the
> > records that you are adding to the table in this temp table? then do an
> > outer join to get all the records.
> >
> Temporary table will contain just one general row to make the join
> possible.
>
> > create table as
> > select a.*,b.* from bigTable a, littleTable b
> > where a.pk = b.pk(+);
> >
> > i always forget where the (+) goes... that is probably the fastest.
> >
> > Make sure archivelog mode is turned off. Make sure nologging is set and do
> > it in parallel. Use as many slaves as you can afford.
>
> What do you mean in parallel? Using parallel clause in the insert
> into? How many degrees should I use? I have a 8 processors machine.

you need to check your max pq slaves parameter in v$parameter. See where its set. I believe the max for 8 processors is 16? You will need to bounce the server to change this I believe... Yes use the parallel clause. Also use append. use as many degrees of parallel as you can afford to spend on this procedure. if nothing else is running do 16.

you may want to consider create table as. It depends how many recores are in the existing table. If its say less than 20-30% of the 200 million consider create table as if you have the space. Drop the old table. Rename, add indexes, etc...
>
> > When you create the index do it in parallel. What kind of a server do you
> > have? Create table as and creating indexes in parallel really isnt that
> > bad.. Its not like you have 10 billion records.
>
> It's SUN with 8 processors.
>
> > "Nuno" <nuno.tavares_at_ams.com> wrote in message
> > news:b3a1403c.0307220250.5894804d_at_posting.google.com...
> > > Hi,
> > >
> > > I would like to migrate 200 Million records. Furthermore my new table
> > > will have more fields that the old one. The new table will be in
> > > different database therefore a link will be used as well.
> > >
> > > I was thinking about:
> > > Strategy A)
> > > 1) create table ... as select * from
> > >
> > > with a join with temporary table to make the new table with new
> > > columns
> > >
> > > 2) create index, contraints etc
> > >
> > > Strategy B)
> > >
> > > 1) create table
> > >
> > > 2) create index and make it unusable (I have to check if this is
> > > possible in v8.0.5)
> > >
> > > 2) Insert into /*+ APPEND */ select * from
> > >
> > > 3) rebuild the index, constraints etc
> > >
> > > Could you please help me in the best strategy to perform this task?
> > >
> > > Do you have any more suggestions
Received on Fri Jul 25 2003 - 06:35:12 CDT