1
\$\begingroup\$

Is there more efficient way remove duplicate records?

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

where rowid <
(
  select max(rowid) from ' || TableName || ' b
  where a.val=b.val
)';
\$\endgroup\$
2
\$\begingroup\$

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 <> 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.

\$\endgroup\$
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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