Identify duplicate rows in a table and delete them

I create a table and insert some rows into the table t11.

SQL> create table t11 (name varchar2(20),id number(10) unique,salary decimal(10),address varchar2(10));

Table created.

SQL> BEGIN
  2  FOR I IN 20000 .. 300000 LOOP
  3  INSERT INTO T11 VALUES('XYZ',I,I,'ABC');
  4  END LOOP;
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  FOR I IN 20000 .. 300000 LOOP
  3  INSERT /* +append */ T11 VALUES('XYZ',I,I,'ABC');
  4  END LOOP;
  5   COMMIT;
  6  END;
  7  /

BEGIN
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C009998) violated
ORA-06512: at line 3

When i create id column with unique constraint,the data dictionary is not allowing to insert duplicates to the table.

So we can either drop or disable the constraint on the id column

SQL> alter table t11 drop constraint SYS_C009998;

Table altered.

SQL> BEGIN
  2  FOR I IN 20000 .. 300000 LOOP
  3  INSERT /* +append */ into T11 VALUES('XYZ',I,I,'ABC');
  4  END LOOP;
  5  COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.

I disable the constraint and somehow inserted the rows by mistake without knowing what metadata is stored inside the table.

Now the end user is complaining about the duplicates values while checking the application.

First ,we have to identify the duplicate values in the table.

For that ,we need to use the unique column on the table to identify duplicates.Here,as we know that id is the unique column,we use id column to identify the table which have > 1 rows and limited the row to 5.

SQL> select id,count(id) from t11
  2  group by id
  3  having count(id) > 1
  4  fetch next 5 rows only;

        ID  COUNT(ID)
---------- ----------
     20001          2
     20003          2
     20009          2
     20010          2
     20012          2

SQL> select count(*) FROM t11
WHERE rowid not in
(SELECT MAX(rowid)
FROM t11
GROUP BY id);

  COUNT(*)
----------
    280001

I identified a total of 280001 duplicate counts from 560002 rows

Total row count in the table:

SQL> select count(*) from t11;

  COUNT(*)
----------
    560002

Now we got the duplicate rows.we can remove these rows by an efficient ctas shift method.

I create a temporary table to store the original data.

SQL> create table t11_temp as
  2  select * from t11
WHERE rowid in
(SELECT MAX(rowid)
FROM t11
GROUP BY id);  3    4    5    6

Table created.

SQL> select count(*) from t11_temp;

  COUNT(*)
----------
    280001

Drop the original table

SQL> drop table t11;

Table dropped.

Create the original table with old data from temporary buffer table

SQL> create table t11 as select * from t11_temp;

Table created.

Check the row count on the new table

SQL> select count(*) from t11_temp;

  COUNT(*)
----------
    280001

Drop the temporary table

SQL> drop table t11_temp;

Table dropped.

We can also use delete statement to remove the duplicate rows but the method is slow and takes much longer to complete.

At the end ,we can create the constraint on the id column.

SQL> alter table t11 add constraint SYS_C009998 unique(id);

Table altered.

To ensure the uniqueness of the table,cross verify the table.

SQL> select count(*) FROM t11
WHERE rowid not in
(SELECT MAX(rowid)
FROM t11
GROUP BY id);
no rows selected

Published by kishan

You will know about me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: