Efficient way to remove duplicate records Is there more efficient way remove duplicate records?

strsql := 'delete from ' || TableName || ' a

where rowid &lt;
(
  select max(rowid) from ' || TableName || ' b
  where a.val=b.val
)';
I would use the ROW_NUMBER windowing function to identify the duplicates and delete those.

Given something like the following:

CREATE TABLE tbl
(
   v1 VARCHAR2(10)
  ,v2 VARCHAR2(10)
);

INSERT ALL
  INTO tbl (v1, v2) VALUES ('A','A')
  INTO tbl (v1, v2) VALUES ('A','A')
  INTO tbl (v1, v2) VALUES ('A','Z')
  INTO tbl (v1, v2) VALUES ('B','B')
  INTO tbl (v1, v2) VALUES ('B','B')
  INTO tbl (v1, v2) VALUES ('B','B')
  INTO tbl (v1, v2) VALUES ('C','B')
  INTO tbl (v1, v2) VALUES ('C','B')
  INTO tbl (v1, v2) VALUES ('keep', 'keep')
SELECT * FROM dual
;


I would do something like the following:

DELETE
  FROM tbl
  WHERE ROWID IN (
    SELECT id
    FROM (
      SELECT
         ROWID AS id
        ,ROW_NUMBER() OVER (PARTITION BY v1, v2 ORDER BY ROWID) AS rnum
      FROM tbl
    )
    WHERE rnum &lt;&gt; 1
  )
;


I wrote a blog post about this using SQL Server but the idea is the same on Oracle.

This blog post claims that using ROW_NUMBER for deleting duplicates is very fast in Oracle (my experience in SQL Server would lead me to believe the claim).

Never under estimate the power of windowing functions.