Deleting Duplicate Rows in Oracle
Deleting duplicate rows from tables is one of the common task oracle developers come across. The data might get duplicated because of missing primary/unique key on the table or batch file getting loaded multiple times. Here I have tried to summarize different ways of deleting this duplicated data. Please note that this is not an extensive list of all available methods but the ones I was able to figure out. This should serve as a handy reference while at work. 
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');

Code language: SQL (Structured Query Language) (sql)
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);

Code language: SQL (Structured Query Language) (sql)
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);

Code language: SQL (Structured Query Language) (sql)
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)

Code language: SQL (Structured Query Language) (sql)
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);

Code language: SQL (Structured Query Language) (sql)
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);

Code language: SQL (Structured Query Language) (sql)
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);

Code language: SQL (Structured Query Language) (sql)
Step 2. 
DROP TABLE tbl_test; --drop the original table with lots of duplicate

Code language: SQL (Structured Query Language) (sql)
Step 3. 
RENAME tbl_test1 TO tbl_test; -- your original table without duplicates.

Code language: SQL (Structured Query Language) (sql)
In case you have some other method of deleting duplicate data, please share it.

View Comments

  • Nice, but may be you should elaborate a little on why certain approaches are slow and inefficient while others are better suited for the job.
    May be a explain plan for each of the delete statements will help further explain your point.

  • Hi Anuj,
    Had some time today so was browsing through your Oracle posts. Nice issue and resolutions but I think the solution for removing duplicates needs to be corrected above.

    1. First, your query would retain only the duplicates at the end as you are CTAS using row number 1. This mean it will return duplicates in the test1 table. The correct solution would be to put rn=1.

    2. Since you have the row_number value , why are you using ROWID again to query tbl_test? This is an unnecessary join, you can directly CTAS from the inner query (as shown below). This way you are eliminating the re-query of the tbl_test table based on ROWID.

    CREATE TABLE tbl_test1 NOLOGGING
    AS
    SELECT * FROM
    (SELECT ROW_NUMBER() OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn, tbl_test.*
    FROM tbl_test)
    WHERE rn=1;
    And then perform step 2 and step3.

    • Hi Ritesh,

      Thanks for pointing it out. The first one is wrong indeed, it should be rn=1. Corrected now.
      Regarding your 2nd point, if I want to avoid re-quering as you suggested, I need to provide the full column list to avoid "rn" becoming a new column for new table.

  • Hi Anuj,
    Point 1: I see the new corrected query now. Thanks for making the correction.
    Point 2: I would still feel that providing full column names in the select query would be a simpler option than re-querying the table. If you are re-querying the table just to avoid the column list then I would point that you are anyway using the full column list in the PARTITION BY clause of the ROW_NUMBER function. It would be matter of pasting the same column in the SELECT clause. Here would be the fine tuned version of the query without "rn" being part of the tbl_test1 table -
    CREATE TABLE tbl_test1 NOLOGGING
    AS
    SELECT ser_no, fst_nm, deptid, cmnt FROM
    (SELECT ROW_NUMBER() OVER (PARTITION BY ser_no, fst_nm, deptid, cmnt ORDER BY ROWID) rn, tbl_test.*
    FROM tbl_test)
    WHERE rn=1;
    You can now perform step 2 and step3 as before.

  • CREATE TABLE tbl_test1 NOLOGGING
    AS
    SELECT distinct ser_no, fst_nm, deptid, cmnt FROM tbl_test

    and now perform step 2 and 3

  • All the contents of this Website is a copy paste from other website like tutorialspoint,javaworld
    ,coderancs,stack overflow

    book like headfirst etc..

    Could face copy protection law

  • may be this would work
    create new table using following query
    create table new as select distinct * from old;
    and then delete old table

  • Very good posts...I have many of your posts on Java also & those were very helpful & informative. Similarly this one also has great stuff...keep posting such information
    Thanks

  • CREATE-DROP-RENAME

    Step 1: CREATE TABLE tbl_test1 NOLOGGING
    AS
    SELECT DISTINCT t .* FROM FROM tbl_test t ;

    STEP 2 : DROP TABLE tbl_test;

    STEP 3: RENAME tbl_test1 TO tbl_test;

Leave a Comment
Share
Published by
Anuj Parashar
Tags: Database database queries How-To Oracle
14 years ago

Recent Posts

  • Java

Java URL Encoder/Decoder Example

Java URL Encoder/Decoder Example - In this tutorial we will see how to URL encode/decode…

4 years ago
  • General

How to Show Multiple Examples in OpenAPI Spec

Show Multiple Examples in OpenAPI - OpenAPI (aka Swagger) Specifications has become a defecto standard…

4 years ago
  • General

How to Run Local WordPress using Docker

Local WordPress using Docker - Running a local WordPress development environment is crucial for testing…

4 years ago
  • Java

Create and Validate JWT Token in Java using JJWT

1. JWT Token Overview JSON Web Token (JWT) is an open standard defines a compact…

4 years ago
  • Spring Boot

Spring Boot GraphQL Subscription Realtime API

GraphQL Subscription provides a great way of building real-time API. In this tutorial we will…

4 years ago
  • Spring Boot

Spring Boot DynamoDB Integration Test using Testcontainers

1. Overview Spring Boot Webflux DynamoDB Integration tests - In this tutorial we will see…

4 years ago