How to quickly find and delete duplicate records in Oracle

Today, we sorted out the user system's staff database and found that many people have repeated records. We need to delete redundant staff information. After checking the information online, there are basically two solutions:

Today, we sorted out the user system's staff database and found that many people have repeated records. We need to delete redundant staff information. After checking the information online, there are basically two solutions:

Today, we sorted out the user system's staff database and found that many people have repeated records. We need to delete redundant staff information. After checking the information online, there are basically two solutions:

In Oracle, each record has a rowid, which is unique throughout the database, rowid determines which data file, block, and row of each record in Oracle;

In a duplicate record, the content of all columns may be the same, but the rowid may not be the same. Therefore, you only need to determine those with the largest rowid in the record, and delete all the others.

If you retrieve these duplicate records:

1. Select gh, xm from t_rygl_ryxx group by gh, xm having count (*)> 1

This statement can be used to find all records that are not repeated in the record)

2. Select * from t_rygl_ryxx e1 where rowid not in (select max (rowid) from t_rygl_ryxx e2 where e1.gh = e2.gh)

This statement can identify repeated records (including repeated records ).

The execution efficiency of the first statement is very high for the preceding two statements. The execution efficiency of the second statement is much slower, but the second record is very accurate, the retrieved records can be exported directly to Excel for better comparison. I will not talk about it here. You can try it on your own...

If you find records that do not repeat, it is easy to delete them. Here we will not talk much about them...

We hope to help our friends who have encountered such problems and send more messages.