How do I eliminate the duplicate rows in Oracle?

Question

How do I can quickly open a 8Gb dsv file with 40 million rows with oracle sql developer?
I did it with a best laptop with high cpu speed but it took one day, I'm looking for a faster way...

Answer 1

Use subquery to delete duplicate rows
Here we see an example 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
        );

Use analytics to delete duplicate rows

You can also detect and delete duplicate rows using Oracle analytic functions:

delete from 
   customer
where rowid in
 (select rowid from 
   (select 
     rowid,
     row_number()
    over 
     (partition by custnbr order by custnbr) dup
    from customer)
  where dup > 1);