Finding Duplicates - Part I

There are various ways to Find the Duplicates in a table,but the tough
thing is to identify the correct one. so here we will discuss about
the differents methods to find the duplicates in a table.

Enabling Unique constraint

The fastest way of detecting duplicate records in a table is to attempt to switch on a unique constraint.
so lets create a Test Table and do with an example by adding some duplicate data.

SQL> create table TestDup
(
empno number,
ename varchar2(20),
sal number,
deptno number
);

Table created.

SQL> Insert into TestDup values(1000,'A',5000,10);

1 row created.

SQL> Insert into TestDup values(1001,'B',8000,20);

1 row created.

SQL> Insert into TestDup values(1000,'A',9000,20);

1 row created.

SQL> commit;

Commit complete.

Now we will try to add a Unique constraint to the Table TestDup and see
what happens. So i am creating a Unique Constraint on combination of
empno and ename column. Note: Unique Constraint will be not created as
long as duplicate rows (based on empno and ename) are there in the table.

SQL> Alter table TestDup add Constraint U_Const_01 Unique(empno,ename);

ERROR at line 1:
ORA-02299: cannot validate (SCOTT.U_CONST_01) - duplicate keys found

So it is throwing an Error saying that the table contains duplicate rows and
so unique constraint cannot be applied. so now comes the part to capture the
duplicate rows. First of all we need a table to capture the duplicate rows,
the table creation script is available in the ORACLE_HOME installation path,
as follows

$ORACLE_HOME\rdbms\admin\utlexcpt.sql

I executed the following create table command from the sql file.


SQL> Create Table Exception_table(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));

Table created.

now we are ready to catch the duplicate keys. so now i will again add the
Constraints but with a little in the syntax;

SQL> Alter table TestDup add Constraint U_Const_01 Unique(empno,ename) exceptions into Exception_table;

ERROR at line 1:
ORA-02299: cannot validate (SCOTT.U_CONST_01) - duplicate keys found

so if you see the syntax, i was using a keyword called EXCEPTIONS INTO , to redirect the duplicate keys
into the table Exception_Table. so now to see the duplicate rows, we use the following query

SQL> select * from TestDup where rowid in (select row_id from Exception_table);

EMPNO ENAME SAL DEPTNO

---------- -------------------- ---------- ----------

1000 A 5000 10

1000 A 9000 20

so it gives the two rows which are duplicate. so now we can take corrective action to remove
the duplicates and then add the constraint to the table to avoid further duplication.