Insert getting exponentially slower ! : Re: Insert and Update ? (newbie)

From: Mark McHugh <mchugh.mark.ma_at_bhp.com.au>
Date: Wed, 10 Feb 1999 15:36:44 +1100
Message-ID: <79r2c9$nj111@atbhp.corpmel.bhp.com.au>

So I implemented the update and insert as suggested using this sql :

update npft_data na
set (
area_id,

calendar_week_no,
npft_action_code,
npft_effect_code,

npft_cause_code,
npft_condition_code,
product_code,
actual_thickness,
actual_width,
npft_mass,
stock_mass,
npft_hold_mass,
attributed_npft_mass,
npft_cost_per_tonne,
npft_total_cost,
report_inclusion_flag,
crew,
discharge_unit,
discharge_line_no,

resp_unit,
resp_line_no,
resp_sub_unit,

attributed_npft_cost,
resp_element_code,
actual_resp_element_code,
shift,
previous_stock_id) =
(select

nt.area_id,
nt.calendar_week_no,
nt.npft_action_code,
nt.npft_effect_code,
nt.npft_cause_code,
nt.npft_condition_code,
nt.product_code,
nt.actual_thickness,
nt.actual_width,
nt.npft_mass,
nt.stock_mass,
nt.npft_hold_mass,
nt.attributed_npft_mass,
nt.npft_cost_per_tonne,
nt.npft_total_cost,
nt.report_inclusion_flag,
nt.crew,
nt.discharge_unit,
nt.discharge_line_no,
nt.resp_unit,
nt.resp_line_no,
nt.resp_sub_unit,
nt.attributed_npft_cost,
nt.resp_element_code,
nt.actual_resp_element_code,
nt.shift,
nt.previous_stock_id

from npft_data_temp nt
where nt.pop_code = na.pop_code

and nt.stock_id = na.stock_id
and nt.discovery_date_time = na.discovery_date_time
and nt.priority_code = na.priority_code)

where (pop_code,stock_id,discovery_date_time,priority_code) in
(select pop_code,

stock_id,discovery_date_time,priority_code from npft_data_temp);

insert into npft_data select * from npft_data_temp where (pop_code,stock_id,discovery_date_time,priority_code) not in
(select pop_code,stock_id,discovery_date_time,priority_code from npft_data);

commit;

At the moment nearly all the records are new so they are being inserted. I am doing a week at a time however the time it takes for the insert to run is getting longer and longer - the first few weeks look about 1 - 2 minutes but now it is taking about 20 !

pop_code,stock_id,discovery_date_time,priority_code are the primary key of npft_data. I have removed all the other constraints from npft_data thinking they may have been the cause but it has made no difference. npft_data_temp is the table I have loaded the new data into using the sql loader. It has exactly the same structure as npft_data.

Any ideas ?

thanks,
Mark. Received on Tue Feb 09 1999 - 22:36:44 CST