Oracle quick batch data deletion

Requirements:

1. A customer table named Pers, whose primary key is the customer ID persnbr;

2. The seven columns in the pers table have foreign key constraints;

2. In the pers table, 0.1 million pieces of data are generated in batches (persnbr> = 100000000 pieces of data). Now you need to delete these 0.1 million pieces of data to facilitate re-generation.

Solution:

1. directly execute the delete from pers where persnbr> = 100000000, and the result is too slow.

2. Use the TRUNCATE Syntax:

Create table pers_TEMP as select * from pers where persnbr <100000000; -- back up the records smaller than 100000000, and the query statement will be faster

Truncate table pers; -- clears all data in pers, which is fast

Insert into pers select * from pers_TEMP; -- inserts the backup data into the pers table.

Comparison between truncate and delete

1. truncate is much faster than delete, and less system and transaction log resources are used. This is because truncate deletes data by releasing the data pages used to store table data, and only records the release of pages in transaction logs. The delete statement deletes a row each time, in the transaction log, each row is deleted.

2. truncate is a ddl statement and delete is a dml statement. Like other ddl statements, truncate will be implicitly committed, so you cannot use the rollback command for truncate. The deletion of each record in the delete statement will be recorded in the transaction log, so it can be rollback.

3. The truncate statement will reset the index value of the table to the initial size, but the delete statement cannot.

Use a Linux File handle to restore accidentally deleted Oracle data files

Several ways to delete duplicate records in Oracle

How to quickly delete duplicate data in Oracle

Comparison between Delete statements and Truncate statements for Oracle database data deletion

Oracle uses the delete command to delete data recovery