Method 3 for deleting duplicate records in an Oracle database

Introduction: Oracle DatabaseDuring operations, there will inevitably be many repeated records. These repeated records are useless data, so you can safely delete them. For a long time, we will introduce some common methods for deleting Repeated Records in Oracle database tables.

Method 1:

Delete from tb_channel a where a. rowid in

(Select max (B. rowid) from tb_channle B

Where a. policyno = B. policyno and a. classcode = B. classcode );

-- This method is generally slow when the data record exceeds 0.1 million.

Method 2:

-- Create a temporary table, -- clear the original table, -- insert it back to the original table, as shown in the following example:

Create table temp_emp as (select distinct * from employee );

Truncate table employee;

Insert into employee select * from temp_emp;

-- This method applies to large tables. Because it is a block operation, it is much more efficient for large tables.

Method 3:

-- Create a new table, -- repeat the table, and -- delete the original table, as shown in the following example:

Select distinct * into new_table from old_table

Order by primary key

Drop table old_table

Exec sp_rename new_table, old_table;

-- This method applies to large tables. Because it is a block operation, it is much more efficient for large tables.

In the preceding three methods, the number of tables is different. Therefore, you can flexibly use the table based on the actual situation. No matter which one you use, you can use it conveniently and quickly.