How can I delete duplicates records in a table efficiently

Question

I know there are several options to delete duplicate rows, eg:

• ROWID
• Analytical fn's like Rank(), row_number()

What would be the most efficient way?

Answer 1

As you said, there are many options. My personal favorite is

delete from dups
  where rowid in (
                  select rid
                    from (
                          select row_number() over (partition by a,b,c order by a,b,c ) rn
                                ,rowid rid
                            from dups
                          )
                   where rn > 1
                  )
;

Being dups the table with the duplicates and a,b,c the columns to check.

Does one FTS (or index scan, if a,b,c is in the index) followed by access by rowid to delete.

SQLFiddle to check/play.

Answer 2

If you can afford to have a temporary table, then creating new table with the distinct values of the old one is a lot faster that deleting the rows. Something like:

create table no_dupes
as 
select min(id) as id,
       col_a,
       col_b,
       col_c,
       min(col_d) as col_d,
       min(col_e) as col_e
from table_with_dupes
group by col_a, col_b, col_c;

Of course this has the drawback of needing to re-recreate all indexes and constraints. But in my experience it way faster - especially if you are deleting a large number of rows in a large table.

Answer 3

I have written an article a while ago about how to delete duplicate records in Oracle. Check it out: http://www.jamesattard.com/2011/09/delete-duplicate-rows-from-oracle-table.html