Re: Quicker process of creating tables over dblinks

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Thu, 23 Jul 2009 14:06:48 +1000
Message-ID: <MPG.24d28ce061273c88989772_at_news.x-privat.org>

In article <f554af9a-1428-4f0e-a246-07052228f443 _at_b25g2000prb.googlegroups.com>, novice82 says...
> 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_at_dblink, table_2_at_dblink
> where
> table_1.cust_id = table_2.cust_id
> )

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 Received on Wed Jul 22 2009 - 23:06:48 CDT