Re: Quicker process of creating tables over dblinks

From: joel garry <>
Date: Thu, 23 Jul 2009 09:57:52 -0700 (PDT)
Message-ID: <>

On Jul 23, 2:52 am, Tim X <> wrote:
> Geoff Muldoon <> writes:
> > In article <efbc7001-9985-454a-a32b-198ad70e67b0_at_
> >>, 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

I third the advice. I researched a similar thing during the O9 days and discovered a straight CTAS over a dblink was faster than any other reasonable option given the configurations. Nowadays I might look into expdp with the network_link option, though.


-- is bogus.
Must Know Features of PL/SQL:
Received on Thu Jul 23 2009 - 11:57:52 CDT

Original text of this message