Oracle deletes duplicate records for the same table with ROWID

Often there is a business scenario where we have duplicate records in the same table , and we want to delete the duplicate records, this article is used to solve this problem, the method is the most efficient method I am currently encountering (if there is a better way to welcome users to provide). This method will use the rowID, the following is a brief introduction to the definition of ROWID

1. ROWID definition

ROWID: Globally unique address for rows in a database

For each row in the data, the ROWID pseudo-column returns the address of the row. The ROWID value mainly contains the following information:

• object's data Object number
• The data block in the data file where the row resides
• The position of the data block in the row (the first row is 0)
• The data file where the data row resides (the first file is 1). The file number is relative to the table space.

Typically, a ROWID value uniquely identifies a row in the data. However, different tables stored in the same cluster can have the same rowid.

2, the method of realization

Method One:

DELETE  fromhr.employeesWHEREROWIDinch (                 SELECTROWID from (                        SELECTfirst_name, last_name, ROWID, Row_number () Over(PARTITION byFirst_name,last_nameORDER  byEMPLOYEE_ID) asStaff_row--partition according to the reserved unique field, take Row_number                                fromhr.employees)WHEREStaff_row> 1               );

At first glance, I take a go, this is what things to, so difficult to understand!! It doesn't matter, it is understood to explain to you below.

For the Employees table in Oracle's built-in sample user HR, we want first_name and last_name to have no duplicates (if your business needs to be converted accordingly, as in the score table the number is the only one , the partition by study number), so the two fields are partitionby.

The primary purpose of our selection in subqueries subquery is ROWID and Row_number (First_Name and last_name are only used to aid in the addition of fields); we chose Row_number > 1 in the subquery, In this case, only one record is not selected according to each grouping in the first_name and last_name groupings, and the outermost delete removes the selected rowID directly. This completes the hr.employees for First_Name and last_name.

　　A friend would say, "Mom, this is so hard to understand!" in this case, there are often methods two ~ ~ ~

Method Two:

DELETE  from hr.employees T1 WHERE  not inch (                       SELECTMIN(T2. ROWID)                       from  hr.employees T2                       GROUPby-- GROUP by the fields that you want to keep only                      );

This is obviously better than the method, in the subquery we first select the ROWID, and then according to the unique fields we want to keep grouped , and take each group of the smallest rowid (note is the subquery table ROWID); All records except the smallest rowID .

How is this method not instantaneous and very well understood? but you think it's over? No No No

Method Three:

DELETE  from hr.employees T1 WHERE > (                   SELECTMIN(t2.rowid)                   from  hr.employees T2                    WHERE = --Match according to the field you want to keep only                 );

This way looks similar to the method two, but to say that he is using the connection, he uses the connection, not to say that the connection must be faster than the group by, but basically will not lose group by, and in general, is the fastest. and the outer ">" can be used to index , is a variety of fast.

Similarly, the subquery Associates T1 and T2 with the fields you want to keep , then selects the smallest rowid (note the ROWID of the subquery table), and then uses ">" in the outer layer to keep only the smallest record of each matching result. And then delete the duplicate records instantly.

　　At this time a friend thinks this is the quickest way, but, I want to say, no, not! Take a look at the quickest method below!

-- cheat You, the above is already the fastest way I've come into contact with

View Code

Oracle deletes duplicate records for the same table with ROWID