merge into 效率好低

场景：三张表分别是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;