Hello Experts,

I'm using the following query to create a table,which has over 7
million records. I'm writing these query's on oracle 10g using Pl/sql
developer IDE. It has been running over 12 hours and is still in
execution. Can anyone suggest me some accelration techniques, as this
query is fetching data over dblinks

create table temp1 as (
select
table_1.cust_id, table_1.invoice_amt, table_2.payment_date
from
table_1@dblink, table_2@dblink
where
table_1.cust_id = table_2.cust_id
)

I came across some posts that use the COPY command in SQL*PLUS. Is
there an equivalent command I can use for plain Oracle SQL ?

Many thanks !

novice

In article <f554af9a-1428-4f0e-a246-07052228f443
@b25g2000prb.googlegroups.com>, novice82 says...
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.

GM

Hello Geoff

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 ?

In article <efbc7001-9985-454a-a32b-198ad70e67b0@
2g2000prl.googlegroups.com>, novice82 says...
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.

GM

Geoff Muldoon <geoff.muldoon (AT) trap (DOT) gmail.com> writes:

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

On Jul 23, 2:52*am, Tim X <t... (AT) nospam (DOT) dev.null> wrote:
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
--
@home.com is bogus.
Must Know Features of PL/SQL: http://www.toadworld.com/BLOGS/tabid...3/Default.aspx