Re: Migrate 200 Million of rows

Hi!

Note that you are using db_link and if your network will be the bottleneck, parallel clauses might not help at all.
Also, when you issue commant:

insert /*+ APPEND PARALLEL(x) */ into localtable select * from remotetable;

That means that parallel slaves will be executed only in your local machine, where insert is done. To get parallel query on remote side too, you ought to run following:

insert /*+ APPEND PARALLEL(x) */ into localtable select /*+ PARALLEL(x) */ * from remotetable;

But since everything still comes through one network connection, it might be pointless to use any parallel (depending on net speed of course)

Tanel.

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0307250335.29832299_at_posting.google.com...
> 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 - 07:51:42 CDT