How to efficiently Delete duplicate data from Oracle databases

The deduplication technology can provide a larger backup capacity for longer data retention, continuous verification of backup data, improve the level of data recovery services, and facilitate data disaster tolerance. Duplicate data may be in the following two cases. In the first case, only some fields in the table are the same, and in the second case, the two rows have identical records. Oracle Database deduplication technology has the following advantages: larger backup capacity, continuous data verification, a higher level of data recovery services, and convenient Disaster Tolerance for backup data.

I. Delete repeated data from some fields

Let's talk about how to query duplicate data.

The following statement can query the data that is duplicated:

Select Field 1, Field 2, count (*) from table name group by field 1, Field 2 having count (*)> 1

Change the ">" number above to "=" to query the non-duplicated data.

To delete the duplicate data, use the following statement:

Delete from table name a where field 1, Field 2 in

(Select Field 1, Field 2, count (*) from table name group by field 1, Field 2 having count (*)> 1)

The preceding statement is very simple, that is, to delete the queried data. However, the deletion execution efficiency is very low, and the database may be suspended for a large amount of data. Therefore, we recommend that you first Insert the queried duplicate data into a temporary table and then delete it. In this way, you do not need to perform another query When deleting the data. As follows:

Create table temporary TABLE

(Select Field 1, Field 2, count (*) from table name group by field 1, Field 2 having count (*)> 1)

The above statement creates a temporary table and inserts the queried data into it.

You can perform the following deletion operations:

Delete from table name a where field 1, Field 2 in (select Field 1, Field 2 from temporary table );

It is much more efficient to create a temporary table and then delete it.

At this time, everyone may jump out and say, what? If you want to execute such a statement, isn't all the duplicates deleted? We want to keep the latest record in the duplicate data! Don't worry. Let's talk about how to perform this operation.

In Oracle, an automatic ROWID is hidden, which gives each record a unique ROWID. If we want to keep the latest record, we can use this field, retain the record with the largest ROWID In the duplicate data.

The following is an example of querying duplicate data:

select a.rowid, a. * from table name a where a.rowid! = ( select max (b.rowid) from table name b where a. field 1 = b. field 1 and a. field 2 = b. field 2 )

The statement in the brackets above is used to query the largest ROWID record in duplicate data.

In addition, duplicate data except the maximum ROWID is queried.

As a result, we need to delete the duplicate data and keep only the latest data record. Then we can write it like this:

delete from table name a where a.rowid! = ( select max (b.rowid) from table name b where a. field 1 = b. field 1 and a. field 2 = b. field 2 )

To put it simply, the execution efficiency of the preceding statement is very low. You can consider creating a temporary table. We need to judge the repeated fields and ROWID to be inserted into the temporary table, and then compare them When deleting the table.

create table temporary table as
select a. field 1, a. field 2, MAX (a.ROWID) dataid from formal table a GROUP BY a. field 1, a. field 2;
delete from table name a
where a.rowid! =
(
select b.dataid from temporary table b
where a. field 1 = b. field 1 and
a. field 2 = b. field 2
);
commit;

Ii. Completely Delete duplicate records

If the two rows in the table have identical records, you can use the following statement to obtain the records after deduplication:

Select distinct * from Table Name

You can place the queried records in a temporary table, delete the original table records, and export the data in the temporary table back to the original table. As follows:

CREATE TABLE temporary table AS (select distinct * from table name); truncate table Formal table; --Note: Originally written as a drop table formal table due to a typo; now it has been corrected insert into formal table (select * from temporary table); drop table temporary table;

If you want to delete the duplicate data of a table, you can create a temporary table first, import the data after the duplicate data is removed to the temporary table, and then import the data into the formal table from the temporary table, as follows:

Insert into t_table_bak

Select distinct * from t_table;

Iii. How to quickly delete an Oracle database

Enter regedit in the Registry as soon as possible, expand HKEY_LOCAL_MACHINE SOFTWARE in sequence, and find the Oracle node. Delete. Delete the Oracle data file and select the path during installation. Finally, delete the Oracle boot file and delete the Oracle folder in the system drive letter's Program Files.

1. Analysis of Oracle listener installation and configuration
2. 30 Oracle statement optimization rules
3. Troubleshooting of common Oracle Database Problems