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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

What would be the most efficient way?

share|improve this question

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
select min(id) as id,
       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

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
I wonder if this one is faster:!4/68a87/18 – a_horse_with_no_name Aug 24 '12 at 19:37

Your Answer


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.