Look - the query has to search the ENTIRE output of the IN (SELECT ..)
for each set of rows it deletes. I know that rowid's are what you get from an index.
Instead of me going on about this - look in Tom Kyte's book 'EXpoert One on One' and look in the analytical function chapter - there is an example of how to optimize a query very like this. Or try the asktom oracle site:
From there:
Code:
from
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1224636375004
delete from tableA a
where rowid <> ( select max(rowid)
from tableA b
where b.column1 = a.column1)
/
