SEO

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

I forgot to mention that the local database is oracle 8.1.6 and the
remote database is oracle 7.3.4

Jay

You have my sympathy.

Look at the plan; it is very well possible lots of data is fetched from
the remote site, to be processed (and discarded) locally.
If so, a possible alternative would be to create a snapshot on the 7.3
side, using the query above, and fetch that using something like
'select * from snapshot@remote_site'.

If (re-)creating the snapshot is too cumbersome, you could partition,
or use a view.

By the way: the first commit is totally redundant; truncate table is
DDL, and commits!

Frank van Bortel