Extreme delay in inserting data

From: jk <jkelkar_at_gmail.com>
Date: 9 Mar 2006 05:00:15 -0800
Message-ID: <1141909215.512755.263200@j33g2000cwa.googlegroups.com>

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_at_finsys dp,

      payment_plans_at_finsys pp,
      donor_visits_at_finsys dv,
      posted_payments_at_finsys ppay,
      item_numbers_at_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 Received on Thu Mar 09 2006 - 07:00:15 CST