Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

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.

share|improve this answer
1  
I wonder if this one is faster: sqlfiddle.com/#!4/68a87/18 –  a_horse_with_no_name Aug 24 '12 at 19:37
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.