Deleting Duplicate Rows in Oracle

CREATE TABLE tbl_test(

  SER_NO NUMBER,
   FST_NM VARCHAR2(30),
   DEPTID NUMBER,
   CMNT   VARCHAR2(30));

INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy');
INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(1, 'aaaaa', 2004, 'xxx');
INSERT INTO tbl_test VALUES(3, 'ccccc', 2005, 'zzz');
INSERT INTO tbl_test VALUES(2, 'bbbbb', 2005, 'yyy');

1. Using MIN(rowid) : The most common method of removing duplicate rows.

DELETE FROM tbl_test
      WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                              FROM tbl_test
                          GROUP BY ser_no, fst_nm, deptid, cmnt);

Comment: This will take hours & hours if the table is large (records in million).
2. Using MIN(rowid) & Join: More or less the same as first one

DELETE FROM tbl_test t
      WHERE t.ROWID NOT IN (SELECT MIN (b.ROWID)
                              FROM tbl_test b
                             WHERE b.ser_no = t.ser_no
                               AND b.fst_nm = t.fst_nm
                               AND b.deptid = t.deptid
                               AND b.cmnt   = t.cmnt);

3. Using Subquery: This is an interesting one

DELETE FROM tbl_test
      WHERE ser_no IN (SELECT ser_no FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND fst_nm IN (SELECT fst_nm FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND deptid IN (SELECT deptid FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND cmnt   IN (SELECT cmnt   FROM tbl_test GROUP BY ser_no, fst_nm, deptid, cmnt HAVING COUNT (*) > 1)
        AND ROWID NOT IN (SELECT   MIN (ROWID) 
                              FROM tbl_test
                          GROUP BY ser_no, fst_nm, deptid, cmnt
                            HAVING COUNT (*) > 1)

Comment: A complicated way of performing the same task. Not efficient.
4. Using Nested Subqueries:

DELETE FROM tbl_test a
      WHERE (a.ser_no, a.fst_nm, a.deptid, a.cmnt) IN (SELECT b.ser_no, b.fst_nm, b.deptid, b.cmnt
                                                     FROM tbl_test b
                                                    WHERE a.ser_no = b.ser_no
                                                      AND a.fst_nm = b.fst_nm
                                                      AND a.deptid = b.deptid
                                                      AND a.cmnt   = b.cmnt
                                                      AND a.ROWID  > b.ROWID);

Comment: Will work but for large tables, this is not efficient.
5. Using Analytic Fucntions:

DELETE FROM tbl_test
      WHERE ROWID IN (
               SELECT rid
                 FROM (SELECT ROWID rid,
                              ROW_NUMBER () OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn
                         FROM tbl_test)
                WHERE rn <> 1);

Comments: This is by far one of the best solutions if the table is really really large. Using the invaluable power of Analytics.
6. CREATE-DROP-RENAME:  This one is a more appropriate solution in terms of resource usage in the sense that if we have a really large table, then with delete option we are generating a huge amount of UNDO information.(if we want to rollback for any reason). Even worst, the rollback segment may not be large enough to hold your UNDO information and give error. CTAS comes handy in this case.
Step 1.

CREATE  TABLE tbl_test1 NOLOGGING
   AS
   SELECT tbl_test .*
     FROM tbl_test tbl_test
    WHERE ROWID IN (SELECT rid 
                      FROM (SELECT ROWID rid, ROW_NUMBER() OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn 
                              FROM tbl_test)
                     WHERE rn=1);

Step 2.

DROP TABLE tbl_test; --drop the original table with lots of duplicate

Step 3.

RENAME tbl_test1 TO tbl_test; -- your original table without duplicates.

Generating Random Data in Oracle

SELECT ROWNUM
FROM DUAL
CONNECT BY LEVEL < 10000;

/

SELECT     LEVEL                                                       empl_id,
           MOD (ROWNUM, 50000)                                         dept_id,
           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2)                 salary,
           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F')   gender,
           TO_DATE (   ROUND (DBMS_RANDOM.VALUE (1, 28))
                    || '-'
                    || ROUND (DBMS_RANDOM.VALUE (1, 12))
                    || '-'
                    || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
                    'DD-MM-YYYY'
                   )                                                   dob,
           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50))        address
      FROM DUAL
CONNECT BY LEVEL < 10000;