select t1.xmlb,t1.xmbh from cw_k3xmdz t1,cw_v_wph_yy t2 where t1.xmlb = '05' and t1.xmbh = t2.wph(+) and t2.wph is null; 为什么这里检索里有一条记录delete cw_k3xmdz T1 where xmlb = '05' and xmbh in ( select t1.xmbh from cw_v_wph_yy V2 where T1.xmlb = '05' and T1.xmbh = V2.wph(+) and V2.wph is null ); 而这里处理却是0行?
用外联接试一试! 速度将会得到很大的提高的 delete t1 from t1 left out join t2 ON t1.a <> t2.a
语句搞错了: delete t1 from t1 left outer join t2 ON t1.a <> t2.a
楼上,你这是MSSQL SERVER的语法吧? welyngj(平平淡淡): delete t1 where not exists (select * from t2 where t1.a=t2.a) 加索引我若还要加其它条件呢?如: select t1.xmlb,t1.xmbh from cw_k3xmdz t1,cw_v_wph_yy t2 where t1.xmlb = '05' and t1.xmbh = t2.wph(+) and t2.wph is null; 为什么这里检索里有一条记录delete cw_k3xmdz T1 where xmlb = '05' and xmbh in ( select t1.xmbh from cw_v_wph_yy V2 where T1.xmlb = '05' and T1.xmbh = V2.wph(+) and V2.wph is null ); 而这里处理却是0行?
呵呵,在SQL Server查询分析器中测试,直接搬过来了! 意思是: SELECT * FROM 表1,表2 WHERE 表1.RECORDNO=表2.RECORDNO(+) AND 表2.RECORDNO IS NULL AND 表2.BIRTHDAY(+)=’710618’;
单纯优化这条语句的话,可能没什么效果,不用not in 可以用not exist 看看 如果是要提高删除速度 1.删除索引,完了后重建索引 2。建立临时表 create table# as select a from t1 minus select a from t2; 然后delete from t1 where exists (select * from table# where t1.a=t#.a)
记录多而索引的存在,也是影响delete的原因,oracle要破坏索引表.
Not in 及in是特别不建设用的,把它改为exists方式是比较好的选择, welyngj是不错的, delete t1 where not exists (select 'x' from t2 where t1.a=t2.a)确保a这列有索引。
加索引
T2是一个视图,是从一个有5W条记录的表里检索出近1K条记录的视图
为什么这里检索里有一条记录delete cw_k3xmdz T1
where xmlb = '05' and xmbh in (
select t1.xmbh from cw_v_wph_yy V2 where T1.xmlb = '05' and T1.xmbh = V2.wph(+) and V2.wph is null
);
而这里处理却是0行?
delete t1 from t1 left out join t2 ON t1.a <> t2.a
delete t1 from t1 left outer join t2 ON t1.a <> t2.a
welyngj(平平淡淡):
delete t1 where not exists (select * from t2 where t1.a=t2.a)
加索引我若还要加其它条件呢?如:
select t1.xmlb,t1.xmbh from cw_k3xmdz t1,cw_v_wph_yy t2 where t1.xmlb = '05' and t1.xmbh = t2.wph(+) and t2.wph is null;
为什么这里检索里有一条记录delete cw_k3xmdz T1
where xmlb = '05' and xmbh in (
select t1.xmbh from cw_v_wph_yy V2 where T1.xmlb = '05' and T1.xmbh = V2.wph(+) and V2.wph is null
);
而这里处理却是0行?
意思是:
SELECT * FROM 表1,表2
WHERE 表1.RECORDNO=表2.RECORDNO(+)
AND 表2.RECORDNO IS NULL AND 表2.BIRTHDAY(+)=’710618’;
如果是要提高删除速度 1.删除索引,完了后重建索引
2。建立临时表 create table# as select a from t1 minus select a from t2; 然后delete from t1 where exists (select * from table#
where t1.a=t#.a)
delete t1
where not exists (select 'x' from t2 where t1.a=t2.a)确保a这列有索引。