SQL Tuning - Deleting Duplicate Rows

SQL Tuning - Deleting Duplicate Rows - A common problem that many DBAs and programmers face is trying to purge duplicate rows from a single table. These rows may have been inadvertently (re)imported by the DBA, or mistakenly created by a rogue application program.

The following example shows a particularly efficient way to delete duplicate records from a table. It takes advantage of the fact that a row's ROWID must be unique.

DELETE FROM emp E
WHERE  E.rowid > ( SELECT MIN(X.rowid)
                   FROM   emp X
                   WHERE  X.emp_no = E.emp_no );

Such a query is not necessary if the table contains a primary key. If the table contains a primary key (or other unique constraint), then you have the capability of uniquely identifying any row of data in the table. That unique identifier can be used to remove the duplicate rows.