Efficiently and quickly delete duplicate records in Oracle tables

Although I used to delete repeated records in the previous article, it was not bad, but I encountered a heavyweight large table, and I accidentally thought of a new method.

Idea 1. keep records that are not repeated

2. Save a rowid in the record

// 3. delete records whose rowid is not the rowid in step 2 of the original table, leaving one of the duplicate data

3. Find the record whose rowid is the rowid in step 2.

4. Data Connection between 1 and 3 is the desired data.

Operation example

Step 1:

SQL> create table xxfgs_sig as (select imeid, max (dn) dn, max (xlh) xlh, max (pro_name) pro_name, max (area_name) area_na

Me, max (brand) brand, max (m_type) m_type from xxfgs group by imeid having count (*) <2 );

Step 2:

SQL> create table xxfgs_row as (select max (rowid) rowdata, imeid from xxfgs group by imeid having count (*)> 1 );

// Step 3: /// you can create an index for meid.

// SQL> delete from xxfgs where rowid not in (select rowdata from xxfgs_row );

// Or

// SQL> delete from xxfgs a where a. rowid <> (select rowdata from xxgs_row B where a. meid = B. meid );