Common Methods for deleting duplicate records in Oracle databases

Method 1:
Delete form 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.

 

Differences between truncate table and delete tabel

I,
1. Delete generates rollback. If you delete a table with a large amount of data, it takes up a lot of rollback segments. truncate is a DDL operation and does not generate rollback, which is faster.
　　
2. Do not free up space from tablespace.
　　
Alter tablespace AAA coalesce; space available
　　
3. After truncate adjusts high water mark and delete does not. truncate, the table's hwm is returned to the positions of initial and next (default), and delete is not allowed.
　　
4. truncate can only be set to table. Delete can be set to table, view, and synonym.
　　
5. the truncate table object must be in the current mode, or have the permission to drop any table, while delete must be in the current mode, or be authorized to delete on schema. table or delete any table Permissions

II,
Truncate is a DDL statement.
Delete is a DML statement.

DDL statement is automatically submitted. Once the command is complete, it cannot be returned.
The speed of truncate is much faster than that of Delete. Therefore, commit work is required after delete, and truncate is not required.

III,
Truncate will return highwatermark to 0... it will be faster when new data is inserted again.
Therefore, it is generally used on temp table, but note that truncate cannot be used on PL/SQL. It can only be used with dynamic SQL.

 

IV,
When you no longer need this table, use drop;
Use truncate to retain the table but delete all records;
When you want to delete some records (always with a where clause), use Delete.

 

V,

Truncate table index will also be deleted, not the drop index