merge into arrival_bill a
using (
select ship_id,
bill_id,
sub_bill_id,
wrap_type,
pack_no,
gross_wt
from chdsc.edi_i_arrival_bill
where headinfo_fk_id=69832
) b
on (a.ship_id = b.ship_id and a.bill_id = b.bill_id)
when matched then update set a.sub_bill_id = b.sub_bill_id,
a.wrap_type = b.wrap_type,
a.pack_no = b.pack_no,
a.gross_wt = b.gross_wt
when not matched then insert (ship_id, bill_id, sub_bill_id, wrap_type,
pack_no, gross_wt)
values (b.ship_id, b.bill_id, b.sub_bill_id, b.wrap_type,
b.pack_no, b.gross_wt);
using (
select ship_id,
bill_id,
sub_bill_id,
wrap_type,
pack_no,
gross_wt
from chdsc.edi_i_arrival_bill
where headinfo_fk_id=69832
) b
on (a.ship_id = b.ship_id and a.bill_id = b.bill_id)
when matched then update set a.sub_bill_id = b.sub_bill_id,
a.wrap_type = b.wrap_type,
a.pack_no = b.pack_no,
a.gross_wt = b.gross_wt
when not matched then insert (ship_id, bill_id, sub_bill_id, wrap_type,
pack_no, gross_wt)
values (b.ship_id, b.bill_id, b.sub_bill_id, b.wrap_type,
b.pack_no, b.gross_wt);
估计是性能有问题,考虑以下三点:1.
select ship_id,
bill_id,
sub_bill_id,
wrap_type,
pack_no,
gross_wt
from chdsc.edi_i_arrival_bill
where headinfo_fk_id=69832
这句话运行速度2.
arrival_bill表的ship_id和bill_id是否有索引
3.
edi_i_arrival_bill表的ship_id和bill_id是否有索引