Re: coordinate my OracleDB with a foreign OracleDB: time-problem

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Mon, 09 Jan 2006 21:58:44 +0100
Message-ID: <dpui51$uhf$1@news6.zwoll1.ov.home.nl>

Andreas Mosmann wrote:
> Frank van Bortel schrieb am 07.01.2006 in
> <dpohfg$hpo$1_at_news1.zwoll1.ov.home.nl>:
> Thanks for your answer, Frank,
>

>> I'm not in Delphi, but here's 2 cents worth:
>> - Is there a way to bypass your Delphi program, and
>>   create a database link, and just do
>>   'create table A as select * from table A_at_OtherDB;'?!?
>>   It surely would be a heck of a lot faster!

>
> Sure, but first it is not allowed to me and 2nd in later times this way
> sure will not exist, thats why the 2-level-architecture.

Okay, no bypass, but can you do it from *within* Delphi?

>

>>   Drop the table after you truncated it, if it's large
>>   (500k records is *not* considered large).

>
> Isn't it enough to truncate it? Why?

Because create table as select is a create (DDL) statement. And it will fail on existing tables.
DDL (like CTAS) will generate even less redo that insert --+APPEND.

Dropping a table will cause checks (=delays) to fire, which will not fire when issuing a truncate. Thus the subsequent drop (of an empty table) is faster.

[Snipped network bla - not the cause]

Try statspack on your MyDB. Take a snap before you start, and one right after you finish.
Then generate the statspack report, and see what you're waiting for; resolve the greatest waits first, work your way down until you have a satisfactory response time.

Statspack comes with every 9i and 10i database, and is even available for 8i, though crippled.

Looking at your Delphi code: looping through a cursor is probably the least efficient way to fill MyDB from OtherDB.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Received on Mon Jan 09 2006 - 14:58:44 CST