Oracle - usuwanie duplikatów

http://www.dba-oracle.com/t_delete_duplicate_table_rows.htm


Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques. This page shows some examples of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:

DELETE FROM
table_name A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);


This outstanding article show an example of the RANK function to identify and remove duplicate rows from Oracle tables:
"It can be frustrating to find a way to delete all of the duplicate rows without deleting the initial instances.

delete from $table_name where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by $primary_key order by rowid) rank_n, rowid as "rowid"
from $table_name
where $primary_key in
(select $primary_key from $table_name
group by $all_columns
having count(*) > 1
)
)
)
where rank_n > 1
)

This query selects all of the ‘extra’ rowids and removes them. It is especially designed for limiting the query scans to only those records which have duplicates, which is useful if there’s only a subset of the table that you are dealing with. If you want to improve its efficiency for a table with a high percentage of duplicates, simply remove the inside where clause."