delete school where school.client_id not in (select child.client_id from child)我在Delete语句中使用了Not in语句，School和Child表分别有10多万条数据，交叉查询次数上百亿次，所以速度很慢，请问有没有优化的方法?谢谢！！！
--先分别在两表的client_id字段上建立索引，后用如下查询 delete a from school as a inner join child as b on a.client_id=b.client_id
delete school where school.client_id not in (select child.client_id from child)----------------------------------------------------------- --client_id建索引,用exists不知道效果會不會好點... delete school where not exists (select 1 from child where child.client_id=school.client_id)
--是我搞错了，抱歉。 如果school表没有级联关系，可以试一下临时表 Select a.* into #school from school as a inner join child as b on a.client_id=b.client_idtruncate table school Insert into school Select * from #schooldrop table #school
--client_id建索引,用exists不知道效果會不會好點... delete school where not exists (select 1 from child where child.client_id=school.client_id)
如果没索引,这样写只会比IN慢. 这里是 NOT EXISTS不是EXISTS. 因为子与主没有边连接,所以IN 会缓存结果,然后每次主查询在结果中搜索.而 NOT EXISTS子与主有连接,主查询每查找一条记录都会执行一次子查询不过如果建了索引的话,会好些.
先分别在两表的client_id字段上建立索引，后用如下查询 delete a from school as a left join child as b on a.client_id=b.client_id and a.client_id is not null
--dba_sunny() ( ) 信誉：100 先分别在两表的client_id字段上建立索引，后用如下查询 delete a from school as a left join child as b on a.client_id=b.client_id and a.client_id is not null------------------------------ 写了is not null就不能进行索引查询了的，还是不能解决LZ的问题。
dba_sunny() ( ) 信誉：100 2006-08-23 15:06:00 得分: 0
先分别在两表的client_id字段上建立索引，后用如下查询 delete a from school as a left join child as b on a.client_id=b.client_id and a.client_id is not null两表在left join 之后，数据应该会有很多冗余吧，即使删除了not null的，其他的记录可能也无法保证唯一（或者说和原school表中的相应记录数一样）还有“and a.client_id is not null”中"and"是不是应该改称"where"才可以执行阿？我刚才试了一下了。
delete a from school as a inner join child as b on a.client_id=b.client_id
--client_id建索引,用exists不知道效果會不會好點...
delete school
where not exists (select 1 from child where child.client_id=school.client_id)
--先分别在两表的client_id字段上建立索引，后用如下查询
delete a from school as a inner join child as b on a.client_id=b.client_id
楼主那条语句是要把school表中满足client_id字段不包含于child表中client_id范围的相应记录删除吧。那么您的这句似乎不对，您这是要把school表中在client_id字段上与child表相交的记录删除，不知道我是不是搞错了。
Select a.* into #school from school as a
inner join child as b on a.client_id=b.client_idtruncate table school
Insert into school
Select * from #schooldrop table #school
delete school
where not exists (select 1 from child where child.client_id=school.client_id)
如果没索引,这样写只会比IN慢. 这里是 NOT EXISTS不是EXISTS. 因为子与主没有边连接,所以IN 会缓存结果,然后每次主查询在结果中搜索.而 NOT EXISTS子与主有连接,主查询每查找一条记录都会执行一次子查询不过如果建了索引的话,会好些.
delete a from school as a left join child as b on a.client_id=b.client_id and a.client_id is not null
先分别在两表的client_id字段上建立索引，后用如下查询
delete a from school as a left join child as b on a.client_id=b.client_id and a.client_id is not null------------------------------
写了is not null就不能进行索引查询了的，还是不能解决LZ的问题。
先分别在两表的client_id字段上建立索引，后用如下查询
delete a from school as a left join child as b on a.client_id=b.client_id and a.client_id is not null两表在left join 之后，数据应该会有很多冗余吧，即使删除了not null的，其他的记录可能也无法保证唯一（或者说和原school表中的相应记录数一样）还有“and a.client_id is not null”中"and"是不是应该改称"where"才可以执行阿？我刚才试了一下了。