Re: Quicker process of creating tables over dblinks
Date: Thu, 23 Jul 2009 09:57:52 -0700 (PDT)
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.
> 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.
-- _at_home.com is bogus. Must Know Features of PL/SQL: http://www.toadworld.com/BLOGS/tabid/67/EntryID/413/Default.aspxReceived on Thu Jul 23 2009 - 11:57:52 CDT