场景:三张表分别是t_organization_sync, t_organization_relation, t_organization ,刚好是一一映射关系,每个表都有40万条数据。
任务:从sync表同步数据通过关系表同步数据到 t_organization 中,如果t_organization表中没有找到数据,就插入一条,并维护关系表。
运行结果:2个多小时都没跑完, 来大师指导。
create or replace procedure pro_org_sync_2 is
begin
merge into t_organization o
using
(
with temp as (select * from t_organization )
select sy.orga_id sy_orga_id,
sy.orga_name sy_orga_name,
sy.parent_orga_id sy_par_orga_id,
(select orga_id from temp where strand_pf_orga_id = sy.parent_orga_id) parent_orga_id,
sy.privince_code sy_pro_code,
(select orga_id from temp where strand_pf_orga_id = sy.privince_code) privince_code,
sy.city_code sy_city_code,
(select orga_id from temp where strand_pf_orga_id = sy.city_code) city_code,
sy.area_code sy_area_code,
(select orga_id from temp where strand_pf_orga_id = sy.area_code) area_code,
rel.strand_pf_orga_id ,
rel.orga_id
from
t_organization_sync sy, t_organization_relation rel where sy.orga_id = rel.strand_pf_orga_id(+)) dat
on (dat.orga_id is not null and dat.orga_id = o.orga_id)
when matched then
update
set orga_name = dat.sy_orga_name,
parent_orga_id = dat.parent_orga_id,
privince_code = dat.privince_code,
city_code = dat.city_code,
area_code = dat.area_code
when not matched then
insert (orga_id,orga_code,orga_name,parent_orga_id,privince_code,city_code,area_code)
values( dat.sy_orga_id,' ', dat.sy_orga_name, dat.parent_orga_id, dat.privince_code, dat.city_code, dat.area_code);
insert into t_organization_relation
select o.orga_id, o.orga_id from t_organization o, t_organization_relation rel
where o.orga_id = rel.orga_id(+) and rel.strand_pf_orga_id is null and o.orga_id <> '1'; -- which is special
end pro_org_sync_2;