今天清洗数据..数据量300W oracle 9i 原始查询语句:该条件查出的记录较少。但不好定位!这是关键。 select /*+USE_HASH(a,b)*/ a.* from sn_custorder_tbl a, (select /*+ index(sn_custorder_tbl flag)*/serialnumber,max(action_date) max_date from sn_custorder_tbl where flag ='2' group by serialnumber ) b where a.SERIALNUMBER = b.serialnumber and A.ACTION_DATE < b.max_date order by a.serialnumber ,a.id,a.flag //如果有更好的建议,欢迎指正!! //利用ROWID进行删除 delete from sn_custorder_tbl where rowid in ( select /*+USE_HASH(a,b)*/ a.rowid x from sn_custorder_tbl a, (select /*+ index(sn_custorder_tbl flag)*/serialnumber,max(action_date) max_date from sn_custorder_tbl where flag ='2' group by serialnumber ) b where a.SERIALNUMBER = b.serialnumber and A.ACTION_DATE < b.max_date ) //因此只消耗查询的时间。 欢迎各种瓶瓶罐罐~