替代sql in 性能优化

 

create table bakInfo(
 id int,
 pname varchar2(10),
 remark varchar2(10),
 impdate varchar2(10),
 upstate varchar(10));
 
 create table Info(
    id int,
    pname varchar2(10),
    remark varchar2(10),
    impdate varchar2(10),
    upstate varchar(10));
    
insert into Info values(1,'1111','1111','1111','1111'); 
insert into Info values(2,'2222','2222','2222','2222'); 
insert into Info values(3,'3333','3333','3333','3333'); 
insert into Info values(4,'1111','1111','1111','1111'); 
insert into Info values(5,'2222','2222','2222','2222'); 
insert into Info values(6,'3333','3333','3333','3333'); 
    
insert into bakInfo values(1,'1111','1111','1111','1111'); 
insert into bakInfo values(2,'2222','2222','2222','2222'); 
insert into bakInfo values(3,'3333','3333','3333','3333'); 
insert into bakInfo values(4,'4444','4444','4444','4444'); 
insert into bakInfo values(5,'1111','1111','1111','1111'); 
insert into bakInfo values(6,'2222','2222','2222','2222'); 
insert into bakInfo values(7,'3333','3333','3333','3333'); 
insert into bakInfo values(8,'4444','4444','4444','4444'); 

select * from bakInfo;
select * from info;  
  
select * from bakInfo where id in (select id from info);
select b.* from bakinfo b  INNER join info i on b.id = i.id;

create index bakinfo_index on  bakinfo(id);
create index info_index on  info(id);


insert into bakInfo
  (id, PName, remark, impdate, upstate)
  select id, pname, remark, impdate, upstate
    from 　　 (SELECT Info.id,
                    Info.pname,
                    Info.remark,
                    Info.impdate,
                    Info.upstate,
                    b.id bid 　　
               FROM Info
               left JOIN bakInfo b
                 ON Info.id = b.id) t
   where t.bid is null;





去 in  性能提高10倍以上。