Re: Migrate 200 Million of rows

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.

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.

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.

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. "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 Tue Jul 22 2003 - 10:21:24 CDT