Re: Quicker process of creating tables over dblinks

From: joel garry <joel-garry_at_home.com>
Date: Thu, 23 Jul 2009 09:57:52 -0700 (PDT)
Message-ID: <c77df9ff-d053-40e6-bdb1-8dec956c7a06_at_d36g2000prb.googlegroups.com>



On Jul 23, 2:52 am, Tim X <t..._at_nospam.dev.null> wrote:
> Geoff Muldoon <geoff.muld..._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

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.

jg

--
_at_home.com is bogus.
Must Know Features of PL/SQL: http://www.toadworld.com/BLOGS/tabid/67/EntryID/413/Default.aspx
Received on Thu Jul 23 2009 - 11:57:52 CDT

Original text of this message