Re: Migrate 200 Million of rows

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

> 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 Wed Jul 23 2003 - 11:13:10 CDT