Re: What is the best way to copy 2G table data between two databases

From: Frank van Bortel <fvanbortel_at_netscape.net>
Date: Mon, 16 Feb 2004 21:55:42 +0100
Message-ID: <c0ram2$j2i$1@news3.tilbu1.nb.home.nl>

Frank van Bortel wrote:

> Daniel Morgan wrote:
>

>> Mladen Gogala wrote:
>>
>>> On Sun, 15 Feb 2004 10:03:50 -0800, Daniel Morgan wrote:
>>>
>>>
>>>> And exactly how does an insert statement get data from one database
>>>> into another? Send 2GB to a spool file?
>>>
>>>
>>>
>>>
>>> insert /*+ append */ into table1(col1,col2,col3,..,colN)
>>> select col1,col2,col3,..,colN from table2_at_remotedb;
>>>
>>> This is elementary, my dear Morgan. If I'm not mistaken, you are one of
>>> the experts that were scolding me for my objections to some oracle
>>> shenanigans not so long ago. 
>>
>>
>>
>> I would agree except that it requires going across a db link. What
>> will take forever ... can be done with TTS in minutes.
>>

>
> Guess you will have to set your students to work, then.
> From the top of my head:
> Compaq DL380/2*Pentium 4/2GB memory/O8iRel3EE - receiving end
> Compaq DL530 (? 5-something)/4 Pentium III/4GB/O8iR2SE - sending end.
>
> From flat table into partitioned table: 7.5 Million records in
> 13 minutes, using CTAS with where clause.
> Original table approx. 120M records.
>

Ah - forgot the network. We blamed the network for a slow process, so evetually it was upgarded to 1GB (copper), but this was on a 100MB section. Just like at home :)
BTW - the slow process was due to the RAID controller + RAID5 config, which had a throughput of "less than a 286 with an RLL controller". Beware of HP's 'economy', 'small office' or 'starter' configurations!

-- 

Regards,
Frank van Bortel

Received on Mon Feb 16 2004 - 14:55:42 CST