How to use rowID to find and delete duplicate records in a table in Oracle

oracle| Repeat | repeat record

You might be working at work. When you try to create a unique index to a column or column in a library table, you are prompted to ORA-01452: You cannot create a unique index and find duplicate records.

Here's a summary of several ways to find and delete duplicate records (for example, CZ):
The table CZ structure is as follows:
Sql> desc CZ
Name Null? Type
----------------------------------------- -------- ------------------

C1 Number (10)
C10 Number (5)
C20 VARCHAR2 (3)

How to delete duplicate records:
(1). In Oracle, each record has a rowid,rowid that is unique across the database, ROWID determines which data files, blocks, and rows are in Oracle for each record.

(2). In duplicate records, all columns may have the same content, but ROWID will not be the same, so just make sure that those with the largest rowid in the duplicate record are OK, and all the rest is deleted.

The criteria for duplicate record determination are:
C1,C10 and C20 are the same values in each of these three columns to be considered duplicate records.

After the view table CZ total total of 16 records:
Sql>set pagesize 100
Sql>select * from CZ;

C1 C10 C20
---------- ---------- ---
1 2 DSF
1 2 DSF
1 2 DSF
1 2 DSF
2 3 che
1 2 DSF
1 2 DSF
1 2 DSF
1 2 DSF
2 3 che
2 3 che
2 3 che
2 3 che
3 4 DFF
3 4 DFF
3 4 DFF
4 5 Err
5 3 Dar
6 1 Wee
7 2 Zxc

Rows selected.

1. Several ways to find duplicate records:
(1). Sql>select * from CZ GROUP by C1,C10,C20 have Count (*) >1;
C1 C10 C20
---------- ---------- ---
1 2 DSF
2 3 che
3 4 DFF

(2). Sql>select distinct * from CZ;

C1 C10 C20
---------- ---------- ---
1 2 DSF
2 3 che
3 4 DFF

(3). Sql>select * from CZ a where rowid= (select Max (ROWID) from CZ where C1=a.c1 and C10=A.C10);
C1 C10 C20
---------- ---------- ---
1 2 DSF
2 3 che
3 4 DFF

2. Several ways to delete duplicate records:
(1). Applies to situations where there is a large number of duplicate records (when indexed on C1,C10 and C20 columns, the following statements are highly efficient):
Sql>delete CZ where (C1,C10,C20) in (select C1,c10,c20 to CZ GROUP by C1,C10,C20 have Count (*) >1) and rowID not In
(select min (rowid) from the CZ group by C1,C10,C20 have Count (*) >1);

Sql>delete CZ where rowID not in (select min (rowid) from CZ Group by C1,C10,C20);

(2). Applies to situations where there is a small number of duplicate records (note that the following statement is inefficient for cases with large amounts of duplicate records):
Sql>delete from CZ A where a.rowid!= (select Max (ROWID) from CZ b where a.c1=b.c1 and A.C10=B.C10);

Sql>delete from CZ A where a.rowid< (select Max (ROWID) from CZ b where a.c1=b.c1 and A.C10=B.C10);

Sql>delete from CZ a where rowID < (select Max (ROWID) from CZ where C1=a.c1 and C10=A.C10);

(3). Applicable to cases with a small number of duplicate records (temporary table method):
Sql>create table Test AS SELECT DISTINCT * from CZ; (Create a temporary table test to hold duplicate records)

Sql>truncate table CZ; (Clears the CZ table's data, but retains the CZ table structure)

Sql>insert into CZ select * from test; (then insert the contents of the temporary table Test back again)

(4). Applicable to situations with a large number of duplicate records (Exception into clause method):
The Exception into clause in the ALTER TABLE command can also be used to determine duplicate records in a library table. This approach is somewhat cumbersome, and in order to use the "excepeion into" clause, you must first create a exceptions table. The SQL script file that created the table is utlexcpt.sql. For Win2000 Systems and UNIX systems, Oracle holds the file in a slightly different location, under the Win2000 system, where the script file resides in the $oracle_home\ora90\rdbms\admin directory, and for UNIX systems, the script file is stored in the $ORACLE the _home/rdbms/admin directory.

The specific steps are as follows:
Sql>@?/rdbms/admin/utlexcpt.sql

Table created.

SQL&GT;DESC exceptions
Name Null? Type
----------------------------------------- -------- --------------

ROW_ID ROWID
OWNER VARCHAR2 (30)
TABLE_NAME VARCHAR2 (30)
CONSTRAINT VARCHAR2 (30)

Sql>alter table CZ Add constraint cz_unique unique (C1,C10,C20) exceptions into exceptions;
*
ERROR at line 1:
Ora-02299:cannot Validate (TEST. Cz_unique)-Duplicate keys found

Sql>create table Dups as SELECT * from CZ where ROWID to (select row_id from exceptions);

Table created.

Sql>select * from Dups;

C1 C10 C20
---------- ---------- ---
1 2 DSF
1 2 DSF
1 2 DSF
1 2 DSF
2 3 che
1 2 DSF
1 2 DSF
1 2 DSF
1 2 DSF
2 3 che
2 3 che
2 3 che
2 3 che
3 4 DFF
3 4 DFF
3 4 DFF

Rows selected.

Sql>select row_id from exceptions;

row_id
------------------
Aaahd/aaiaaaadsaaa
Aaahd/aaiaaaadsaab
Aaahd/aaiaaaadsaac
Aaahd/aaiaaaadsaaf
Aaahd/aaiaaaadsaah
Aaahd/aaiaaaadsaai
Aaahd/aaiaaaadsaag
Aaahd/aaiaaaadsaad
Aaahd/aaiaaaadsaae
Aaahd/aaiaaaadsaaj
Aaahd/aaiaaaadsaak
Aaahd/aaiaaaadsaal
Aaahd/aaiaaaadsaam
Aaahd/aaiaaaadsaan
Aaahd/aaiaaaadsaao
Aaahd/aaiaaaadsaap

Rows selected.

Sql>delete from the CZ where rowID in (select row_id from exceptions);

Rows deleted.

Sql>insert into CZ select DISTINCT * from dups;

3 rows created.

Sql>select *from CZ;

C1 C10 C20
---------- ---------- ---
1 2 DSF
2 3 che
3 4 DFF
4 5 Err
5 3 Dar
6 1 Wee
7 2 Zxc

7 rows selected.

You can see from the results that duplicate records have been deleted.