SQL语句效率

从开始傻乎乎的select查询后插入
今天终于找到了解决办法

使用外连接先将表拼好后直接insert进入新表 只需要6秒钟

比之前感觉一个月才能插入好不知道提升了多少倍！！！
ctmd

CREATE OR REPLACE
procedure isertALL_2020 is
begin
	--统计每个班的人数，建立新表
  --新建游标suts
Declare cursor suts is  SELECT a.XX_JBXX_ID,a.XX_NJXX_ID,a.XX_BJXX_ID,b.XXDZDM,b.XXZDCXLXDM,b.XXBXLXDM,c.JYJD,c.RXNF,c.XJNJDM,d.BJLXDM,a.COUNTS
FROM
(select XX_JBXX_ID,XX_NJXX_ID,XX_BJXX_ID,COUNTS from ZXXS_XS_JBXX_COUNT_1)a
left join 
(select XX_JBXX_ID,XXDZDM,XXZDCXLXDM,XXBXLXDM from ZXXS_XX_JBXX_1)b
on a.XX_JBXX_ID=b.XX_JBXX_ID
left join 
(SELECT XX_NJXX_ID,JYJD,RXNF,XJNJDM FROM ZXXS_XX_NJXX_1)c
on a.XX_NJXX_ID=c.XX_NJXX_ID
left join 
(Select XX_BJXX_ID,BJLXDM FROM ZXXS_XX_BJXX_1)d
on a.XX_BJXX_ID=d.XX_BJXX_ID;
  begin
    --开始循环
    for sut in suts loop
      begin
insert into XX_ALL_2020 (XX_JBXX_ID,XX_NJXX_ID,XX_BJXX_ID,XXDZDM,XXZDCXLXDM,XXBXLXDM,JYJD,RXNF,XJNJDM,BJLXDM,COUNTS)
        values (sut.XX_JBXX_ID,sut.XX_NJXX_ID,sut.XX_BJXX_ID,sut.XXDZDM,sut.XXZDCXLXDM,sut.XXBXLXDM,sut.JYJD,sut.RXNF,sut.XJNJDM,sut.BJLXDM,sut.COUNTS);
      end;
    end loop;
    commit;
  end;
end;

这里是多个表的字段拼接

CREATE OR REPLACE
procedure isertID is
begin
	--统计每个班的人数，建立新表
  --新建游标suts
  Declare cursor suts is  SELECT distinct a.XX_BJXX_ID, a.XX_JBXX_ID,a.XX_NJXX_ID,b.COUNTS
FROM
(select XX_JBXX_ID,XX_NJXX_ID,XX_BJXX_ID from XS_JBXX_2018 where XX_BJXX_ID  is not null  )a
left join 
(select XX_BJXX_ID,count(*) COUNTS from XS_JBXX_2018 WHERE XX_BJXX_ID is not null group by XX_BJXX_ID)b
on a.XX_BJXX_ID=b.XX_BJXX_ID;
  begin
    --开始循环
    for sut in suts loop
      begin
insert into XX_MERGE_2018 (XX_JBXX_ID,XX_NJXX_ID,XX_BJXX_ID,COUNTS)
        values (sut.XX_JBXX_ID,sut.XX_NJXX_ID,sut.XX_BJXX_ID,sut.COUNTS);
      end;
    end loop;
    commit;
  end;
end;

这里是单标去重查询数量

完事
感觉还是连接好！