待优化的SQL,此sql在数据量大时如僵死一般:
delete from tb_sc where (studentid,courseid,score) not in (select studentid,courseid,max(score) as score from tb_sc group by studentid,courseid);
优化方案1:
delete from tb_sc where id not in (select tb_sc.id from tb_sc,( select studentid,courseid,max(score) as score from tb_sc group by studentid,courseid ) tb_sc2 where tb_sc.studentid=tb_sc2.studentid and tb_sc.courseid=tb_sc2.courseid and tb_sc.score=tb_sc2.score)
优化方案2:
delete from tb_sc where not exists ( select null from tb_sc a, (select studentid,courseid,max(score) as score from tb_sc group by studentid,courseid) b where a.studentid=b.studentid and a.courseid=b.courseid and a.score=b.score and tb_sc.id=a.id)
优化方案3: 这种方案适用于delete语句太简单而删除数据较多的场合:
每次删除一百条:
delete from tb_sc where (studentid,courseid,score) not in (select studentid,courseid,max(score) as score from tb_sc group by studentid,courseid) and rownum<101
放在循环里执行:
while(剩余数量>0){ 删除符合条件的 }
优化方案四:将要保留的数据存入一张临时表,删除原表再倒回来,这种操作最大的优势在于降低表的水位线。