2万条数据delete要31秒,而同样insert2万条数据只须7秒,问:为什么delete要慢这么多?以下为delete和insert代码,请大虾帮我分析为什么delete要比insert慢这么多?
1.删除语句
DELETE FROM fnc_container_activities_temp
Where cac_cda_apid=i_apid;
IF sql %NOTFOUND THEN
NULL;
END IF;
DELETE FROM fnc_container_act_bak_temp
where cac_cda_apid=i_apid;
If sql%notfound Then
null;
end if;
DELETE FROM fnc_container_data_temp
WHERE
cda_apid=i_apid;
IF sql %NOTFOUND THEN
NULL;
END IF;
DELETE FROM fnc_groupfee_data_temp t
WHERE
gfd_apid =i_apid;
IF sql %NOTFOUND THEN
NULL;
END IF;
2.插入语句
begin
insert into fnc_container_data_temp(cda_apid,cda_ieid,cda_cstmcd,cda_cntrid,
cda_cntrno,cda_middle_ship,cda_in_termcd,cda_out_termcd,
cda_fbus_termcd,cda_sbus_termcd,
cda_fbus_ieid,cda_sbus_ieid,cda_copercd,cda_vescorp_code,
cda_ccr_cstmcd,cda_fe,cda_ie,cda_size,cda_goodstype,
cda_standard,cda_ldport,cda_unldport,cda_dstnport
,cda_trade)
select 1,cnt_sdlb_id,nvl(fa_fun_wbq_hbcode(cnt_hb_id),'MSKL'),t.cnt_id,t.cnt_cntno,
decode(t.CNT_CSLOADMA_ID,null,'N','Y'),
fa_fun_getshdock_code(CNT_UNLOADVYGMA),
fa_fun_getshdock_code(CNT_LOADVYGMA),
fa_fun_getshdock_code(CNT_CSLOADMA_ID),
fa_fun_getshdock_code(CNT_CSUNLOADMA_ID),
cnt_cs_vyg_id,
cnt_csunloading_vyg_id,
fa_fun_wbq_hbcode(cnt_hb_id),
fa_fun_wbq_op(cnt_ci_id),
fa_fun_wbq_op(cnt_dcr_id),
fa_fun_getctnef(CNT_EF),
FA_FUN_GETCTNEI(CNT_EF),
cnt_cs_id,
decode(cnt_ct_id,5,'R',decode(CNT_DC_ID,null,'D','G')),
'Y'
,CNT_LDPORT,
CNT_DSTPORT,
CNT_DSTPORT
,decode(fa_fun_getctntrade(cnt_ef),1,'RG',2,'RT',3,'RI',4,'TM',
5,'RN',6,'NT',7,'GL',8,'RL',9,'UT',10,'CD')
from fa_tbl_container t
where cnt_sdlb_id=10065 ;
cnt_vyg_id in (177689,179247,169455,175757,169218,172628,168213,173039,180379,180359,175909,170985);
cnt_vyg_id in (182608,174768,174685,175866,180435,170050,171219,174467,174475,173114,176288,173482,178384);
fnc_s_gen_data_p(1,null);
commit;
end;
|