How to delete oracle record duplication

When I was doing a project, a colleague accidentally duplicate all the data in a table while importing data. That is to say, all the records in this table have a record. This table has tens of millions of data records and is a production system. That is to say, You cannot delete all records, and you must quickly delete duplicate records.

This section summarizes the methods for deleting duplicate records and their advantages and disadvantages.

For the sake of convenience, assume that the table name is Tbl. The table has three columns: col1, col2, and col3, where col1 and col2 are primary keys, and indexes are added to col1 and col2.

1. Create a temporary table

You can import data to a temporary table first, delete the data from the original table, and then export the data back to the original table. The SQL statement is as follows: creat table tbl_tmp (select distinct * from tbl ); truncate table tbl; // clear the table record insert into tbl select * from tbl_tmp; // insert the data in the temporary table back.

This method can meet the requirements, but it is obvious that for a table with tens of millions of records, this method is very slow. In the production system, this will bring great overhead to the system and will not work.

2. Use rowid

In oracle, each record has a rowid, which is unique throughout the database. rowid determines which data file, block, and row of each record is in oracle. In repeated records, the content of all columns may be the same, but the rowid may not. The SQL statement is as follows: delete from tbl where rowid in (select. rowid from tbl a, tbl B where. rowid> B. rowid and. col1 = B. col1 and. col2 = B. col2)

If you already know that only one record is repeated, this SQL statement applies. However, if there are N repeated records for each record, this N is unknown and the following method should be considered.

3. Use max or min Functions

Rowid is also used here, which is implemented in combination with the max or min function. The SQL statement is as follows: delete from tbl awhere rowid not in (select max (B. rowid) from tbl B where. col1 = B. col1 and. col2 = B. col2); // here max can use min

Or use the following statement to delete from tbl awhere rowid <(select max (B. rowid) from tbl B where. col1 = B. col1 and. col2 = B. col2); // if you change max to min, you must change "<" to ">" in the where clause"

The idea behind the above method is basically the same, but the use of group by reduces Explicit Comparison conditions and improves efficiency. The SQL statement is as follows:
Deletefrom tbl where rowid not in (select max (rowid) from tbl tgroup by t. col1, t. col2 );
Delete from tbl where (col1, col2) in (select col1, col2 from tblgroup bycol1, col2havingcount (*)> 1) and rowidnotin (selectnin (rowid) fromtblgroup bycol1, col2havingcount (*)> 1)

Another method is suitable for the case where there are few duplicate records and indexes in the table. Assume that there are indexes on col1 and col2, and there are fewer records in the tbl table. The SQL statement is as follows 4. Use group by to improve efficiency.

Author "lijun-19871023"