"ORACLE" deletes duplicate data from a table

Reason

In the process of operating the database, we may encounter this situation, the data in the table may be repeated, so that we have a lot of difficulties in the operation of the database, so how to delete these duplicate data?

You may encounter a job when you try to create a unique index on a column or column in a library table, you are prompted to ORA-01452: You cannot create a unique index and discover duplicate records.

Processing methods

There are two possible scenarios for duplicate data:

First: Delete all duplicate data in the table

Second: Keep only one record of the most recent record in duplicate data "common in work"

The idea of deleting duplicate data

Each row of data corresponding to the ROWID is unique, the time table in the two data exactly the same, ROWID is also different. We just need to find the duplicate value of the ROWID to delete the duplicate value

Analog data

[Email protected]>create table Test as SELECT * from EMP;

[Email protected]>insert into test select * from test;

-

[email protected]>insert into test select * from test;

28672 rows created.

elapsed:00:00:00.03

Delete all data for duplicate values

"Method One"

[email protected]>@?/rdbms/admin/utlexpt1.sql

Generate Exceptions Table

Adding a (primary KEY or UNIQUE constraint) to the test table does not succeed and inserts the duplicate value ROWID into the exceptions table

[Email protected]>alter table Test add constraint TEST_EMPNO_PK primary key (EMPNO)

exceptions into exceptions;

[Email protected]>select count (*) from exceptions;

COUNT (*)

----------

28672

Delete all duplicate data

[Email protected]>delete from Test where rowID in (select row_id from exceptions);

28672 rows deleted.

Elapsed: 00:00:00.28

"Method Two"

[Email protected]>delete from test where ename in

(select ename from Test group by ENAME have Count (*) >2);

28672 rows deleted.

Elapsed: 00:00:00.34

"Method Three"

Method Three is based on the method two, because the test data is relatively small, we can not take into account the database of large amounts of data, a large number of deleted data may cause the database to hang, so we could insert duplicate values into the temporary table, and then delete the table

[Email protected]>create table TEST_TEMP1

As (select Empno,ename from Test group by Empno,ename have Count (*) >2)

[Email protected]>delete from test where (Empno,ename) in (select Empno,ename from TEST_TEMP1);

28672 rows deleted.

Elapsed: 00:00:00.26

Delete duplicate data keep the latest article

It also uses ROWID to process the data.

[Email protected]>delete from test where rowid not in (select Max (ROWID) from test group by ename);

28658 rows deleted.

Elapsed: 00:00:00.24

Create a temporary table to insert the reserved rowid into the table,

[Email protected]>create table Temp1 (row_id urowid);

[Email protected]>insert into TEMP1 select Max (ROWID) from test group by ename;

[Email protected]>delete from test where rowid not in (select row_id from Temp1);

28658 rows deleted.

Elapsed: 00:00:00.25

Technology is sharing, that is ascension, but also innovation,

If there is an error in the article, or different opinions can contact JAYEWU

1048586878 qq:1048586878

"ORACLE" deletes duplicate data from a table