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?
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:
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.
As you said, there are many options. My personal favorite is
Does one FTS (or index scan, if
SQLFiddle to check/play.
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