Re: Quicker process of creating tables over dblinks

From: Tim X <timx_at_nospam.dev.null>
Date: Thu, 23 Jul 2009 19:52:45 +1000
Message-ID: <87prbrbuaa.fsf_at_lion.rapttech.com.au>



Geoff Muldoon <geoff.muldoon_at_trap.gmail.com> writes:

> In article <efbc7001-9985-454a-a32b-198ad70e67b0_at_
> 2g2000prl.googlegroups.com>, novice82 says...
>> Hello Geoff
>>
>> > IIRC, the optimizer has difficulties calculating potential index usage
>> > over DBLinks. Assuming that there are indexes on cust_id on both these
>> > tables, consider creating a view in the source database with the join on
>> > the tables there, and then select from that view over the DBLink.
>
>> I get an error saying " unable to extend temp segment by 61446 in
>> tablespace.
>> So working on the source db is not an option. Moreover, I have been
>> instructed not to use that database for any testing purposes.
>> any other ideas ?
>
> Rather than create one table based on a join over the DBLink, create TWO
> tables, each selecting from only one table over the link. Then index your
> columns and do the work (create table or just create view) locally.
>

I can second this advice. I've done this in the past and it has greatly improved performance. In my case, I used temporary global tables as I knew I needed to join all the rows and full table scans were not an issue. the performance changed from being unusable to very acceptable.

Tim

-- 
tcross (at) rapttech dot com dot au
Received on Thu Jul 23 2009 - 04:52:45 CDT

Original text of this message