Using rowid to delete duplicate rows from an oracle database?

Question

I'm using the following query to delete duplicate rows from a table. There are only 2 columns in the table (col1 and col2).

delete from tab1 where rowid not in
(select min(rowid) from tab1 t group by col1, col2);

Is there any problem, like some side effects, if I use this query to delete duplicate rows?

Answer 1

As far as i can tell, your query will perform slow

This should give you a better performance

delete t1
from tab1 t1
join tab1 t2
on t1.rowid > t2.rowid 
and t1.col1 = t2.col1 
and t1.col2 = t2.col2

Or this (it works on mssql, I believe oracle has the same syntax)

;WITH [CTE DUPLICATE] AS 
(
SELECT 
  ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) RN
FROM tab1
)
DELETE FROM [CTE DUPLICATE] WHERE RN > 1

Answer 2

This query is ok. You can phrase it without rownum as described in this question:

Oracle Delete Rows Matching On Multiple Values