loopback database link very*n slow, why ??

why the loopback database link is so slow ? four cases (from easy to complicated) are presented:

1. select count(*) from ( SELECT distinct tt.* FROM tbAct tt.... (result set: 342 row) )
   ---> this LOCAL complicated query takes only 1 sec
   
2. insert into tmpAct SELECT distinct tt.* FROM tbAct tt.... (result set: 342 row)
   ---> inserting LOCAL table with the same complicated query takes only 1 sec
   ## tables tmpAct and tbAct have the same schema ##
3. insert into tmpAct_at_odbhq SELECT * FROM tbAct where rownum < 342; (result set: 342 row)
   ---> inserting REMOTE table(by db link) with the simplest query takes only 1 sec
   
4. insert into tmpAct_at_odbhq SELECT distinct tt.* FROM tbAct tt.... (result set: 342 row)
   ---> inserting REMOTE table(by db link) with the same complicated query
   takes 80 sec !!!!!

note that:

1. _at_odbhq is a loopback database link
2. I think the complicated query is locally processed. that is, only the DATA TO INSERT will cross over the database link. (complicated query has nothing to do with db link)

why the last case takes 80 SECONDS ???
any one have idea about that & how to improve it ? any comment is greatly appreciated.

Jerry Perng, Received on Thu Mar 14 2002 - 06:10:44 CET