Here is a query that selects data over a dblink (several tables). It
returns data in 60-70 seconds. Inserting this data into a local table
takes a very long time, well over an hour. Am I missing something? I
have several queries going to the same set of dblinks and being
inserted to the same table and they do fine. The local table is
truncated before rows are inserted. I tried "create table as select"
with similar results. There is something specific about this query
itself. One thing I know is that the other queries take 30-35 seconds
to return. Could that matter?


The code is as follows:


truncate table zifadata;
commit;
INSERT into zifadata (mandt, darum, runcntr, appid, keyseq, amount)
SELECT '250' mandt, '20060302' datum,
'0011' runcntr, 'BA028' appid, keyseq, sum(amount) amount
FROM

(SELECT
dp.donor_number keyseq, dp.amount amount
from donor_payments@finsys dp,
payment_plans@finsys pp,
donor_visits@finsys dv,
posted_payments@finsys ppay,
item_numbers@finsys i
where pp.payment_type_indicator = 'B'
and dp.center_code = ppay.center_code
and dp.donor_number = ppay.donor_number
and dp.donor_visit_id = ppay.donor_visit_id
and dp.payment_plan_code = pp.payment_plan_code
and dp.amount > 0
and pp.center_code = dp.center_code
and dv.donor_visit_id = dp.donor_visit_id
and dv.center_code = dp.center_code
and i.item_number_code = dv.item_number_code
and ppay.drawer_transaction_id is not null
and ppay.creation_userid = 'ATMACCESS'
and ppay.creation_timestamp >= to_date(20060301, 'YYYYMMDD')
and ppay.creation_timestamp < trunc(sysdate)
)
group by keyseq;
commit ;

jk