Deleteの速度优化

今天清洗数据..数据量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_tblwhere flag ='2'group by serialnumber ) bwhere a.SERIALNUMBER = b.serialnumber and A.ACTION_DATE < b.max_dateorder 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_tblwhere flag ='2'group by serialnumber ) bwhere a.SERIALNUMBER = b.serialnumber and A.ACTION_DATE < b.max_date)//因此只消耗查询的时间。 

欢迎各种瓶瓶罐罐~