Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to delete data in batches in the database. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

In the process of running an Oracle database, it is sometimes encountered to delete data in batches, for example, a table that stores historical data has a large amount of data that does not need to be retained, and this part of the data should be deleted. The usual methods are as follows:

1. Use the TRUNCATE command to delete.

If the data of the entire table is to be deleted, using the TRUNCATE TABLE command is the ideal choice. It deletes all the data in the table and is fast because it doesn't write REDO LOG FILE. While deleting, the indexes and constraints of the table still exist. This method applies to all versions of ORACLE. But this approach doesn't work when the data to be deleted is only part of the table.

2. Perform DELETE operation directly.

Delete directly by using the DELETE command, which may result in an error in the rollback segment if the amount of data deleted is large. This is due to the error caused by expanding the rollback segment during the process of deleting data until the maximum range of the rollback segment or the tablespace free space in the rollback segment is used up. This problem can be solved by specifying a large enough rollback segment for the transaction that deletes the data or turning on the AUTOEXTEND option of the table space in which the rollback segment resides, while changing the MAXEXTENTS of the rollback segment to UNLIMITED. However, there is still a hidden danger. If a large amount of data is deleted and the database works in archive mode, it may cause frequent log switching and all log files need to be archived. If the archiving process is too late to archive the log files, the database will be suspended and will not return to normal until the log files are available.

So this method is not ideal.

3. Delete the partition.

For example, if the partition table is done according to time, the operation of deleting a partition by drop partition may be the fastest and easiest. However, there are not many cases of using partitioned tables.

Here are three other methods:

Method 1:

Deleting large amounts of data in batches is usually very complex and slow, and there are many methods, but the common concept is: delete in batches and submit one by one.

The following is my deletion process, my data table can be deleted through the primary key, tested Delete and For all two methods, for all here did not bring performance improvement, so still chose to delete directly in batch.

First of all, create a process and use homemade transactions to deal with: (what is autonomous, which is not explained too much here)

Create or replace procedure delBigTab

(

P_TableName in varchar2

P_Condition in varchar2

P_Count in varchar2

)

As

Pragma autonomous_transaction

N_delete number:=0

Begin

While 1 # 1 loop

EXECUTE IMMEDIATE

'delete from' | | p_TableName | | 'where' | | p_Condition | | 'and rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.